Consulting

Results 1 to 6 of 6

Thread: Solved: macro to copy data - down other columns

  1. #1
    VBAX Tutor
    Joined
    Aug 2007
    Posts
    294
    Location

    Solved: macro to copy data - down other columns

    Hi

    Need to change the current macro so that is populates the formula down columns B:F based (staring at row 6) on column A6 until the las cell with data....

    [VBA]
    Sub Macro4()
    Workbooks.Open Filename:="D:\Users\LKassai\Desktop\VOCH\SA38.xlsm"
    Windows("Margin File.xlsm").Activate
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],SA38.xlsm!C1:C8,2,0)"
    Range("C6").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],SA38.xlsm!C1:C8,5,0)"
    Range("D6").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],SA38.xlsm!C1:C8,6,0)"
    Range("E6").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],SA38.xlsm!C1:C8,7,0)"
    Range("F6").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],SA38.xlsm!C1:C8,8,0)"
    Range("F7").Select

    End Sub
    [/VBA]

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,272
    Location
    [vba]

    Sub Macro4()
    Dim Lastrow As Long
    Dim i As Long

    With ActiveSheet

    Workbooks.Open Filename:="D:\Users\LKassai\Desktop\VOCH\SA38.xlsm"

    Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("B6").Resize(Lastrow - 5).FormulaR1C1 = "=VLOOKUP(RC[-1],SA38.xlsm!C1:C8,2,0)"
    .Range("C6").Resize(Lastrow - 5).FormulaR1C1 = "=VLOOKUP(RC[-2],SA38.xlsm!C1:C8,5,0)"
    .Range("D6").Resize(Lastrow - 5).FormulaR1C1 = "=VLOOKUP(RC[-3],SA38.xlsm!C1:C8,6,0)"
    .Range("E6").Resize(Lastrow - 5).FormulaR1C1 = "=VLOOKUP(RC[-4],SA38.xlsm!C1:C8,7,0)"
    .Range("F6").Resize(Lastrow - 5).FormulaR1C1 = "=VLOOKUP(RC[-5],SA38.xlsm!C1:C8,8,0)"
    End With

    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Tutor
    Joined
    Aug 2007
    Posts
    294
    Location
    thanks xld wroks prefectly

  4. #4
    VBAX Tutor
    Joined
    Aug 2007
    Posts
    294
    Location
    hi

    Need to finish this macro so that it populates the results in columns M:N based on column A data last cell

    [VBA]
    Sub Vlookup_KE_24()

    Application.ScreenUpdating = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-12],'KE24'!C1:C13,7,0)"
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-13],'KE24'!C1:C13,13,0)"

    Application.ScreenUpdating = True
    End Sub
    [/VBA]

  5. #5
    VBAX Tutor
    Joined
    Aug 2007
    Posts
    294
    Location

    assist with part completed macro

    hi

    Need to finish this macro so that it populates the results in columns M:N based on column A data last cell


    VBA:
    Sub Vlookup_KE_24() Application.ScreenUpdating = False ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-12],'KE24'!C1:C13,7,0)" ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-13],'KE24'!C1:C13,13,0)" Application.ScreenUpdating = True End Sub

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,272
    Location
    Same as before, just change the ranges.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •