asked on

Macro to automate a task in Excel

My Excel worksheet has 2 tabs called “MSTR_ACCOUNTS_3” (let us refer to it as Tab1) and “MSTR_custs” (let us refer to it as Tab2).

Col K of Tab1 is called “Other relationship” and contains numeric data.

Col A of Tab2 is called “RRN” and has numeric data.

I would like a macro which reads each “Other relationship” value in Tab1 and searches for a match in “RRN” of Tab2. Where a match is found it grabs the data relating to that RRN in rows D to V from Tab2 and copies it to the match in Tab1 starting in col AP.

Thanks.

nutsch

Try this code,

Sub asdfasdfg()
Dim sht1 As Worksheet, sht2 As Worksheet, lLoop As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

Set sht1 = Sheets("MSTR_ACCOUNTS_3")
Set sht2 = Sheets("MSTR_custs")

For lLoop = 0 To 18
    With sht1.Range("AP2:AP" & sht1.Cells(Rows.Count, "K").End(xlUp).Row).Offset(, lLoop)
        .FormulaR1C1 = "=VLOOKUP(RC11,'" & sht2.Name & "'!R1C1:R17C22," & lLoop + 4 & ",0)"
        .Value = .Value
    End With
Next lLoop

sht1.Cells.Replace What:="#N/A", Replacement:="", lookat:=xlWhole

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

End Sub

Open in new window

Thomas

RishiSingh05

ASKER

nothing happens ;)

RishiSingh05

ASKER

may not be your fault. I just found that I am using a "macro-free workboo". I need to change this setting ...

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RishiSingh05

ASKER

Bingo. Seems to have worked. My data is huge so I have to do some manual validations just for comfort.

nutsch

If you comment out the
.Value = .Value
line, by putting an inverted comma in front, the macro will leave the formulas in (it's just a few vlookups) and it might be easier to audit that way.

T

RishiSingh05

ASKER

ok ... thanks

RishiSingh05

ASKER

I am trying to understand the code so that I can modify for future use.

In Line 13: what is RC11; and C1: C22 ?

nutsch

The R1C1 notation is a way of referring to cells by the row and column information:
RC11 means same row column 11 ("K")
C1:C22 means range from column 1 to 22 ("A:V")

RishiSingh05

ASKER

Last question: lLoop + 4

What does "4" signify? Would I need to change it if I modified the code to look up stuff in different columns?

Thanks.

nutsch

You want column D through V, so 4 to 22. I could do a loop from 4 to 22 or a loop from 0 to 18 and add 4 (which I did). To pick up column F through X, you would use
lLoop+6

Thomas

RishiSingh05

ASKER

So I still want Col D thru V. Did I make the right changes below? Thanks.

For lLoop = 4 To 22
With sht1.Range("AP2:AP" & sht1.Cells(Rows.Count, "K").End(xlUp).Row).Offset(, lLoop)
.FormulaR1C1 = "=VLOOKUP(RC11,'" & sht2.Name & "'!C1:C22," & lLoop
.Value = .Value
End With
Next lLoop

nutsch

For lLoop = 4 To 22
With sht1.Range("AP2:AP" & sht1.Cells(Rows.Count, "K").End(xlUp).Row).Offset(, lLoop-4)
.FormulaR1C1 = "=VLOOKUP(RC11,'" & sht2.Name & "'!C1:C22," & lLoop & ",0)"
.Value = .Value
End With
Next lLoop