Solved: A Loop To Create A Worksheet Formula

[zoom38]

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.


[VBA]
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
[/VBA]


This is what I came up with but it makes the column as a number not a letter.

[VBA]
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
[/VBA]

Would R1C1 do it? If so how?

Thanks
Gary

[Bob Phillips]

I guess this ius what you want

[vba]

ilastrow = Cells(Rows.Count, "B").End(xlUp).Row
For r = 6 To ilastrow
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))))"

Cells(r, 3).Copy Cells(r, 6)
Cells(r, 3).Copy Cells(r, 9)
Cells(r, 3).Copy Cells(r, 12)
Next r
[/vba]

[Charlize]

A little late, but I tried this one :[VBA]Option Explicit
Option Base 1
Sub fill_with_formula()
Dim row As Long
'position where formula must be filled in
Dim c_pos As Long
'letters of the columns in formula
Dim c_array
'for each letter in array of columnletters
Dim c_item1
'array holding the columnletters where formule must come
Dim b_array
'starting row for formula
row = 6
'starting position in array
c_pos = 1
'defining the values of the arrays
b_array = Array("B", "E", "H", "K")
c_array = Array("C", "F", "I", "L")
For Each c_item1 In b_array
Range(c_array(c_pos) & row).Formula = "=If(" & c_item1 & row & "=0,89," & _
"if(isna(vlookup(" & c_item1 & row & ",$CH$6:$CI$89,2,0)),89," & _
"(Vlookup(" & c_item1 & row & ",$CH$6:$CI$89,2,0))))"
c_pos = c_pos + 1
Next c_item1
End Sub[/VBA]

[zoom38]

Thanks XLD and Charlize.
XLD yours was simpler so I modified it and used it.
Charlize, yours was a little over my head so I didn't use it.

Thank you
Gary

This is what I used.

[vba]
iRows = Cells(Rows.Count, "B").End(xlUp).Row
For r = 6 To iRows
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))))"

For c = 6 To 84 Step 3
Cells(r, 3).Copy Cells(r, c)
Next c
Next r
[/vba]

[Norie]

Gary

I see this is marked as solved.

But are you sure you even need 2 loops in the first place?
[vba]
irows = Cells(Rows.Count, "B").End(xlUp).Row
With Range("C6:C" & irows)
.Formula = "=If(B6=0, 89," & _
"If(ISNA(Vlookup(B6,$CH$6:$CI$89,2,0)),89," & _
"(Vlookup(B6,$CH$6:$CI$89,2,0))))"
For c = 3 To 84 Step 3
.Copy .Offset(, c)
Next c
End With[/vba]

[zoom38]

Thanks Norie, your method also works. Is this method anymore efficient than the other methods?

[Norie]

I don't rightly know.

But I would think only using 1 loop instead of 2 might be.

As long as whatever you use works that's the most important thing I think.)

[Bob Phillips]

Yours will definitely be quicker, the bigger the loop, the greater the saving.