Highline Excel Class 35: IF Function Vs. VLOOKUP Function

I get the above error on the following piece of code. I have printed the value of Product_Short_name and it is meaningful and it exists in the lookup table. Any suggestions ? Thanks

Kaps

[code]

Description = Application.WorksheetFunction.VLookup(Product_Short_Name, Inventory, 3, False)

[code/]

I'm stuck with this message and really can't figure out how to rectify the issue.

I'm using the code function in a workbook_open sub and within a with statement.

I'm trying to run the vloopup on a range in another workbook.
In that workbook, the contents of the cell the variable is taken from is not an error.

My code is as follows:

Code:

With Workbooks(a - 1).Worksheets("sheet1")
.Activate
SUPCODE = Cells(15, 3).Value
SUPNAME = Application.WorksheetFunction.VLookup(SUPCODE, Range("G1:G10000"), 2, False)
End With

Can anyone please help this seemingly simple problem?

Hello,

I am trying to do a vlookup on current worksheet compared to a range in another file.

I get a "Run Time Error 1004: Unable to get the vlookup property of the worksheet function class".

After some investigation, I found out that if there are some values that dont match up this could be a problem. In my case I will run into that situiation. But for testing purposes, I tried manipulating the data so that this doesnt happen. With no luck.

Here is my code:

Code:

 
Dim lastrow as Long

lastrow = Sheets("Master List").Range("A1").End(xlDown).Row

For i = 2 To lastrow
Sheets("Master List").Cells(i, "B").Value = Application.WorksheetFunction.VLookup(Sheets("Master List").Cells(i, "A"), "'T:\Operations+Technology\NOC\Abuse Emails\[Customer Data for Abuse Emails.xls]Report'!$E$4:$G$60000", 2, False)
Next i

Thanks in Advance

I'm trying to use a class for defining a set of data and also to get values from two different ways, a database and a spreadsheet. Let me explain with an example.

I created a class called clsData with the following code:

Public conn As ADODB.Connection
Private p_isin As String
Private p_tick As String

Public Property Let isin(myvalue As String)
p_isin = myvalue
End Property
Public Property Get isin() As String
isin = p_isin
End Property
Public Property Let tick(myvalue As String)
p_tick = myvalue
End Property
Public Property Get tick() As String
tick = p_tick
End Property


I inserted a public function inside that class, in order to retrieve the data in the two mentioned ways:

Public Function GetData(myvalue As String, DB As Boolean) As clsData
Dim rs As ADODB.Recordset
Dim sql As String
Dim c As Range
Dim myData As clsData
Set myData = New clsData

If DB Then
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
sql = "SELECT * FROM dbData WHERE isin='" & myvalue & "'"
rs.Open sql, conn

If Not rs.EOF Then
myData.isin = rs("isin")
myData.tick = rs("tick")
End If
rs.Close
Else
With ActiveSheet
Set c = .Range("A:A").Find(myvalue, LookAt:=xlWhole)
If Not c Is Nothing Then
myData.isin = .Cells(c.Row, 1)
myData.tick = .Cells(c.Row, 2)
End If
End With
End If
GetData = myData
End Function

All that code goes into clsData. Now I add a standard mode with this code:

Public Function GetClassData() As clsData
Set GetClassData = New clsData
End Function

Sub myTest()
Dim AllData As clsData
Set AllData = New clsData
AllData = GetClassData.GetData("FI0009000681", False)
End Sub

The first function is in order to access the class and the function inside
The routine myTest is trying to retrieve the data I need

I get an error in last line of the function: "GetData = myData". Error message window says "Object variable or block With not established" (actually it is a translation of my current Excel version)

Anybody knows what is wrong with this code?
I can send the whole code to anybody who can help

JC

Hello,

How do you pass an object to a class or function.

I've tried this:

Class

Code:

Private objListBox As listbox
Private intY As Long

Public Function items()
    For intY = 0 To objListBox.ListCount - 1
        If objListBox.Select(intY) = True Then
            MsgBox objListBox.List(intY)
        End If
    Next intY
End Function

Public Property Set SetObj(ByVal NewObj As listbox)
    Set objListBox = NewObj
End Property

Module

Code:

Option Explicit

Private Sub cmdRow_Click()

    Dim GetList As Class1
    
    GetList.SetObj = lstpvtdata ' This is a listbox in a from
    
    GetList.items
    
End Sub

But get an invalid use of property error.

Any suggestions to work around the sum error?

Quote:

Runtime Error '1004'
unable to locate the sum property of the worksheetfunction class

Code:

