CODEWITHSUNDEEP
vbaexcel
Sean
Sean

Reputation: 3

referencing worksheet with vlookup

New to this, only been doing it around 24 hours. 12 of those hours have been researching this problem. I have found so many pages with examples that seem like they SHOULD work, but haven't. I must be missing sth blatantly obvious.

My code:

  1. opens a msgbox, with which the user chooses (types in) an existing worksheet. Currently there is only one worksheet, C1.
  2. the macro then uses a vlookup to find a the value in a cell, which is stored in a variable for later use. The cell I'm trying to find contains 2016.1. It is located in Cell C25 of sheet C1.

The problem is the vlookup. I get "runtime error 1004: Unable to get the VLookup property of the WorksheetFunction class".

I know my variables Prodcode contains the correct sheet name (C1), and ForecastYear contains the correct year (2016.1). I think my issue is that I'm not referencing the worksheet name somehow, but I've tried to follow examples from so many websites, and none of them work. 

Function WorksheetExists(WSName As String) As Boolean
    On Error Resume Next
    WorksheetExists = Worksheets(WSName).Name = WSName
    On Error GoTo 0
End Function

Private Sub UserForm_Initialize()

Dim ProdCode As String
Do Until WorksheetExists(ProdCode)
    ProdCode = InputBox("Enter Product Code: ", "Enter Product Code:", "i.e C1")
    If Not WorksheetExists(ProdCode) Then MsgBox ProdCode & _
        " doesn't exist!", vbExclamation
Loop
Sheets(ProdCode).Select

Me.Title.Caption = "Forecast data for " & ProdCode
Me.Label2012.Caption = Format(Now(), "yyyy")
Me.Label1sta.Caption = "1st Qtr"
Me.Label2nda.Caption = "2nd Qtr"
Me.Label3rda.Caption = "3rd Qtr"
Me.Label4tha.Caption = "4th Qtr"

Me.LabelFc1.Caption = "Forecast"
Me.Labelwfc1.Caption = "Weighted Forecast"
Me.LabelwD1.Caption = "Weighted Demand"

'-----------------------------------------------------------------------------

'1st quarter current year predictions
Dim ForecastYear As Double
ForecastYear = Year(Now) + .1 'the .1 is to break the year into quarters

MsgBox (ForecastYear)       'for debugging only. checks the correct year is selected
MsgBox (ProdCode)           'for debugging only. checks the correct worksheet is selected

Dim Forecast As Double
Forecast = Application.WorksheetFunction.VLookup(ForecastYear, _
    Sheets(ProdCode).Range("A9:J5000"), 10, False)
Forecast = Round(Forecast, 2)

'-----------------------------------------------------------------------------
With ListBox1
    .AddItem ForecastYear
    .AddItem Forecast
    .AddItem ""

End With

End Sub

Sorry, I know this has likely been asked before. I may have even stared at the answer on another page and not realised it was the answer.

Upvotes: 0

Views: 68

Answers (1)

user3598756
user3598756

Reputation: 29421

I guess you have to change:

Dim Forecast As Double
Forecast = Application.WorksheetFunction.VLookup(ForecastYear, Sheets("ProdCode").Range("A9:J5000"), 10, False)
Forecast = Round(Forecast, 2)

'-----------------------------------------------------------------------------
With ListBox1
    .AddItem ForecastYear
    .AddItem Forecast
    .AddItem ""

End With

to:

Dim Forecast As Variant
Forecast = Application.VLookup(ForecastYear, Sheets(ProdCode).Range("A9:J5000"), 10, False)
If IsError(Forecast) Then
    MsgBox "couldn't find '" & ForecastYear & "' in Sheets '" & ProdCode & "'"
    Exit Sub    
End If

Forecast = Round(Forecast, 2)     
'-----------------------------------------------------------------------------
With ListBox1
    .AddItem ForecastYear
    .AddItem Forecast
    .AddItem ""    
End With

Furthermore I'd refactor the initial ProdCode loop to:

ProdCode = Application.InputBox("Enter Product Code: ", "Enter Product Code:", "i.e C1", , , , , 2)
Do While Not WorksheetExists(ProdCode)
    MsgBox ProdCode & " doesn't exist!", vbExclamation
    ProdCode = Application.InputBox("Enter Product Code: ", "Enter Product Code:", "i.e C1", , , , , 2)
Loop

Upvotes: 1

Related Questions