Link to home
Start Free TrialLog in
Avatar of ExpertHelp79
ExpertHelp79

asked on

Get sheet name dynamically in vlookup

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

Avatar of Makrini
Makrini
Flag of Australia image

What do you mean by "previous" sheet?  The sheet to the left? The last sheet that was used?
Avatar of Dave
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

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
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
ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
... 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

:) I was and believe he was, too.

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

nite,

Dave
:)

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

Cheers

Dave
Avatar of ExpertHelp79
ExpertHelp79

ASKER

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

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