Public Function CalcSheet()
Dim NoSales(0 To 11) As Range
Dim DriveOffs(0 To 11) As Range
Dim Voids(0 To 11) As Range
Dim Shortages(0 To 11) As Range
Dim tNoSales As Integer
Dim tDriveOffs As Currency
Dim tVoids As Currency
Dim tShortages As Currency
Dim X As Integer
Dim NSc As Integer
Dim DOc As Integer
Dim VOc As Integer
Dim SHc As Integer
Dim aNoSales As Integer
Dim aDriveOffs As Currency
Dim aVoids As Currency
Dim aShortages As Currency

    'Initialize Total and Average Variables
    aNoSales = 0
    aDriveOffs = 0
    aVoids = 0
    aShortages = 0
    tNoSales = 0
    tDriveOffs = 0
    tVoids = 0
    tShortages = 0

    'Populate Object Arrays
    Set NoSales(0) = Worksheets(1).Range("B3:B33")
    Set NoSales(1) = Worksheets(1).Range("F3:F33")
    Set NoSales(2) = Worksheets(1).Range("J3:J33")
    Set NoSales(3) = Worksheets(1).Range("N3:N33")
    Set NoSales(4) = Worksheets(1).Range("R3:R33")
    Set NoSales(5) = Worksheets(1).Range("V3: V33")
    Set NoSales(6) = Worksheets(1).Range("Z3:Z33")
    Set NoSales(7) = Worksheets(1).Range("AD3:AD33")
    Set NoSales(8) = Worksheets(1).Range("AH3:AH33")
    Set NoSales(9) = Worksheets(1).Range("AL3:AL33")
    Set NoSales(10) = Worksheets(1).Range("AP3:AP33")
    Set NoSales(11) = Worksheets(1).Range("AT3:AT33")
    
    Set DriveOffs(0) = Worksheets(1).Range("C3:C33")
    Set DriveOffs(1) = Worksheets(1).Range("G3:G33")
    Set DriveOffs(2) = Worksheets(1).Range("K3:K33")
    Set DriveOffs(3) = Worksheets(1).Range("O3:O33")
    Set DriveOffs(4) = Worksheets(1).Range("S3:S33")
    Set DriveOffs(5) = Worksheets(1).Range("W3:W33")
    Set DriveOffs(6) = Worksheets(1).Range("AA3:AA33")
    Set DriveOffs(7) = Worksheets(1).Range("AE3:AE33")
    Set DriveOffs(8) = Worksheets(1).Range("AI3:AI33")
    Set DriveOffs(9) = Worksheets(1).Range("AM3:AM33")
    Set DriveOffs(10) = Worksheets(1).Range("AQ3:AQ33")
    Set DriveOffs(11) = Worksheets(1).Range("AU3:AU33")
    
    Set Voids(0) = Worksheets(1).Range("D3:D33")
    Set Voids(1) = Worksheets(1).Range("H3:H33")
    Set Voids(2) = Worksheets(1).Range("L3:L33")
    Set Voids(3) = Worksheets(1).Range("P3:P33")
    Set Voids(4) = Worksheets(1).Range("T3:T33")
    Set Voids(5) = Worksheets(1).Range("X3:X33")
    Set Voids(6) = Worksheets(1).Range("AB3:AB33")
    Set Voids(7) = Worksheets(1).Range("AF3:AF33")
    Set Voids(8) = Worksheets(1).Range("AJ3:AJ33")
    Set Voids(9) = Worksheets(1).Range("AN3:AN33")
    Set Voids(10) = Worksheets(1).Range("AR3:AR33")
    Set Voids(11) = Worksheets(1).Range("AV3:AV33")
        
    Set Shortages(0) = Worksheets(1).Range("E3:E33")
    Set Shortages(1) = Worksheets(1).Range("I3:I33")
    Set Shortages(2) = Worksheets(1).Range("M3:M33")
    Set Shortages(3) = Worksheets(1).Range("Q3:Q33")
    Set Shortages(4) = Worksheets(1).Range("U3:U33")
    Set Shortages(5) = Worksheets(1).Range("Y3:Y33")
    Set Shortages(6) = Worksheets(1).Range("AC3:AC33")
    Set Shortages(7) = Worksheets(1).Range("AG3:AG33")
    Set Shortages(8) = Worksheets(1).Range("AK3:AK33")
    Set Shortages(9) = Worksheets(1).Range("AO3:AO33")
    Set Shortages(10) = Worksheets(1).Range("AS3:AS33")
    Set Shortages(11) = Worksheets(1).Range("AW3:AW33")
    
    For X = 0 To 11 Step 1

        If Application.WorksheetFunction.Sum(NoSales(X)) > 0 Then 'Check for no data
            NSc = NSc + Application.WorksheetFunction.Count(NoSales(X)) 'Count number of data entries
            tNoSales = tNoSales + Application.WorksheetFunction.Sum(NoSales(X)) 'Sum data
        End If
    
    Next X

    For X = 0 To 11 Step 1
        
        If Application.WorksheetFunction.Sum(DriveOffs(X)) > 0 Then 'Check for no data
            DOc = DOc + Application.WorksheetFunction.Count(DriveOffs(X)) 'Count number of data entries
            tDriveOffs = tDriveOffs + Application.WorksheetFunction.Sum(DriveOffs(X)) 'Sum data
        End If
    
    Next X

    For X = 0 To 11 Step 1

        If Application.WorksheetFunction.Sum(Voids(X)) > 0 Then 'Check for no data
            VOc = VOc + Application.WorksheetFunction.Count(Voids(X)) 'Count number of data entries
            tVoids = tVoids + Application.WorksheetFunction.Sum(Voids(X)) 'Sum data
        End If
    
    Next X

    For X = 0 To 11 Step 1

        If Application.WorksheetFunction.Sum(Shortages(X)) > 0 Then 'Check for no data
            SHc = SHc + Application.WorksheetFunction.Count(Shortages(X)) 'Count number of data entries
            tShortages = tShortages + Application.WorksheetFunction.Sum(Shortages(X)) 'Sum data
        End If
    
    Next X

    'Error Check before Dividing for Average
    If (tNoSales > 0) And (NSc > 0) Then aNoSales = tNoSales / NSc
    If (tDriveOffs > 0) And (DOc > 0) Then aDriveOffs = tDriveOffs / DOc
    If (tVoids > 0) And (VOc > 0) Then aVoids = tVoids / VOc
    If (tShortages > 0) And (SHc > 0) Then aShortages = tShortages / SHc

    'Insert Averages
    Worksheets(1).Range("B37").Value = aNoSales
    Worksheets(1).Range("C37").Value = aDriveOffs
    Worksheets(1).Range("D37").Value = aVoids
    Worksheets(1).Range("E37").Value = aShortages
    
    'Insert Totals
    Worksheets(1).Range("B38").Value = tNoSales
    Worksheets(1).Range("C38").Value = tDriveOffs
    Worksheets(1).Range("D38").Value = tVoids
    Worksheets(1).Range("E38").Value = tShortages
    
    'Release Objects
    Set NoSales(0) = Nothing
    Set NoSales(1) = Nothing
    Set NoSales(2) = Nothing
    Set NoSales(3) = Nothing
    Set NoSales(4) = Nothing
    Set NoSales(5) = Nothing
    Set NoSales(6) = Nothing
    Set NoSales(7) = Nothing
    Set NoSales(8) = Nothing
    Set NoSales(9) = Nothing
    Set NoSales(10) = Nothing
    Set NoSales(11) = Nothing
    
    Set DriveOffs(0) = Nothing
    Set DriveOffs(1) = Nothing
    Set DriveOffs(2) = Nothing
    Set DriveOffs(3) = Nothing
    Set DriveOffs(4) = Nothing
    Set DriveOffs(5) = Nothing
    Set DriveOffs(6) = Nothing
    Set DriveOffs(7) = Nothing
    Set DriveOffs(8) = Nothing
    Set DriveOffs(9) = Nothing
    Set DriveOffs(10) = Nothing
    Set DriveOffs(11) = Nothing
    
    Set Voids(0) = Nothing
    Set Voids(1) = Nothing
    Set Voids(2) = Nothing
    Set Voids(3) = Nothing
    Set Voids(4) = Nothing
    Set Voids(5) = Nothing
    Set Voids(6) = Nothing
    Set Voids(7) = Nothing
    Set Voids(8) = Nothing
    Set Voids(9) = Nothing
    Set Voids(10) = Nothing
    Set Voids(11) = Nothing
        
    Set Shortages(0) = Nothing
    Set Shortages(1) = Nothing
    Set Shortages(2) = Nothing
    Set Shortages(3) = Nothing
    Set Shortages(4) = Nothing
    Set Shortages(5) = Nothing
    Set Shortages(6) = Nothing
    Set Shortages(7) = Nothing
    Set Shortages(8) = Nothing
    Set Shortages(9) = Nothing
    Set Shortages(10) = Nothing
    Set Shortages(11) = Nothing

