none
Using Hlookup to find a date in a spreadsheet RRS feed

  • Question

  • I'm creating a macro that will allow a user to enter a date into cell A1 of Sheet1 of Perfomancecheck.xlsm.  When it is changed, a macro should run that uses an Hlookup to find what column the date is in in a different spreadsheet (World.xlsx (worksheet "Daily Detail")).  I then want to put the data from the Hlookup in Cell B2 of Sheet1 of Perfomancecheck.xlsm.

    Code is as follows:

    In Sheet1 of Performancecheck.xlsm:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim KeyCell As Range
        Dim newDate As String
        Dim instance As WorksheetFunction
        
    
      
        Set KeyCell = Range("A1:A1")
        Application.EnableEvents = True
        If Not Application.Intersect(KeyCell, Range(Target.Address)) Is Nothing Then
    
        Application.Run "GetVal"
        End If
        
    End Sub

    GetVal is in a Module in Performancecheck.xlsm.  I want to search Row 3 for the number of columns used and then do an HlookupCode as follows:

    Private Sub GetVal()
        Dim ColCount As Integer
        Dim newDate As String, ReturnVal As String
        
        newDate = Cells(1, "A").Value                    
        
        Workbooks.Open Filename:="C:\test\World.xlsx"
        Workbooks("World.xlsx").Activate
        ColCount = Cells(3, Columns.Count).End(xlToLeft).Column
        ReturnVal = WorksheetFunction.Hlookup(newDate, Range(Cells(3, 2), Cells(67, ColCount)), 64, False)
        Debug.Print ReturnVal
       
    End Sub

    I keep getting a run-time error '1004': Unable to get the Hlookup property of the WorksheetFunction class.

    I'm not sure how to pass the variable newDate from one Sub to another, so that's why I manually defined it in GetVal. My main concern at the moment is that I can't get the variable ReturnVal to be anything other than a null string.



    • Edited by bcvd Tuesday, September 16, 2014 5:38 PM
    Tuesday, September 16, 2014 5:36 PM

Answers

  • You have a local variable - KeyCell As String - which will take precedence over the public variable KeyCell in your  GetVal procedure.

    So: Put this into a standard codemodule:

    Option Explicit
    Public KeyCell As Range

    Sub GetVal()
        Dim ColCount As Integer
        Dim ReturnVal As Variant  'In case the HLookup returns an error

        Workbooks.Open Filename:="S:\test\World.xlsx"
        Workbooks("World.xlsx").Activate
        ColCount = Cells(3, Columns.Count).End(xlToLeft).Column

        ReturnVal = Application.HLookup(KeyCell, Range(Cells(3, 2), Cells(67, ColCount)), 64, False)
        MsgBox ReturnVal

    End Sub

    And put this into the codemodule of the sheet of with your keycell:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Set KeyCell = Range("A1")
        If Target.Address <> "$A$1" Then Exit Sub
        Application.Run "GetVal"
    End Sub

    You cannot have your macro code stored in the sheet module because the cell references will default to that sheet, because you don't fully qualify all your range references.

    PS  For event code:

    Range(Target.Address)

    is the same as just

    Target



    Wednesday, September 17, 2014 1:13 PM

All replies

  • Make KeyCell a public variable,  then use

    ReturnVal = Application.HLookup(KeyCell, Range(Cells(3, 2), Cells(67, ColCount)), 64, False)

    Tuesday, September 16, 2014 5:52 PM
  • Thanks for the quick reply, Bernie. 

    I changed KeyCell to: Public KeyCell as Range 

    However, now I get a compile error due to using an "invalid attribute in sub or function".  Is it not possible to make a variable public in a private sub procedure?

    Tuesday, September 16, 2014 6:25 PM
  • Put the declaration at the top of your module, before any sub or function definitions, but after the Option Explicit statement that I hope you are using ;)
    Tuesday, September 16, 2014 6:32 PM
  • Got it.  I am now!

    Still finding myself at the same spot, however. Updated code :

    Option Explicit
    Public KeyCell As Range
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim instance As WorksheetFunction
    
    
        
        Set KeyCell = Range("A1:A1")
        Application.EnableEvents = True
        If Not Application.Intersect(KeyCell, Range(Target.Address)) Is Nothing Then
    
        Application.Run "GetVal"
    
        End If
        
    End Sub
    
    
    Private Sub GetVal()
        Dim ColCount As Integer
        Dim ReturnVal As String, KeyCell As String
        
        
        Workbooks.Open Filename:="S:\test\World.xlsx"
        Workbooks("World.xlsx").Activate
        ColCount = Cells(3, Columns.Count).End(xlToLeft).Column
        ReturnVal = Application.Hlookup(KeyCell, Range(Cells(3, 2), Cells(67, ColCount)), 64, False)
        Debug.Print ReturnVal
       
    End Sub

    If I leave KeyCell as a Range, I get a run-time error '13' :type mismatch.  If I Dim KeyCell as a string in GetVal(), I still get the same result.  The Former results in KeyCell value being empty and the latter results in "".

    Tuesday, September 16, 2014 7:04 PM
  • You have a local variable - KeyCell As String - which will take precedence over the public variable KeyCell in your  GetVal procedure.

    So: Put this into a standard codemodule:

    Option Explicit
    Public KeyCell As Range

    Sub GetVal()
        Dim ColCount As Integer
        Dim ReturnVal As Variant  'In case the HLookup returns an error

        Workbooks.Open Filename:="S:\test\World.xlsx"
        Workbooks("World.xlsx").Activate
        ColCount = Cells(3, Columns.Count).End(xlToLeft).Column

        ReturnVal = Application.HLookup(KeyCell, Range(Cells(3, 2), Cells(67, ColCount)), 64, False)
        MsgBox ReturnVal

    End Sub

    And put this into the codemodule of the sheet of with your keycell:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Set KeyCell = Range("A1")
        If Target.Address <> "$A$1" Then Exit Sub
        Application.Run "GetVal"
    End Sub

    You cannot have your macro code stored in the sheet module because the cell references will default to that sheet, because you don't fully qualify all your range references.

    PS  For event code:

    Range(Target.Address)

    is the same as just

    Target



    Wednesday, September 17, 2014 1:13 PM