Using VLOOKUp to find a Date in a Data Worksheet

[Denny57]

I have been getting Out of Memory messages when running a couple of Excel workbooks using the 32 bit version of Office 365. I decided to replace this with the 64 bit version in the full knowledge that some controls are not supported in this version. In the 32 bit version I happily used the DTPicker control, which is not available in 64 bit.

I have also decided that I would like to save memory by replacing pre-added forumulas to hundreds currently stored cells in the data sheet with VBA code to apply a value at the time new records are added.

I did try to write code that would run the VLookup task as new records are added to the data sheet, without success. I have used the sample code in previous files to lookup and populate textboxes in user Forms, but not where the value to be used is a date.

I need to use VLOOKUP in VBA if I am to replace the current formula in a range of Cells
Current Cell Formula
=IFNA(VLOOKUP(A64,'Pay Month Dates & Holiday Pay'!$A$1:$B$358,2,FALSE),"")

I have tried using the following VBA Module to lookup the value in txtWorkLeaveDate, and populate txtPayMonthInput with the corresponding VLookup value in Sheet2.

Private Sub txtWorkLeaveDate_AfterUpdate()
With Me
.txtPayMonthInput = Application.WorksheetFunction.VLookup(Me.txtWorkLeaveDate, Sheet2.Range("A4:B358"), 2, False)
End With
End Sub

I get a Run Time 1004 Error which I have identifed as occurring because the value I type into txtWorkLeaveDate is not in date format as it is in column A in Sheet2.

Is there some additional line I can add to this module that will convert the input detail to dd/mm/yyyy format so that the VLookup code will be able to find a match.

 

[Logit]

This will convert your text entries in Col A to Dates in Col B.

Sub ConvertStringToDate()

    Dim i As Integer

    For i = 1 To 358
        Range("B" & i) = Format(CDate(Range("A" & i)), "DD/MM/YYYY")
       
    Next i
   
End Sub

 

[Denny57]

This will convert your text entries in Col A to Dates in Col B.

Sub ConvertStringToDate()

    Dim i As Integer

    For i = 1 To 358
        Range("B" & i) = Format(CDate(Range("A" & i)), "DD/MM/YYYY")
     
    Next i
 
End Sub

Hi Logit

Perhaps I should have made it clearer that Sheet 2 Contains information which will be used later and should not be changed.

