CODEWITHSUNDEEP
vbaexcelloopsvlookup
Katie M
Katie M

Reputation: 13

VBA: Using VLookUp to Set Dynamic Values in loop?

New VBA user here...

I have set up a Macro that runs for each file in a folder, as a loop.

In one part of the macro, there is an equation that contains a value that differs for each file.

Here is the equation (the value that changes for each file is 0.2483, everything else stays the same): 

ActiveCell.FormulaR1C1 = "=(((" & signal_array(Element, 1) & ")-
R[-83]C)/R[-85]C)*1000*0.2483"

I have set up a table that lists each file name and its corresponding value in another workbook. I have attempted to use vLookUp within the equation to find the value based on the file name - the file of which is active.

Here's what I have so far, for which I get a "Run-Time Error: 1004" on:

ActiveCell.FormulaR1C1 = "=(((" & signal_array(Element, 1) & ")-
R[-88]C)/R[-90]C)*1000*(=VLOOKUP(" & ActiveWorkbook.Name & ",'[Calibration 
Curves.xlsm]Sample Weights'!A2:B10,2,FALSE))"

Suggestions on how to make this work?

Upvotes: 0

Views: 114

Answers (1)

dwirony
dwirony

Reputation: 5450

Try getting rid of the = in the formula and also use consistent cell notation (i.e. don't mix A1 notation with R1C1 notation): 

ActiveCell.FormulaR1C1 = "=(((" & signal_array(Element, 1) & ")-
R[-88]C)/R[-90]C)*1000*(VLOOKUP(" & ActiveWorkbook.Name & ",'[Calibration 
Curves.xlsm]Sample Weights'!R2C1:R10C2,2,FALSE))"

Upvotes: 1

Related Questions