PDA

View Full Version : Solved: A Loop To Create A Worksheet Formula



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

Bob Phillips
05-15-2007, 02:41 PM
I guess this ius what you want



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

Charlize
05-15-2007, 02:51 PM
A little late, but I tried this one :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

zoom38
05-15-2007, 03:39 PM
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.


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

Norie
05-16-2007, 08:10 AM
Gary

I see this is marked as solved.

But are you sure you even need 2 loops in the first place?

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

zoom38
05-16-2007, 02:45 PM
Thanks Norie, your method also works. Is this method anymore efficient than the other methods?

Norie
05-16-2007, 04:05 PM
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
05-16-2007, 04:35 PM
Yours will definitely be quicker, the bigger the loop, the greater the saving.