Find does NOT find

J

jdmils

New Member
Joined
Jun 3, 2006
Messages
10
I've been racking my brain over this for a whole day now and cannot figger out why my VBA FIND command is not coming up with results. Here's what I have:

It's an XL workbook used to compare Electricity prices for suppliers who have Peak and non-Peak charges.
The worksheet "Electricity- Peak-Off Peak" is the main sheet and the worksheet "AusNetDownload" is the raw electricity usage data over a number of months.
In worksheet "Electricity- Peak-Off Peak", cell $I$13, I have a UDM (User Defined Macro) which takes the start & end dates from S1:T1 ("24/11/2021" & "21/02/2022"), and the start and end times $G$13:$H$13 ("15:00 - 15:30" & "20:30 - 21:00") and finds the range of values between the dates and times specified, and will then sum the values (this part has not been started yet).

My problem is this line of code:
VBA Code: 
    Dim vRangeDateStart As Range
    Dim vRangeDateEnd As Range
   
    Debug.Print vDateColumnRange.Parent.Name
    Debug.Print vDateColumnRange.Address
    Debug.Print vDateColumnRange.Cells(586, 1).Value
    Debug.Print vDateColumnRange.Cells(1, 1).Value
    Debug.Print vDateColumnRange.Cells(2, 1).Value
   
    ' Find the Row numbers of the Start Date & End Date.
    With vDateColumnRange
        Set vRangeDateStart = vDateColumnRange.Find(vDateFrom.Value)
        Set vRangeDateEnd = vDateColumnRange.Find(vDateTo.Value, After:=vDateColumnRange.Cells(1), LookIn:=xlValues)
    End With

When the UDM fires, I see the following in the Immediate Window:
AusNetDownload
$A$1:$A$731
24/11/2021
DATE
19/04/2020
Click to expand...
So the code seems OK, it seems to have the correct references, however, the vRangeDateStart & vRangeDateEnd variables always come out blank???

Can anyone offer any help into why this is happening?


Link to workbook:

ExcelHelp - Google Drive

drive.google.com drive.google.com

For those who do not want to open the workbook:
Cell $I$13: =GetPeakOffPeakUsage(G13,H13)

The VBA code:
VBA Code: 
Function GetPeakOffPeakUsage(vTimeStart As String, vTimeEnd As String)
    ' Define worksheet variables.
    Dim vCurrentWorksheet As Worksheet
    Set vCurrentWorksheet = ActiveSheet
    Dim wUsageHistory As Worksheet
    Set wUsageHistory = Worksheets("AusNetDownload")
   
    Dim vDateFrom As Range, vDateTo As Range
    Set vDateFrom = Range("DateFrom")
    Set vDateTo = Range("DateTo")

    '
    ' Find the column numbers for the time range.
    '
    Dim vRangeTimeStart As Range    ' The range where the Time Start value is found.
    Dim vRangeTimeEnd As Range      ' The range where the Time End value is found.
   
    Dim vColumnTimeStart As Integer ' The column of the range where the Time Start value is found.
    Dim vColumnTimeEnd As Integer   ' The column of the range where the Time End value is found.
   
    ' Search the Time header for the start time & end time.
    With wUsageHistory.Range("B1:AW1")
        Set vRangeTimeStart = .Find(vTimeStart, After:=.Cells(1))
        Set vRangeTimeEnd = .Find(vTimeEnd, After:=.Cells(1))
    End With
   
    vColumnTimeStart = vRangeTimeStart.Column   ' Retrieve the column number of the Start Time.
    vColumnTimeEnd = vRangeTimeEnd.Column       ' Retrieve the column number of the End Time.
   
    '
    ' Find the row numbers for the date range.
    '
    Dim vRowDateStart As Range
    Dim vRowDateEnd As Range
   
    ' Find the last column of the date range. This can vary.
    Dim vLastDateRow As Integer
    vLastDateRow = wUsageHistory.Range("A1").End(xlDown).Row
    Set vDateColumnRange = wUsageHistory.Range("A1:A" & vLastDateRow)

    Dim vRangeDateStart As Range
    Dim vRangeDateEnd As Range
   
    Debug.Print vDateColumnRange.Parent.Name
    Debug.Print vDateColumnRange.Address
    Debug.Print vDateColumnRange.Cells(586, 1).Value
    Debug.Print vDateColumnRange.Cells(1, 1).Value
    Debug.Print vDateColumnRange.Cells(2, 1).Value
   
    ' Find the Row numbers of the Start Date & End Date.
    With vDateColumnRange
        Set vRangeDateStart = vDateColumnRange.Find(vDateFrom.Value)
        Set vRangeDateEnd = vDateColumnRange.Find(vDateTo.Value, After:=vDateColumnRange.Cells(1), LookIn:=xlValues)
    End With
   
    ' Debug.Print vRangeDateStart.Address
    ' Debug.Print vRangeDateEnd.Address
   
    vRowTimeStart = vRangeTimeStart.Row     ' Retrive the row number of the Start Date.
    vRowTimeEnd = vRangeTimeEnd.Row         ' Retrieve the row number of the End Date.
   
    ' The next set of lines will sum the Kilowatt values for the date range and time range.

   
