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.

 

[Denny57]

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

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

Alex, once again thank you. I really must find some comprehensive information about how and what code should be applied when using VBA.. None of the reference books I have, and I have several, cover dates in any great detail.

 

[Alex Blakenburg]

You are not alone in having issues with dates. VBA has a tendency to use US dates at importune times. Match, Find, AutoFilter all need to be tweaked to get them to work. CDate and DateValue use your regional settings to convert to date. You often also need to throw in CLng which then uses the 5 digit number representing the date to ensure there is no ambiguity.

The most comprehensive treatment of this that I am aware of is:
Chapter 22 International Issues
Excel 2002 VBA Programmer's Reference
Written by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg
Which has been made available to the public here:

Office Automation Ltd. - Excel 2002/2003 VBA Programmer's Reference

OA Ltd is an independent consultancy for Excel and Access development. This site is the home of Stephen Bullen's Excel Page, containing many tools and examples for Excel.

www.oaltd.co.uk

 

[Denny57]

You are not alone in having issues with dates. VBA has a tendency to use US dates at importune times. Match, Find, AutoFilter all need to be tweaked to get them to work. CDate and DateValue use your regional settings to convert to date. You often also need to throw in CLng which then uses the 5 digit number representing the date to ensure there is no ambiguity.

The most comprehensive treatment of this that I am aware of is:
Chapter 22 International Issues
Excel 2002 VBA Programmer's Reference
Written by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg
Which has been made available to the public here:

Office Automation Ltd. - Excel 2002/2003 VBA Programmer's Reference

OA Ltd is an independent consultancy for Excel and Access development. This site is the home of Stephen Bullen's Excel Page, containing many tools and examples for Excel.

www.oaltd.co.uk

Alex, once again I apologise for the delay in acknowledging your response. Thank you so much for the link to the chapter in the reference book and I have downloaded this chapter. The information it which I am sure will be invaluable to me with future projects.

Regards

Dave

 

[Alex Blakenburg]

No problem. Hope you find it useful.