Posting formulas in cells

[JARichard74]

Trying to put the formula in cells C4,C5,C6 and then C8, C9, C10. proptbl.ListRows.Count is the number of proponents i.e. in this example = 3

For b = 1 To 2
ThisWorkbook.Worksheets("Price").Range("C" & b * 4 & ":C" & 3 + proptbl.ListRows.Count).Formula = "=VLOOKUP($B4,Data!$G$3:$I$13,3)"
Next b

 

[Osvaldo Palmeiro]

Try:

[C4].Resize(3) = "=VLOOKUP(B4,Data!$G$3:$I$13,3)"
[C8].Resize(3) = "=VLOOKUP(B8,Data!$G$3:$I$13,3)"

 

[JARichard74]

Works however how would I make it so that it adjusts based on the number of proponents and criteria e.g. 3 criteria and 4 proponents. See attached. Number of proponents could be as high as 40

 

[Osvaldo Palmeiro]

Sorry, I don't understand what you mean by "it adjusts based on the number of proponents and criteria e.g. 3 criteria and 4 proponents."
It adjusts exactily what? You mean adjust the range that will receive the formulas or you mean adjust any parameters in the context of the formula?
In which cells should the code read the "criteria" and "proponents" values? Give us examples, please.

 

[JARichard74]

If you look at the picture on my previous post I now have Proponent1, 2,3,4 as well as an extra line Row 13. The code puts the formula where it says #N/A. The number of criteria is
y = ThisWorkbook.Worksheets("Prep").Range("E7").Value ; and the number of proponent is z - 1. The C and B values would need to adjust based on y and z

[C4].Resize(z - 1) = "=VLOOKUP(B4,Data!$G$3:$I$13,3)"
[C9].Resize(z - 1) = "=VLOOKUP(B9,Data!$G$3:$I$13,3)"
[C14].Resize(z - 1) = "=VLOOKUP(B14,Data!$G$3:$I$13,3)"

Something like this which I tried but gives a syntax error

For i = 1 To y
[C & z * i - 1].Resize(z - 1) = "=VLOOKUP("B"& z * i - 1,Data!$G$3:$I$13,3)"
Next i