VB Loop Help Required

[mandye]

I have vb code set up to add a vlookup formula to the activecell. I was trying to add a For Each In Next loop to it so that it would populate the range with the relative formula, but instead it returning exactly the same formula for each and every cell within the range.


Sub Lookup_Formula()

Dim a$, c$, e$, f$
Dim r As Range
Set r = ActiveCell

a = r.Offset(0, -4).Value
b = r.Column - 4
c = r.Offset(0, -b).Value
d = r.Row - 10
e = r.Offset(-d, 0).Value

For Each r In Range("overviewall")
r.Formula = "=vlookup(" & a & "," & c & "_overview," & e & "+2,false)"
Next r

End Sub

Can someone point me in the right direction has how to rectify the code.

Many thanks

 

[Andrew Poulsom]

It's difficult without seeing your data, but would this work?

Sub Test()
    Range("overviewall").FormulaR1C1 = "=VLOOKUP(RC[-4],INDIRECT(RC[-1]&""_overview""),R10C+2,FALSE)"
End Sub

 

[mandye]

This almost works, but i need to make some of the references absolute rather than relative. Can you give me guidance on how to do this?

 

[Andrew Poulsom]

Edit the formula in the first cell in your overviewall range so that it contains the correct mix of absolute and relative addresses. Then choose Tools|Options and check R1C1 style references on the General tab. Compare the formula with my code and amend as necessary. Revert to A1 style references when done.

 

[mandye]

I have modified your code so that it looks like this:

Sub Test()
Range("overviewall").FormulaR1C1 = "=VLOOKUP(RC1,INDIRECT(RC4&""_overview""),R10C+2,FALSE)"
End Sub


However, the part that says INDIRECT(RC4&""_overview"") - I actually need this to give me a named range, as it is the table_array for the vlookup formula.

The first part is in RC4 and it ends in "_overview", e.g.,

inspection_overview is the range I need to look up.

Can you help me modify the code so it will return this?

Thank you for your time on this one!!

 

[Andrew Poulsom]

If the cell in column 4 on the same row contains "inspection" (no quotes) then:

INDIRECT(RC4&""_overview"")

will return inspection_overview and it will be used as a named range. You need to use INDIRECT otherwise it will be treated as a string.

If the named range is not dependant on that cell's value, you can use:

Range("overviewall").FormulaR1C1 = "=VLOOKUP(RC[-4],inspection_overview,R10C+2,FALSE)"