Use ActiveCell from former sheet in formula

**rubix** · 08-26-2010, 05:09 AM

Hi,

I need to use the ActiveCell from sheet1 (highlighted in red) in a vlookup entered into a cell in sheet 2. The ActiveCell I want to use will change in every loop until the end of a list. Can you help?

Sub Test2()
Sheets("Sheet1").Select
Range("B8").Select
Do Until IsEmpty(ActiveCell)
Sheets("Sheet2").Select
Range("O4").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(ActiveCell,'Sheet1'!C[-13]:C[-12],2,FALSE)"
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub

**rubix** · 08-26-2010, 05:10 AM

Please ignore the comments in the script.

Sub Test2()
Sheets("Sheet1").Select
Range("B8").Select
Do Until IsEmpty(ActiveCell)
Sheets("Sheet2").Select
Range("O4").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(ActiveCell,'Sheet1'!C[-13]:C[-12],2,FALSE)"
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub

**rubix** · 08-26-2010, 07:53 AM

Any suggestions?

**romperstomper** · 08-26-2010, 09:05 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here