Vlookup Using VBA

[Swoootie]

Hi.

I am trying to perform a vlookup between two sheets which are likely to have varying names (relating to the month in which the report is run). In attempting to do this I keep on getting errors and I was wondering if there is a simple solution to my problem.

My code so far is:

Sub MON()
Dim DT As Date
Dim WKBOOKSTAB1 As Workbook
Dim ACCPRIME As Workbook

'INPUT BOX TAKES DATE FROM USER
DT = Application.InputBox("Enter a Date as MMM YYYY")
DT = Format(DT, "MMM YYYY")

'SPECIFYING PATH WHERE INFORMATION IS TO BE POPULATED
PATH = "R: \Test\"
'SPECIFYING PATH WHERE INFORMATION IS OUTPUT TO SAS
SPATH = "R:\Output\”

Set WKBOOKSTAB1 = Workbooks.Open(SPATH & " STAB _PRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")

Set ACCPRIME = Workbooks.Open(PATH & "Acc Prime.XLS")

'Windows( _
'"Acc Prime LT1.xls"). _
'Activate
'Sheets("LT1 Score Stability Indices").Select
'Range("G7").Select
'ANSWER = Application.VLOOKUP(LOOKUPVAL, WKBOOKSTAB2, 3, False)

' ActiveCell.Formula = "=VLOOKUP("(RANGE("A7"),& WKBOOKSTAB2, 3, FALSE"
'
' "=VLOOKUP("(RANGE("A7"),WKBOOKSTAB2!WKBOOKSTAB2,"3,0))"
' Range("G7").Select
'Selection.AutoFill Destination:=Range("G7:G16"), Type:=xlFillDefault
'Range("G7:G16").Select
'Range("C17").Select
'ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
'Range("C7").Select
'ActiveCell.FormulaR1C1 = "=RC[4]/100"
'Range("C7").Select
'Selection.AutoFill Destination:=Range("C7:C16"), Type:=xlFillDefault
'Range("C7:C16").Select

Any help on this would be greatly appreciated.

Please let me know if you require additional information on what I am trying to do.

Thank you in advance.

Hi.

I am trying to perform a vlookup between two sheets which are likely to have varying names (relating to the month in which the report is run). In attempting to do this I keep on getting errors and I was wondering if there is a simple solution to my problem.

My code so far is:

Sub MON()
Dim DT As Date
Dim WKBOOKSTAB1 As Workbook
Dim ACCPRIME As Workbook

'INPUT BOX TAKES DATE FROM USER
DT = Application.InputBox("Enter a Date as MMM YYYY")
DT = Format(DT, "MMM YYYY")

'SPECIFYING PATH WHERE INFORMATION IS TO BE POPULATED
PATH = "R: \Test\"
'SPECIFYING PATH WHERE INFORMATION IS OUTPUT TO SAS
SPATH = "R:\Output\”

Set WKBOOKSTAB1 = Workbooks.Open(SPATH & " STAB _PRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")

Set ACCPRIME = Workbooks.Open(PATH & "Acc Prime.XLS")

'Windows( _
'"Acc Prime LT1.xls"). _
'Activate
'Sheets("LT1 Score Stability Indices").Select
'Range("G7").Select
'ANSWER = Application.VLOOKUP(LOOKUPVAL, WKBOOKSTAB2, 3, False)

' ActiveCell.Formula = "=VLOOKUP("(RANGE("A7"),& WKBOOKSTAB2, 3, FALSE"
'
' "=VLOOKUP("(RANGE("A7"),WKBOOKSTAB2!WKBOOKSTAB2,"3,0))"
' Range("G7").Select
'Selection.AutoFill Destination:=Range("G7:G16"), Type:=xlFillDefault
'Range("G7:G16").Select
'Range("C17").Select
'ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
'Range("C7").Select
'ActiveCell.FormulaR1C1 = "=RC[4]/100"
'Range("C7").Select
'Selection.AutoFill Destination:=Range("C7:C16"), Type:=xlFillDefault
'Range("C7:C16").Select
End Sub


Any help on this would be greatly appreciated.

Please let me know if you require additional information on what I am trying to do.

Thank you in advance.

 

[Firefly2012]

If you just want the result of the vlookup then it would be calculated like this:

ANSWER = Application.VLookUp(Range("A7").Value, WKBOOKSTAB1.Sheets(1).Range("A:C"),3,FALSE)

Please note I have made an assumption that the target workbook if WKBOOKSTAB1 and it is the first sheet in the workbook and column A holds the ID to lookup.

 

[Swoootie]

Hi,

Thanks for your reply. Something that I forgot to mention in my earlier post was that, as the Excel files are produced using a statistical programming tool, the tabs for the Excel files that are output have the same name as the file itself. So in this case the file name and the tab name would contain the componants specified as wbookstab1.

In order to reference the tab name I have added the following bit of code:

Dim FILENAME6 as String

FILENAME6 = "STAB _PRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)

I take it that the syntax would now be:

ANSWER = Application.VLookUp(Range("A7").Value, WKBOOKSTAB1.FILENAME6.Range("A:C"),3,FALSE)

However, this is still not working. Can you see where I'm going wrong.

Thanks again.
</pre>

 

[Firefly2012]

If the sheet is the same name as the workbook then this implies that there is only a single sheet in the workbook (can't have two sheets named the same) so what I gave originally should work.

You have a syntactic error though:

ANSWER = Application.VLookUp(Range("A7").Value, WKBOOKSTAB1.Sheets(FILENAME6).Range("A:C"),3,FALSE)

 

[Swoootie]

Hi. I've tried your suggestion (and added to / edited the code as a form of trial and error), but it still isn't working. The code doesn't error, but it does not perform the vlookup. I've tried in manually and it works, so there aren't any problems with the values themselves.

The most recent version of my code is:

Dim ANSWER As Variant

Windows( _
"Acc Prime.xls"). _
Activate
Sheets("Stability").Select
Range("O7").Select
ActiveCell.
ANSWER = Application.VLOOKUP(Range("A7").VALUE, WKBOOKSTAB2.Sheets(FILENAME6).Range("A:C"), 3, 0)
Range("P7").Select
Selection.AutoFill Destination:=Range("P7:P17"), Type:=xlFillDefault
Range("P7:P17").Select

I had a feeling that the fill down wouldn't work, but I expected at least the selcted cell to display the correct value.

Hope this makes sense, but please let me know if you would like any further details.

Thanks

 

[Firefly2012]

You have populated a variable with the value returned by the Vlookup but at no point do you do anything with this value (like placing it in a cell) so you see no obvious result of running the code.

You would need to place the value in a cell eg like:

Range("P7").Value = ANSWER

 

[Swoootie]

Hi,

Thanks for that. It is now working for the one cell. Is there any quick way of filling the formula down 10 rows (from P7 to P17) without having to repeat the code for each individual cell.

Thanks again.

 

[Firefly2012]

To be clear, it isn't a formula - it is the result (value) of performing the VLOOKUP, but no formula is involved.

You can return all the results at once and place these into your sheet though:

Dim ANSWER As Variant  'always good to explicitly declare your variables (and to use Option Explicit at top of your module)


ANSWER = Application.VLookUp(Range("A7:A17").Value, WKBOOKSTAB1.Sheets(FILENAME6).Range("A:C"),3,FALSE)

Range("P7:P17").Value = ANSWER

 

[Swoootie]

Thank you so much! That works a treat and has saved me so much time.
Really appreciate all the help.