I started recording a macro. A simple copy and paste by the vlookup table.

[Abhinav Shori]

I started recording a macro. A simple copy and paste by the vlookup table.

I started at cell A1 with formula: vlookup(a7, sheet2!a1:d5, 3, false)
A2: vlookup(a7, sheet2!a1:d5, 3, false)
i stopped the Macro and it worked perfectly fine.
Now when I make my active cell as B1, I would like same result to appear in cells B1 and B2
but
what happens is that I get result in B1 (when I run the Macro) and then it goes to A2. Is there any way to keep the Macro going along the same row????

Thanks in advance

 

[Smallman]

Hi Abhinav

For anyone looking at your problem it is a little confusing. You are getting exactly the same result in A1 as A2. Why would you replicate your result in 2 cells. This question is not answered in the remainder of your post so most people just leave your post hanging because they can not grasp what you are trying to do.

An example workbook would help with expected results. I suspect you just need to make the rows absolute in your formula.

Take care

Smallman

 

[Hui]

I'd also suggest attaching a sample file with the code you have tried
When you goto B1 what should the formula be?
vlookup(B7, sheet2!a1:d5, 3, false)
or
vlookup(a7, sheet2!B1:E5, 3, false)

etc ?

 

[Abhinav Shori]

Please find the excel file attached.
Macro runs with control+shift+Q
so I ran the macro in A1 and I get the results in A1 and A2
if I run the macro in B1, I would like to get results in B1 and B2 but it does not happen the cursor goes back to A2 (B2 is not populate with the result).
I would like to record a macro which runs based on the active cell, is that possible

 

[Abhinav Shori]

I'd also suggest attaching a sample file with the code you have tried
When you goto B1 what should the formula be?
vlookup(B7, sheet2!a1:d5, 3, false)
or
vlookup(a7, sheet2!B1:E5, 3, false)

etc ?

Same formula should be applied.. in the macro, at the end, I want to copy the result and paste it in the same cells (by special paste 123)

 

[Hui]

Replace

  ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[7], Sheet2!RC:R[4]C[3], 3, FALSE)"

with

  ActiveCell.Formula = "=VLOOKUP(H1, Sheet2!$A$1:$D$5, 3, FALSE)"