Solved

Get sheet name dynamically in vlookup

Posted on 2011-02-11
17
501 Views
Last Modified: 2012-05-11
Hello

I am attaching a macro code. I need to fetch the previous sheetname dynamically in my vlookup macro

Please help
TIA
Sub Macro1()
Dim SheetName As String
Dim lRow As Long
lRow = Cells(Rows.Count, "I").End(xlDown).Row
Set sh1 = ActiveSheet.Previous

SheetName = sh1.Name

    Range("H3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(IFERROR(VLOOKUP(RC[1],SheetName!R3C[1]:R801C[1],1,0),""Done"")<>""Done"","""",IFERROR(VLOOKUP(RC[1],SheetName!R3C[1]:R801C[1],1,0),""Done""))"
    Range("H3").Select
    Selection.AutoFill Destination:=Range("H3:H" & lRow)
    Range("H3:H" & lRow).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("H3").Select
    Application.CutCopyMode = False
End Sub

Open in new window

0
Comment
Question by:ExpertHelp79
17 Comments
 
LVL 10

Expert Comment

by:Makrini
ID: 34869726
What do you mean by "previous" sheet?  The sheet to the left? The last sheet that was used?
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34869728
use this replacement line to apply your SheetName variable

hth

Dave
ActiveCell.FormulaR1C1 = _
        "=IF(IFERROR(VLOOKUP(RC[1],'" & SheetName & "'!R3C[1]:R801C[1],1,0),""Done"")<>""Done"","""",IFERROR(VLOOKUP(RC[1],'" & SheetName & "'!R3C[1]:R801C[1],1,0),""Done""))"

Open in new window

0
 
LVL 42

Expert Comment

by:dlmille
ID: 34869751
Declare this at the top of one of your regular modules:
Public myPrevious As Worksheet

Put this in ThisWorkbook module:
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Set myPrevious = ActiveSheet
    Debug.Print myPrevious.Name, Sh.Name
End Sub

Then, you can use:

Sub Macro1()
Dim SheetName As String
Dim lRow As Long
dim sh1 as Worksheet

lRow = Cells(Rows.Count, "I").End(xlDown).Row

Set sh1 = myPrevious
SheetName = sh1.Name

    Range("H3").Select

    ActiveCell.FormulaR1C1 = _
        "=IF(IFERROR(VLOOKUP(RC[1]," & SheetName & "!R3C[1]:R801C[1],1,0),""Done"")<>""Done"","""",IFERROR(VLOOKUP(RC[1]," & SheetName & "!R3C[1]:R801C[1],1,0),""Done""))"

Note SheetName is a VBA Variable, so you have to concatenate it with the & character, as above...

Dave
0
Independent Software Vendors: 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 50

Expert Comment

by:Dave Brett
ID: 34869832
Dave

1) >Declare this at the top of one of your regular modules
why? That code will run on every sheet change rather than just the specific sheet when the macro occurs

2)  And your SheetName variable will fall over if there is a non alphanumeric character in the prior sheet name. Thats why I include used
'" & SheetName & "'!
in my prior comment

Cheers

Dave #2
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 34869872
Dave #2

The declaration was for the global variable I dimensioned called myPrevious

agreed.  my use of sheetname should be corrected to:

    ActiveCell.FormulaR1C1 = _
        "=IF(IFERROR(VLOOKUP(RC[1],'" & SheetName & "'!R3C[1]:R801C[1],1,0),""Done"")<>""Done"","""",IFERROR(VLOOKUP(RC[1],'" & SheetName & "'!R3C[1]:R801C[1],1,0),""Done""))"

Asided from correcting his syntax, I believe he really was trying to understand how to get the previous sheetname and that's what myPrevious is for....

So entire corrected set of code is now:
Declare this at the top of one of your regular modules:
Public myPrevious As Worksheet

Put this in ThisWorkbook module:
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Set myPrevious = ActiveSheet
    Debug.Print myPrevious.Name, Sh.Name
End Sub

Then, you can use:

Sub Macro1()
Dim SheetName As String
Dim lRow As Long
dim sh1 as Worksheet

lRow = Cells(Rows.Count, "I").End(xlDown).Row

Set sh1 = myPrevious
SheetName = sh1.Name

    Range("H3").Select

    ActiveCell.FormulaR1C1 = _
        "=IF(IFERROR(VLOOKUP(RC[1],'" & SheetName & "'!R3C[1]:R801C[1],1,0),""Done"")<>""Done"","""",IFERROR(VLOOKUP(RC[1],'" & SheetName & "'!R3C[1]:R801C[1],1,0),""Done""))"

etc....
end sub


Note SheetName is a VBA Variable, so you have to concatenate it with the & character, as above...

Dave

0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34869874
... okay I figured out (1).  I presume that your were looking to match the prior active sheet rather than the current code that looks to the immediate left. So scratch that as an interpretation rather than approach

0
 
LVL 42

Expert Comment

by:dlmille
ID: 34869882
:) I was and believe he was, too.

its late...and I can't tell the diff between " and ''' anymore...  

nite,

Dave
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34869893
:)

And that's a very interesting career profile you have there

Cheers

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34869922
:)
0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34869924
The code is doing vlookup in upward direction and if i am giving
lRow = Cells(Rows.Count, "I").End(xlDown).Row
its not taking the range and doing a update for all the existing rows in excel
Sub CompareRecords()
Dim SheetName As String
Dim LRow As Long

Range("H3").Select
LRow = Cells(Rows.Count, "I").End(xlUp).Row

Set sh1 = ActiveSheet.Previous
SheetName = sh1.Name

    
     ActiveCell.FormulaR1C1 = _
        "=IF(IFERROR(VLOOKUP(RC[1],'" & SheetName & "'!R3C[1]:R801C[1],1,0),""Done"")<>""Done"","""",IFERROR(VLOOKUP(RC[1],'" & SheetName & "'!R3C[1]:R801C[1],1,0),""Done""))"
    Range("H3").Select
    Selection.AutoFill Destination:=Range("H3:H" & LRow)
    Range("H3:H" & LRow).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("H3").Select
    Application.CutCopyMode = False
End Sub

Open in new window

0
 
LVL 42

Expert Comment

by:dlmille
ID: 34869946
Well I didn't pickup the .previous - I guess my autosenses are off, lol....

the range you've defined is:

=IF(IFERROR(VLOOKUP(I3,Sheet2!I$3:I$801,1,0),"Done")<>"Done","",IFERROR(VLOOKUP(I3,Sheet2!I$3:I$801,1,0),"Done"))

can you upload a sample sheet right quick?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34869950
So that's it - you're one column off

You're copying column H down and the vlookup parameters are looking at column I

COuld this be your error?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34869959
Does your active cell need to be one cell to the left?  or your R1C1 references to be RC as opposed to RC[1]??

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34869964
arrg - MY active cell was off.  Your code doesn't inform where the active cell is, but if its in column G, then you'll get the vlookup in column H - is that correct?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34869974
Ok - I made up some data and set my active cell to column G and the macro ran beautifully.

Check your activecell position before it runs this macro

VLOOKUP TABLE exists in column I-whatever, activecell must be in column G, then your vlookup goes in column H and it all worked for me for these conditions.

Dave
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35221081
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article will show you how to use shortcut menus in the Access run-time environment.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

685 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