Solved

Get sheet name dynamically in vlookup

Posted on 2011-02-11
17
488 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 41

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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 41

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 41

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 41

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 41

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 41

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 41

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 41

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 41

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Formula 5 46
Find and Replace Function not working in Excel 13 48
Need to put a web footnote into a text book 7 22
Saving excel document in lotus script 4 13
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 …
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

803 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