vba. search works for number but not date, what have i done wrong?

dizzydunham

New Member
Joined
Mar 22, 2014
Messages
30
I have entered the code below from searching the web, all works spot on when I put week ending in the column and search text box, but if I put dates in the column instead and a date in the search textbox then I get a error highlighting ABnum=Textbox1.value

As a learner in this world, its probably going to blinding obvious to most of you, but I cant see it. Ive tried a few things like dim double to integer, but got no where.

Any help gratefully received.:p


Sub CommandButton1_Click()
' To write edited info of userform2 to Sheets("Succession Database")
Dim LastRow As Long
Dim ABnum As Double
Dim ABrng As Range
Dim WriteRow As Long


' Make sure we're on the right sheet dc2a1
Sheets("DC2A1").Select
With ActiveSheet
' Get the last row used so can set up the search range
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
' Set the range to search for the AB number
Set ABrng = .Range("A1:A" & LastRow)
' Get the AB number from what is selected on userform2
ABnum = TextBox1.Value



' Get the row of sheet for this AB number
WriteRow = Application.Match(ABnum, ABrng, 0)


' Make this AB number the active cell
Cells(WriteRow, 1).Select
' Write in all the editable stuff, don't bother with the non-editable things
With ActiveCell
.Offset(0, 18).Value = ComboBox2.Value
.Offset(0, 15).Value = ComboBox1.Value
.Offset(0, 16).Value = TextBox2.Value
.Offset(0, 17).Value = Now()
'etc.
'etc.
End With
End With

'next sheet dc2a2
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,273
Office Version
  1. 365
Platform
  1. Windows
Try declaring ABnum as Long and converting the text value from the textbox to a date.
Code:
ABnum = DateValue(TextBox1.Value)
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Hi

ABnum has been declared type Double (i.e. a number).

If you input a date, e.g. "02/12/2016", then TextBox1 will yield a String. This means you should get a type mismatch error.

To convert your date to a number, try:

ABnum = DateValue(Me.TextBox1.Value)

Then you can match that result against the column of dates.

Note, WriteRow is also declared a number type (i.e. Long). So if Application.Match doesn't make a match you will get an Error, which is also not a numeric type.
 

dizzydunham

New Member
Joined
Mar 22, 2014
Messages
30
Thanks chaps, faster than I imagined on the response times, all makes sense,

Thanks.
 

dizzydunham

New Member
Joined
Mar 22, 2014
Messages
30

ADVERTISEMENT

I did as you suggested last night whilst at work to the spreadsheet i am working on and works as a dream, and i did not pick up on the last bit of your message until i ran it through with a incorrect date to match, and exactly as you pointed out it crashed. How do i work round this? grateful for suggestions or answers.
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
There are a few options, one way is:

Declare WriteRow as Variant:
Rich (BB code):
Dim WriteRow As Variant

Test WriteRow before writing in the editable stuff
Rich (BB code):
WriteRow = Application.Match(ABnum, ABrng, 0)
If Isnumeric(WriteRow) Then
    'do your stuff
End If
 

Watch MrExcel Video

Forum statistics

Threads
1,127,768
Messages
5,626,769
Members
416,202
Latest member
donya ba

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
Top