CODEWITHSUNDEEP
vbaexcelcells
Khazd0rf
Khazd0rf

Reputation: 1

Run a macro on a selection of cells

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

Upvotes: 0

Views: 1328

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

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

Upvotes: 1

Related Questions