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