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.
RishiSingh05Asked:
Who is Participating?
 
nutschCommented:
OK, update:


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 & "'!C1:C22," & 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

0
 
nutschCommented:
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
0
 
RishiSingh05Author Commented:
nothing happens ;)
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
RishiSingh05Author Commented:
may not be your fault.  I just found that I am using a "macro-free workboo".  I need to change this setting  ...
0
 
RishiSingh05Author Commented:
Bingo.  Seems to have worked.  My data is huge so I have to do some manual validations just for comfort.
0
 
nutschCommented:
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
0
 
RishiSingh05Author Commented:
ok  ... thanks
0
 
RishiSingh05Author Commented:
I am trying to understand the code so that I can modify for future use.  

In Line 13:  what is RC11; and C1: C22  ?
0
 
nutschCommented:
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")

0
 
RishiSingh05Author Commented:
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.
0
 
nutschCommented:
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
0
 
RishiSingh05Author Commented:
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
0
 
nutschCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.