PDA

View Full Version : Solved: Cell Reference



slamet Harto
02-04-2009, 09:59 PM
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
02-05-2009, 12:24 AM
all,

please find the attached for your reference.

your response is highly appreciated it.
Rgds, Harto

lucas
02-05-2009, 08:49 AM
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
02-05-2009, 03:32 PM
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
02-05-2009, 04:05 PM
Hi Malcolm.....:hide:
Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp)).FillDown
Is it because of the -1

mdmackillop
02-05-2009, 04:14 PM
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
02-05-2009, 08:55 PM
All,

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