End Function

Thanks for helping out.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Here is one way that might work for you:
VBA Code: 
    With vDateColumnRange
        Set vRangeDateStart = vDateColumnRange.Find(Format(vDateFrom.Value, "Short Date"), LookIn:=xlValues)
        Set vRangeDateEnd = vDateColumnRange.Find(Format(vDateTo.Value, "Short Date"), After:=vDateColumnRange.Cells(1), LookIn:=xlValues)
    End With
 
Upvote 0
I think this is more reliable since it is not date format dependant:
VBA Code: 
    Set vRangeDateStart = WorksheetFunction.Index(vDateColumnRange, WorksheetFunction.Match(CLng(vDateFrom.Value), vDateColumnRange, 0), 0)    
    Set vRangeDateEnd = WorksheetFunction.Index(vDateColumnRange, WorksheetFunction.Match(CLng(vDateTo.Value), vDateColumnRange, 0), 0)
 
Upvote 0
I had a bit more of a play with my second option and it needs a bit more code around it to make it robust.
If you do go with the 2nd option use this instead.

VBA Code: 
    With Application
        chkFound = .IfError(.Index(vDateColumnRange, .Match(CLng(vDateFrom.Value), vDateColumnRange, 0), 0), "")
        If chkFound = "" Then
            Set vRangeDateStart = Nothing
        Else
            Set vRangeDateStart = .Index(vDateColumnRange, .Match(CLng(vDateFrom.Value), vDateColumnRange, 0), 0)
        End If
            
        chkFound = .IfError(.Index(vDateColumnRange, .Match(CLng(vDateTo.Value), vDateColumnRange, 0), 0), "")
        If chkFound = "" Then
            Set vRangeDateEnd = Nothing
        Else
            Set vRangeDateEnd = .Index(vDateColumnRange, .Match(CLng(vDateTo.Value), vDateColumnRange, 0), 0)
        End If
    
    End With
 
Upvote 0
Hi,
had a brief look at your function & made a vain attempt to update it

