Run a macro on a selection of cells

Question

I wrote the following macro to help me on a VLOOKUP repetitive action. It works, but I can't manage to run it on several cells at the same time.

I guess there's a code to write at the beginning of the macro.

Help much appreciated ;-)

    Sub Croisement_ZANOX_BO()
'
' Croisement_ZANOX_BO Macro
'

'
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC3,BO!C[-18]:C[-11],1,FALSE)"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC3,BO!C[-19]:C[-12],2,FALSE)"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC3,BO!C[-20]:C[-13],3,FALSE)"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC3,BO!C[-21]:C[-14],4,FALSE)"
    Selection.NumberFormat = "dd/mm/yy;@"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC3,BO!C[-22]:C[-15],5,FALSE)"
    Selection.NumberFormat = "dd/mm/yy;@"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC3,BO!C[-23]:C[-16],6,FALSE)"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC3,BO!C[-24]:C[-17],7,FALSE)"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC3,BO!C[-25]:C[-18],8,FALSE)"
    Selection.NumberFormat = "# ##0,00 €"
End Sub

`ActiveCell` literally means **active cell**. What are you trying to do and what does not work? — , Jan 07 '14 at 13:34
@mehow but he's changing cell using `ActiveCell.Offset(0, 1).Select`. It's not a good code, but..... — Makah, Jan 07 '14 at 13:38
Insead of write the vlookup every time you could (i) record a macro (ii) write the code in the cell (iii) copy and paste or autofill. — Makah, Jan 07 '14 at 13:40

score 1 · Answer 1 · edited May 23 '17 at 11:50

1

You should avoid the use of .Select/ActiveCell etc as @Makah suggested. INTERESTING READ

If the formula that you want to use is say =VLOOKUP($C1,BO!D:XFA,N,FALSE) where n is the column number in the formula (based on your above code) and you want to put that from say D1 then use a simple loop like this

Sub Sample()
    Dim ws As Worksheet
    Dim n As Long, col As Long

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    col = 4 '<~~ For COl D

    With ws
        For n = 1 To 8
            .Cells(1, col).Formula = "=VLOOKUP($C1,BO!D:XFA," & n & ",FALSE)"
            col = col + 1
        Next n
    End With
End Sub

edited May 23 '17 at 11:50

Community

1
1

answered Jan 07 '14 at 13:48

Siddharth Rout

147,039
17
206
250

Hi Siddhart, Thanks a lot for your help. I'm getting an error 91 while using this : In fact I have 2 sheets in the same workbook, they have one column in common and I'm trying to automatically merge the data. Thanks a lot! `code`Sub Croisement_ZANOX_BO_2() Dim ws As Worksheet Dim n As Long, col As Long '~~> Change this to the relevant sheet Set ws = test.Sheets("Zanox") col = 4 '<~~ For COl D With ws For n = 1 To 8 .Cells(1, col).Formula = "=VLOOKUP($C1,BO!D:XFA," & n & ",FALSE)" col = col + 1 Next n End With End Sub – Khazd0rf Jan 07 '14 at 17:18

Run a macro on a selection of cells

1 Answers1