Solved

Get sheet name dynamically in vlookup

Posted on 2011-02-11
17
495 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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 is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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…

840 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