zoom38
05-15-2007, 01:41 PM
Hello eveyone,
Can someone help make the following formulas to be built by a loop. I have only listed the first 4 of 28 formulas which goes up to column CE. There have been several times where I have had to change the lookup table size and modify each formula individually so i'd rather have it done with a loop.
For r = 6 To AllRows
Cells(r, 3).Formula = "=If($B" & r & "=0, 89," & _
"If(ISNA(Vlookup($B" & r & ",$CH$6:$CI$89,2,0)),89," & _
"(Vlookup($B" & r & ",$CH$6:$CI$89,2,0))))"
Next r
For r = 6 To AllRows
Cells(r, 6).Formula = "=If($E" & r & "=0, 89," & _
"If(ISNA(Vlookup($E" & r & ",$CH$6:$CI$89,2,0)),89," & _
"(Vlookup($E" & r & ",$CH$6:$CI$89,2,0))))"
Next r
For r = 6 To AllRows
Cells(r, 9).Formula = "=If($H" & r & "=0, 89," & _
"If(ISNA(Vlookup($H" & r & ",$CH$6:$CI$89,2,0)),89," & _
"(Vlookup($H" & r & ",$CH$6:$CI$89,2,0))))"
Next r
For r = 6 To AllRows
Cells(r, 12).Formula = "=If($K" & r & "=0, 89," & _
"If(ISNA(Vlookup($K" & r & ",$CH$6:$CI$89,2,0)),89," & _
"(Vlookup($K" & r & ",$CH$6:$CI$89,2,0))))"
Next r
This is what I came up with but it makes the column as a number not a letter.
For c = 3 To 84 Step 3
For r = 6 To AllRows
Cells(r, c).Formula = "=If(" & c & r & "=0, 89," & _
"If(ISNA(Vlookup(" & c & r & ",$CH$6:$CI$89,2,0)),89," & _
"(Vlookup(" & c & r & ",$CH$6:$CI$89,2,0))))"
Next r
Next c
Would R1C1 do it? If so how?
Thanks
Gary
Can someone help make the following formulas to be built by a loop. I have only listed the first 4 of 28 formulas which goes up to column CE. There have been several times where I have had to change the lookup table size and modify each formula individually so i'd rather have it done with a loop.
For r = 6 To AllRows
Cells(r, 3).Formula = "=If($B" & r & "=0, 89," & _
"If(ISNA(Vlookup($B" & r & ",$CH$6:$CI$89,2,0)),89," & _
"(Vlookup($B" & r & ",$CH$6:$CI$89,2,0))))"
Next r
For r = 6 To AllRows
Cells(r, 6).Formula = "=If($E" & r & "=0, 89," & _
"If(ISNA(Vlookup($E" & r & ",$CH$6:$CI$89,2,0)),89," & _
"(Vlookup($E" & r & ",$CH$6:$CI$89,2,0))))"
Next r
For r = 6 To AllRows
Cells(r, 9).Formula = "=If($H" & r & "=0, 89," & _
"If(ISNA(Vlookup($H" & r & ",$CH$6:$CI$89,2,0)),89," & _
"(Vlookup($H" & r & ",$CH$6:$CI$89,2,0))))"
Next r
For r = 6 To AllRows
Cells(r, 12).Formula = "=If($K" & r & "=0, 89," & _
"If(ISNA(Vlookup($K" & r & ",$CH$6:$CI$89,2,0)),89," & _
"(Vlookup($K" & r & ",$CH$6:$CI$89,2,0))))"
Next r
This is what I came up with but it makes the column as a number not a letter.
For c = 3 To 84 Step 3
For r = 6 To AllRows
Cells(r, c).Formula = "=If(" & c & r & "=0, 89," & _
"If(ISNA(Vlookup(" & c & r & ",$CH$6:$CI$89,2,0)),89," & _
"(Vlookup(" & c & r & ",$CH$6:$CI$89,2,0))))"
Next r
Next c
Would R1C1 do it? If so how?
Thanks
Gary