VBA Code to Automate vlookup based on condition and additional formula.

Question

Hi All

I have recorded a macro to do the following, but it doesn't look neat and creates format issues with unsightly cell data in two columns. Can you possibly assist.

Workbook name = STOCK TAKE AUTOMATION TEMPLATE

The above is the main sheet. VBA should function in the following sequence. The range is Column E3, up to line E78.

(Step 1) If cell E3 has a value, then the value should be deleted and a VLOOKUP formula should be inserted. The lookup value is from Column B.
The reference/table is Column B-D in the below workbook. I have the code already that opens that workbook, so it will be visible. The return value is from below column D. See below workbook. This should only be done If range of cells E3 to E78 in STOCK TAKE AUTOMATION TEMPLATE has a value, otherwise nothing should be done.

Workbook name = VARIANCE RECOUNT 2 TEMPLATE

(Step 2) If cells in column G of STOCK TAKE AUTOMATION TEMPLATE has a value, then the value should be deleted, and formula =E3-F3 should be inserted.

I have recorded a macro myself doing the steps, and it works, but the rows with no data, in the range column E & G then has zero values, which creates a problem when I do a print to PDF. See below. So the macro and the way I have approached it works, but the result leaves unsightly cells with formulas included in the PDF print which I would like to avoid, See below.

The end result should look like this:

Any assistance will be greatly appreciated.

Regards

Answer 1

Hi Hans

The solution calls for them to remain separate, the one is a count sheet, and the other a recon sheet.

Staff can cheat if the are in the same workbook.

Answer 2

What is the exact name of the STOCK TAKE AUTOMATION TEMPLATE workbook?

1. STOCK TAKE AUTOMATION TEMPLATE.xlsx
2. STOCK TAKE AUTOMATION TEMPLATE.xlsm
3. STOCK TAKE AUTOMATION TEMPLATE.xlsb
4. STOCK TAKE AUTOMATION TEMPLATE.xls

Answer 3

Hi Hans

See path of each workbook below:

The main workbook = "S:\WAREHOUSE FILES\WAREHOUSE STOCK TAKES\STOCK TAKE AUTOMATION TEMPLATE.xlsm"

The template for the count = "S:\WAREHOUSE FILES\WAREHOUSE STOCK TAKES\VARIANCE RECOUNT 2 TEMPLATE.xlsm"

And both have a sheet called "VARIANCE RECOUNT 2"

Hope this helps.

Answer 4

Thanks. So it should be

    Set wst = Workbooks("STOCK TAKE AUTOMATION TEMPLATE.xlsm").Worksheets("VARIANCE RECOUNT 2")

And the line

            wst.Range("E" & r).FormulaR1C1 = "=VLOOKUP(RC2, 'VARIANCE RECOUNT 2 TEMPLATE'!C2:C4, 3, FALSE)"

should become

            wst.Range("E" & r).FormulaR1C1 = "=VLOOKUP(RC2, '[VARIANCE RECOUNT 2 TEMPLATE.xlsm]VARIANCE RECOUNT 2'!C2:C4, 3, FALSE)"

Answer 5

Hi Hans

So the "Subscript out of range" error is gone now.

On the first try it kept looping but not doing anything.

On the second try I got the following error: On debug, see highlighted error: