VBA Lookup function

Question

I have a table in sheet1 of a workbook like this:

section  |  Class  
11       |  2  
11       |  2  
12       |  3  
12       |  3  
12       |  3  
13       |  4

On sheet 2 I have something like this:

section  |  Class  
11       |    
11       |   
11       |  
11       |  
11       |  
11       |   
12       |    
12       |    
12       |  
12       |  
12       |    
13       |

I want to use the lookup function in sheet2 to get the value for Class from sheet1 and this is the macro that is generated for the first cell only:

ActiveCell.FormulaR1C1 = _
        "=LOOKUP(RC[-1],Sheet1!RC[-1]:R[4]C[-1],Sheet1!RC:R[4]C)"

My actual formula is:

=LOOKUP(A2,Sheet1!A2:A6,Sheet1!B2:B6)

I would want it to be like:

=LOOKUP(A2,Sheet1!$A$2:A6,Sheet1!$B$2:B6)

but this not allowed. Could anybody help me fill in the Class column?

Subodh Tiwari sktneer · Accepted Answer · 2017-05-31 12:46:40Z

2

Try something like this...

Sub GetClasses()
Dim wsSource As Worksheet, wsDest As Worksheet
Dim lr1 As Long, lr2 As Long
Application.ScreenUpdating = False
Set wsSource = Sheets("Sheet1")
Set wsDest = Sheets("Sheet2")
lr1 = wsSource.Cells(Rows.Count, 1).End(xlUp).Row
lr2 = wsDest.Cells(Rows.Count, 1).End(xlUp).Row
wsDest.Range("B2:B" & lr2).Formula = "=LOOKUP(A2,'" & wsSource.Name & "'!$A$2:$A$" & lr1 & ",'" & wsSource.Name & "'!$B$2:$B$" & lr1 & ")"
Application.ScreenUpdating = True
End Sub

answered May 31, 2017 at 12:46

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Subodh Tiwari sktneer Over a year ago

Thanks for the feedback. :)

vacip · Accepted Answer · 2017-05-31 13:06:02Z

2

R1C1 reference style is a bit strange at first, but you should try to understand it if you are going to do VBA.

Square brackets denote relative reference, no brackets mean absolute reference:

R1C4 = Row 1, Column 4 = $D$1
R[1]C[-2] = Relative to where the formula is, 1 Row down and 2 Columns to the left.
[0] becomes nothing, so R[0]C[1] would actually become RC[1], meaning same row, one column to the right.

So in your case, the formula should look like this:

ActiveCell.FormulaR1C1 = _
    "=LOOKUP(RC[-1],Sheet1!R2C1:R[4]C[-1],Sheet1!R2C2:R[4]C)"

You can have Excel display formulas like this, it might help understand how this formula type works. In File/Options/Formulas, tick "R1C1 Reference Style".

Alternatively you can use .Formula instead of .FormulaR1C1 as @sktneer did. It is easier at first, though you will find it is more limited in its uses in VBA.

edited May 31, 2017 at 13:06

answered May 31, 2017 at 13:00

vacip

5,4563 gold badges29 silver badges56 bronze badges

1 Comment

SQLserving Over a year ago

Thanks for the quick intro to R1C1. Your first two points made way more sense than many of the websites I've gone through. Will be learning more about it for sure. It is relatively elegant.

Collectives™ on Stack Overflow

VBA Lookup function

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related