?
Solved

Macro to automate a task in Excel

Posted on 2011-05-12
13
Medium Priority
?
336 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:RishiSingh05
  • 7
  • 6
13 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 35749082
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
 

Author Comment

by:RishiSingh05
ID: 35749205
nothing happens ;)
0
 

Author Comment

by:RishiSingh05
ID: 35749233
may not be your fault.  I just found that I am using a "macro-free workboo".  I need to change this setting  ...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Accepted Solution

by:
nutsch earned 2000 total points
ID: 35749263
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
 

Author Comment

by:RishiSingh05
ID: 35749314
Bingo.  Seems to have worked.  My data is huge so I have to do some manual validations just for comfort.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35749349
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
 

Author Comment

by:RishiSingh05
ID: 35749401
ok  ... thanks
0
 

Author Comment

by:RishiSingh05
ID: 35749505
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
 
LVL 39

Expert Comment

by:nutsch
ID: 35749561
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
 

Author Comment

by:RishiSingh05
ID: 35749845
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
 
LVL 39

Expert Comment

by:nutsch
ID: 35749871
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
 

Author Comment

by:RishiSingh05
ID: 35750075
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
 
LVL 39

Expert Comment

by:nutsch
ID: 35750191
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question