Solved: Cell Reference

[slamet Harto]

hi there,

Glad to see you again!

A quick question, I want to match a data in reference cell

in this case, match a cell is F3, F4, F5 and so on.
Please advice how to do this
[VBA]Do Until ActiveCell = ""

ActiveCell(1, 3).Formula = "=INDEX(Area,MATCH(Data!F3,Area1,0),2)"

ActiveCell.Offset(1, 0).Select

Loop[/VBA]

Thanks in advance

[slamet Harto]

all,

please find the attached for your reference.

your response is highly appreciated it.
Rgds, Harto

[lucas]

See if this helps Harto:
[vba]Option Explicit
Sub LoopFormula()
Range("D3").Select
ActiveCell.Formula = "=INDEX(Area,MATCH(Data!F3,Area1,0),1)"
Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp)).FillDown
Range("E3").Select
ActiveCell.Formula = "=INDEX(Area,MATCH(Data!F3,Area1,0),2)"
Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp)).FillDown
End Sub[/vba]

[mdmackillop]

Steve,
There is a problem with your FillDown, It copies down C3 as well.

[VBA]Option Explicit

Sub LoopFormula()
Dim LRw As Long
LRw = Cells(Rows.Count, 1).End(xlUp).Row
Range("D3").Formula = "=INDEX(Area,MATCH(Data!F3,Area1,0),1)"
Range("E3").Formula = "=INDEX(Area,MATCH(Data!F3,Area1,0),2)"
Range("D3:E" & LRw).FillDown
End Sub[/VBA]

[lucas]

Hi Malcolm.....
[VBA]Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp)).FillDown [/VBA]
Is it because of the -1

[mdmackillop]

Yes. You're trying a sneaky way to get the bottom row, but it selects both columns
Maybe
Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp).offset(,1)).FillDown

[slamet Harto]

All,

That's work fine.
Many thank for great support.
best,
harto