The scenario is that column A contains every date in a pay year and Column B indicates the Pay Month to which the work / leave applies (This might change later so a single data sheet is the best method if multiple enties need to be updated. This sheet contains fixed information, based on current company pay date arrangements.

My User Form allows each user to input their daily work / leave hours and search hours and Gross pay by day and also by pay month. The VLookup is being used to identify the Pay Month.

Therefore I was looking to match a date to a Pay Month.

In previous files, as soon as I change a value in textboxA then the VLookup automatically updated the value displayed in textboxB. My problem this time is that the value to be used and matched are dates.

 

[Alex Blakenburg]

See if this works for you.

Private Sub txtWorkLeaveDate_AfterUpdate()
    With Application
        Me.txtPayMonthInput = .IfError(.VLookup(CLng(CDate(Me.txtWorkLeaveDate)), Sheet2.Range("A4:B358"), 2, False), "")
    End With
End Sub

Additional error handling may be required depending on what you want to happen if it can't find the date, this will default to "" if it can't find it.

 

[Denny57]

See if this works for you.

Private Sub txtWorkLeaveDate_AfterUpdate()
    With Application
        Me.txtPayMonthInput = .IfError(.VLookup(CLng(CDate(Me.txtWorkLeaveDate)), Sheet2.Range("A4:B358"), 2, False), "")
    End With
End Sub

Additional error handling may be required depending on what you want to happen if it can't find the date, this will default to "" if it can't find it.

Alex Apologies for the delay in responding.
Your solution works perfectly
Many thanks

 

[Alex Blakenburg]

You're welcome. Thanks for letting us know.

 

[Denny57]

Alex Apologies for the delay in responding.
Your solution works perfectly
Many thanks

Alex Apologies but i now have an additional problem with the date. I set the txtWorkLeaveDate to todays date when the form is opened using this line in the following module.

Private Sub cmdClearForm_Click()
'Clears the User Form
    txtWorkLeaveDate.Value = Format(Date, "mm/dd/yyyy")
    txtPayMonthInput.Value = ""
    cboSchedulingType.Value = ""
    cboLocation.Value = ""
    txtStartTime.Value = "00:00"
    txtStartTime.MaxLength = 5
    txtFinishTime.Value = "00:00"
    txtFinishTime.MaxLength = 5
    cboPayRate.Value = ""
    CheckBoxPete.Value = False
    CheckBoxKirsty.Value = False
    CheckBoxJan.Value = False
    CheckBoxKelly.Value = False
    CheckBoxCarla.Value = False
    txtWorkDate.Value = ""
    txtDailyPayMonth.Value = ""
    txtDailyWorkHours.Value = ""
    txtDailyLeaveHours.Value = ""
    txtDailyNonWorkingDay.Value = ""
    txtDailyEarningsGross.Value = ""
    txtMonthlyWorkHours.Value = ""
    txtDailyEarningsGross.Value = ""
    txtMonthlyPayMonth.Value = ""
    txtMonthlyWorkHours.Value = ""
    txtMonthlyWorkEarningsGross.Value = ""
    txtMonthlyLeaveHours.Value = ""
    txtMonthlyLeaveEarningsGross.Value = ""
    txtTotalMonthlyEarningsGross.Value = ""
    txtDateSearch.Value = ""
    cboPayMonthSearch.Value = ""
  
    txtWorkLeaveDate.SetFocus
  
    With Me.cmdInputRecords
        .Tag = xlAdd
        .Caption = "Add Record"
        .BackColor = rgbBlue
    End With
End Sub

This shows the date (today for example ) as 01/12/2024 but and call the correct data using VLookup. However, when the value is passed to the cell in sheet 2 it displays as if the date is in US format (12th January 2024). I have check my MS 365 account and Excel settings and there are set to UK format.

Any ideas how I can get the value to remain UK format. Incidentally the value in the formula bar shows 12/01/2024 (US) but only when the follwoing "Input" Code is used

rivate Sub cmdInputRecords_Click()
    Dim answer      As VbMsgBoxResult
    Dim AddRecord   As Boolean
  
    AddRecord = Val(Me.cmdInputRecords.Tag) = xlAdd
  
    answer = MsgBox(IIf(AddRecord, "Add New", "Update Current") & " Record?", 36, "Information")
    If answer = vbYes Then
      
        'new record
        If AddRecord Then CurrentRow = wsDailyHours.Range("A" & wsDailyHours.Rows.Count).End(xlUp).Row + 1
      
        On Error GoTo myerror
        With wsDailyHours
            [B].Cells(CurrentRow, 1).Value = txtWorkLeaveDate.Value[/B]
            .Cells(CurrentRow, 2).Value = cboSchedulingType.Value
            .Cells(CurrentRow, 3).Value = cboLocation.Value
            '.Cells(CurrentRow, 5).Value = txtStartTime.Value
            '.Cells(CurrentRow, 6).Value = txtFinishTime.Value
            .Cells(CurrentRow, 5).Value = CDate(Format(txtStartTime.Text, "hh:mm"))
            .Cells(CurrentRow, 6).Value = CDate(Format(txtFinishTime.Text, "hh:mm"))
            .Cells(CurrentRow, 11).Value = cboPayRate.Value
            .Cells(CurrentRow, 13).Value = CheckBoxPete.Value
            .Cells(CurrentRow, 14).Value = CheckBoxKirsty.Value
            .Cells(CurrentRow, 15).Value = CheckBoxJan.Value
            .Cells(CurrentRow, 16).Value = CheckBoxKelly.Value
            .Cells(CurrentRow, 17).Value = CheckBoxCarla.Value
        End With
      
        MsgBox "Record has been " & IIf(AddRecord, "added", "updated") & " to the database", 64, "Information"
      
    End If
    
    With ActiveSheet
    Application.Goto Reference:=.Cells(.Rows.Count, "A").End(xlUp).Offset(-15), Scroll:=True
    End With

    Call cmdClearForm_Click
    txtWorkLeaveDate.SetFocus
  
myerror:
 If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Many thanks

 

[Alex Blakenburg]

Just on an initial look why are you formatting the date as US date ?
txtWorkLeaveDate.Value = Format(Date, "mm/dd/yyyy")

 

[Alex Blakenburg]

Also in the second lot of code change this to add CDate

.Cells(CurrentRow, 1).Value = CDate(txtWorkLeaveDate.Value)

 

[Denny57]

Just on an initial look why are you formatting the date as US date ?
txtWorkLeaveDate.Value = Format(Date, "mm/dd/yyyy")

Apologies, this was probably left from some testing I was doing to try to identify where the problem was happening