Solved

Get sheet name dynamically in vlookup

Posted on 2011-02-11
17
510 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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
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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

624 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