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
Do Until ActiveCell = ""

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

ActiveCell.Offset(1, 0).Select

Loop

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:
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

[mdmackillop]

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

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

[lucas]

Hi Malcolm.....:hide:
Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp)).FillDown
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