VBA Code: 
Option Explicit
Function GetPeakOffPeakUsage(ByVal vTimeStart As String, ByVal vTimeEnd As String) As Variant
    ' Define worksheet variables.
    Dim vCurrentWorksheet           As Worksheet
    Dim vColumnTimeStart            As Variant          'column of the range where the Time Starts
    Dim vColumnTimeEnd              As Variant          'column of the range where the Time End value is found.
    Dim vRowDateStart               As Variant          'row number of the Start Date.
    Dim vRowDateEnd                 As Variant          'row number of the End Date.
    Dim vRangeDateStart             As Range            'Start date range.
    Dim vRangeDateEnd               As Range            'End date range.
    Dim vRangeTimeStart             As Range            'range where the Time Start value is found.
    Dim vRangeTimeEnd               As Range            'range where the Time End value is found.
    Dim vDateFrom                   As Range
    Dim vDateTo                     As Range
    Dim vDateColumnRange            As Range
    Dim wUsageHistory               As Worksheet
    Dim MySum                       As Variant
    
    With ThisWorkbook
        Set vCurrentWorksheet = .Worksheets("Electricity- Peak-Off Peak")
        Set wUsageHistory = .Worksheets("AusNetDownload")
    End With
    
    Application.Volatile
    
    With vCurrentWorksheet
        Set vDateFrom = .Range("DateFrom")
        Set vDateTo = .Range("DateTo")
    End With
    
    ' Search the Time header for the start time & end time.
    With wUsageHistory.Rows(1)
        Set vRangeTimeStart = .Find(vTimeStart, After:=.Cells(1, .Columns.Count), LookIn:=xlValues, lookat:=xlWhole)
        If vRangeTimeStart Is Nothing Then vColumnTimeStart = CVErr(10) Else vColumnTimeStart = vRangeTimeStart.Column
        Set vRangeTimeEnd = .Find(vTimeEnd, After:=.Cells(1, .Columns.Count), LookIn:=xlValues, lookat:=xlWhole)
        If vRangeTimeEnd Is Nothing Then vColumnTimeEnd = CVErr(10) Else vColumnTimeEnd = vRangeTimeEnd.Column
    End With
    
    If IsError(vColumnTimeStart) Or IsError(vColumnTimeEnd) Then
        'report search errors
        MySum = "Time Error"
        
    Else
        'search column
        Set vDateColumnRange = wUsageHistory.Columns(1)
        
        ' Find the Row numbers of the Start & End Dates.
        With vDateColumnRange
            Set vRangeDateStart = vDateColumnRange.Find(CStr(vDateFrom.Text), LookIn:=xlValues, lookat:=xlWhole)
            If vRangeDateStart Is Nothing Then vRowDateStart = CVErr(10) Else vRowDateStart = vRangeDateStart.Row
            Set vRangeDateEnd = vDateColumnRange.Find(CStr(vDateTo.Text), LookIn:=xlValues, lookat:=xlWhole)
            If vRangeDateEnd Is Nothing Then vRowDateEnd = CVErr(10) Else vRowDateEnd = vRangeDateEnd.Row
        End With
        
        If IsError(vRowDateStart) Or IsError(vRowDateEnd) Then
            'report search errors
            MySum = "Date Error"
            
        Else
            ' The next set of lines will sum the Kilowatt values for the date range and time range.
            
            With wUsageHistory
                MySum = Application.Sum(.Range(.Cells(CLng(vRowDateStart), vColumnTimeStart).Address & ":" & _
                        .Cells(CLng(vRowDateEnd), vColumnTimeEnd).Address))
            End With
            
        End If
        
    End If
    
    GetPeakOffPeakUsage = MySum
    
End Function

1650384142946.png

Updated code returned a result in your sample workbook but not entirely sure if its what you are looking for?

Dave
 
Upvote 0
Alex Blakenburg said:
Here is one way that might work for you:
VBA Code: 
    With vDateColumnRange
        Set vRangeDateStart = vDateColumnRange.Find(Format(vDateFrom.Value, "Short Date"), LookIn:=xlValues)
        Set vRangeDateEnd = vDateColumnRange.Find(Format(vDateTo.Value, "Short Date"), After:=vDateColumnRange.Cells(1), LookIn:=xlValues)
    End With
Click to expand...
This worked perfectly- thank you!
 
Upvote 0
dmt32 said:
Hi,
had a brief look at your function & made a vain attempt to update it

