98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Vlookup
Vlookup
Resolved · Medium Priority · Version 2007
Wed 20 Jul 2011, 15:22
Edited on Thu 21 Jul 2011, 13:40Delegate Gari said...
Gari has attended:
Excel VBA Intro Intermediate course
Vlookup
Hi,
I am trying to write some code that will allow my sheet that is running this sub-procedure to look at the 6th sheet of my workbook and perform a vlookup on it.
My issue is that the name of my 6th sheet will change daily (it is format DD.MM.YY - e.g. today is called 20.07.11, tomorrow will be 21.07.11), as it contains the previous sheet's data... how do I reference the 6th sheet of the workbook (The workbook is called "SC_Pending - GD Test" at the moment) ?
We're not sure, but we think it may be an issue with my Dim?
Any help would be MUCH appreciated, as this is driving me crazy!
Best,
Gari
Sub VLOOKUPS()
Dim NextSheet As Variant
Set NextSheet = Workbooks("SC_PENDING - GD TEST.xlsm").Sheets(6)
StrLastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
'creates StrLastRow
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-33],NextSheet!C[-33]:C,34,FALSE)"
Range("AH5").Select
Selection.AutoFill Destination:=Range("$AH$2:$AH$" & StrLastRow)
Range("A1").Select
End Sub
Sun 24 Jul 2011, 09:55Trainer Stephen said...
RE: vlookup
Hi Gari
Thanks for the question
Can you confirm the error number and message that occurs when you run the code?. Also which line the code breaks on
Thanks
Stephen
Mon 25 Jul 2011, 14:38Delegate Gari said...
RE: vlookup
Hi Stephen,
I've had to remove the code and try to do this with static sheets, as I needed to get this launched - so that's been a bit of a stop gap (it copies sheet 6 into a sheet permanently called 'Today's Data').
I would still like to try and get it working this way though as these static sheets are proving a bit clunky.
The code has been modified since I submitted last week and is now as below:
Sub VLOOKUPS()
Dim PreviousSheet As Variant
Set PreviousSheet = Worksheets(6)
strLastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
'creates StrLastRow
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-33],'Worksheets(6)'!C[-33]:C,34,FALSE)"
Range("AH5").Select
Selection.AutoFill Destination:=Range("$AH$1:$AH$" & strLastRow)
Range("A1").Select
When we run this, we get 1004 - AutoFill Method of Range Class Failed (but I suspect this is because I just hit cancel when it opens a windows brower that asks us to navigate to the workbook and worksheet that we want - which shouldn't be doing, as I've already told this via DIM?).
Many thanks for any help with this!
Tue 11 Oct 2011, 12:45Trainer Mark said...
RE: vlookup
Hello Gari,
I notice you are referencing the index number of the sheet, your code is
Set NextSheet = Workbooks("SC_PENDING - GD TEST.xlsm").Sheets(6)
Try replacing the number 6 with the actual sheet name, if you look in the property panel for Name (top entry), it will say Sheet4, or Sheet12 etc..), in your code put this name in.
Set NextSheet = Workbooks("SC_PENDING - GD TEST.xlsm").Sheet3
Remember this name is the code name and not the name you see on the sheet tab, such as your changing date.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Mark
Microsoft Office Specialist Trainer
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Importing Numbers in Excel 2010Occasionally, when importing data into Excel you find that the some of the imported values are treated as text. |
