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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Try declaring ABnum as Long and converting the text value from the textbox to a date.
Code:
ABnum = DateValue(TextBox1.Value)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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