End Function

Hi all,

I've got a small class module containing 5 properties, all with Get and Let methods. I can create an instance of the class and assign values to the properties, like this:

Dim currentSite As New Site
currentSite.DateRange = "B9:AF9"

The 'currentSite' variable then needs to be passed to a function so that the function can access the instance properties and act on properties. My function declaration looks like this:

Sub AnalyseRange2(siteData As Site)

When the function is called, I just get this:

Run-time error '438':
Object doesn't support this property or method.

Seems self-explanatory enough but all I'm doing is passing an instance of the class, nothing else. I've tried passing the variable ByRef and ByVal - neither of those make any difference.

Can anyone help solve this?

Thanks!

Hello,

is it possible to add a description/summary to functions that you write?

I want to make a re-usable Class and want the intellisense to show a brief description of the functions that i wrap up in the class.

I can add comments within the class but that wouln't really help whilst using the class.

Thanks

How is code declared in add-in (.xla) class module called from spreadsheet Workbook_SheetChange event?

I want to remove as much code as possible from the spreadsheet and keep it in the add-in.

I've created an UDF in the add-in Modules but Compile error "sub or function not defined"

Below is an example of what I would like to write. How do I get it to work?

=if(vlookup(+a1,data range,2,false)= empty cell,0,vlookup(+a1,data range,2,false)) In other words if the cell addressed in the vlookup function is empty return a zero. ALso how do i denote an empty cell in an IF function? I know that " " will put a leading ick mark in the cell but will not leave it empty.

Thanks
Mike