How can I refer to different rows in a range and include the cell addresses
into a formula being entered onto a sheet by macro?

What I'm trying to do is:

- Range = a1:m100 of another sheet
- formula: .Range("A49").FORMULA = "=LOOKUP(cells in first row in range)"
- more code in a loop
- back to start of loop
- formula: .Range("A49").FORMULA = "=LOOKUP(cells in second row in range)"
- and so on...

I hope this makes sense - the code is very long and detailed and only need
help with this small (but crucial!!) part...

TIA

Dim cnt As Long
Dim rOS As Range 'Range offset
Dim FormulaString As String
Set rOS = Selection.Cells(1, 1)
For cnt = 1 To YourRange.Rows.Count
FormulaString = "'" & YourAnotherSheet & "'!" & Cells(rOS.Row + cnt
- 1, rOS.Column).Address(False,False) & ":" & _
Cells(rOS.Row + cnt - 1, rOS.Column +
YourRange.Columns.Count).Address(False,False)
Range("A49").Formula = "=VLookup(" & FormulaString &
otherparameters")"
Next

HTH

Die_Another_Day
ewan7279 wrote:
How can I refer to different rows in a range and include the cell addresses
into a formula being entered onto a sheet by macro?

What I'm trying to do is:

- Range = a1:m100 of another sheet
- formula: .Range("A49").FORMULA = "=LOOKUP(cells in first row in range)"
- more code in a loop
- back to start of loop
- formula: .Range("A49").FORMULA = "=LOOKUP(cells in second row in range)"
- and so on...

I hope this makes sense - the code is very long and detailed and only need
help with this small (but crucial!!) part...

TIA