VBA Code: 
Option Explicit
Function GetPeakOffPeakUsage(ByVal vTimeStart As String, ByVal vTimeEnd As String) As Variant
    ' Define worksheet variables.
    Dim vCurrentWorksheet           As Worksheet
    Dim vColumnTimeStart            As Variant          'column of the range where the Time Starts
    Dim vColumnTimeEnd              As Variant          'column of the range where the Time End value is found.
    Dim vRowDateStart               As Variant          'row number of the Start Date.
    Dim vRowDateEnd                 As Variant          'row number of the End Date.
    Dim vRangeDateStart             As Range            'Start date range.
    Dim vRangeDateEnd               As Range            'End date range.
    Dim vRangeTimeStart             As Range            'range where the Time Start value is found.
    Dim vRangeTimeEnd               As Range            'range where the Time End value is found.
    Dim vDateFrom                   As Range
    Dim vDateTo                     As Range
    Dim vDateColumnRange            As Range
    Dim wUsageHistory               As Worksheet
    Dim MySum                       As Variant
   
    With ThisWorkbook
        Set vCurrentWorksheet = .Worksheets("Electricity- Peak-Off Peak")
        Set wUsageHistory = .Worksheets("AusNetDownload")
    End With
   
    Application.Volatile
   
    With vCurrentWorksheet
        Set vDateFrom = .Range("DateFrom")
        Set vDateTo = .Range("DateTo")
    End With
   
    ' Search the Time header for the start time & end time.
    With wUsageHistory.Rows(1)
        Set vRangeTimeStart = .Find(vTimeStart, After:=.Cells(1, .Columns.Count), LookIn:=xlValues, lookat:=xlWhole)
        If vRangeTimeStart Is Nothing Then vColumnTimeStart = CVErr(10) Else vColumnTimeStart = vRangeTimeStart.Column
        Set vRangeTimeEnd = .Find(vTimeEnd, After:=.Cells(1, .Columns.Count), LookIn:=xlValues, lookat:=xlWhole)
        If vRangeTimeEnd Is Nothing Then vColumnTimeEnd = CVErr(10) Else vColumnTimeEnd = vRangeTimeEnd.Column
    End With
   
    If IsError(vColumnTimeStart) Or IsError(vColumnTimeEnd) Then
        'report search errors
        MySum = "Time Error"
       
    Else
        'search column
        Set vDateColumnRange = wUsageHistory.Columns(1)
       
        ' Find the Row numbers of the Start & End Dates.
        With vDateColumnRange
            Set vRangeDateStart = vDateColumnRange.Find(CStr(vDateFrom.Text), LookIn:=xlValues, lookat:=xlWhole)
            If vRangeDateStart Is Nothing Then vRowDateStart = CVErr(10) Else vRowDateStart = vRangeDateStart.Row
            Set vRangeDateEnd = vDateColumnRange.Find(CStr(vDateTo.Text), LookIn:=xlValues, lookat:=xlWhole)
            If vRangeDateEnd Is Nothing Then vRowDateEnd = CVErr(10) Else vRowDateEnd = vRangeDateEnd.Row
        End With
       
        If IsError(vRowDateStart) Or IsError(vRowDateEnd) Then
            'report search errors
            MySum = "Date Error"
           
        Else
            ' The next set of lines will sum the Kilowatt values for the date range and time range.
           
            With wUsageHistory
                MySum = Application.Sum(.Range(.Cells(CLng(vRowDateStart), vColumnTimeStart).Address & ":" & _
                        .Cells(CLng(vRowDateEnd), vColumnTimeEnd).Address))
            End With
           
        End If
       
    End If
   
    GetPeakOffPeakUsage = MySum
   
End Function

View attachment 62770
Updated code returned a result in your sample workbook but not entirely sure if its what you are looking for?

Dave
Click to expand...
That is perfect- the returned values are exactly what i was looking for, thank you again! Looks like my problem was that I was searching for a string in a column of dates which was not working.
 
Upvote 0
Glad we were able to resolve your issue & appreciate the feedback

Dave
 
Upvote 0
jdmils said:
Looks like my problem was that I was searching for a string in a column of dates which was not working.
Click to expand...
Its probably more the other way around. Find is really unforgiving when it comes to date formatting. If you change either the Find Date format or the Column A date format so that they are different you are likely to discover that the Find no longer works.

eg change the date format of either the find value (say DateFrom) or the Column A value to d/mm/yy or d-mmm-yyyy and Set vRangeDateStart = vDateColumnRange.Find(CStr(vDateFrom.Text), LookIn:=xlValues, lookat:=xlWhole) won't find it.

Do the same and change only column A (leaving DateFrom unchanged) and Set vRangeDateStart = vDateColumnRange.Find(Format(vDateFrom.Value, "Short Date"), LookIn:=xlValues) will no longer find it.

The index match method will be unaffected and still work.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Setting up Two Pie charts in VBA
Replies
4
Views
171
TheTiredEngineer
T
J
  • Question Question
Find in VBA - unexpected behaviour
Replies
7
Views
110
MARK858
MARK858
C
  • Question Question
Using pattern searches with a variable to find the correct values
2
Replies
10
Views
194
chazrab
C
L
  • Solved
VBA Find Cell from Date listed, in Columns, Return cell to highlight in color
Replies
7
Views
496
mumps
mumps
BAKELOVEMORE
  • Question Question
Find value in sheet1, copy offset value, find offset value in sheet 2, paste text in offset cell
Replies
1
Views
288
offthelip
O

Forum statistics

Threads
1,223,881
Messages
6,175,161
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top