Using TextBox data from user form to find cell in column

dannyh

Board Regular
Joined
Oct 15, 2007
Messages
156
Hi All

I have a simple loop looking for a date in a column based on whatever is typed in the Text Box in my user form, I know the date is there but it just keeps looping past it. I have tried testing putting in a simple number “5” and searching for that and again it fails. Is there any other tests I can do ?

My loop code
Code:
Rng = UserForm3.TextBox1
Range(“A1”).select
Do Until ActiveCell.Value = Rng
ActiveCell.Offset(1,0)
Loop

Thanks Dan
 
Hi @dannyh

For side of it, try:
VBA Code:
Sub Macro1()

On Error Resume Next
[A:A].Find(UserForm3.TextBox1, , xlFormulas, xlPart).Offset(,1).Resize(, 9).Value = Sheets("Data").[G5:O5].Value
On Error GoTo 0

End Sub
Unsure about not finding date, could be the format of values in column A are not set to date or the value being searched for isn't recognised as a date.

As a guess, I've commented out the error surpressions and forced the search to be a date, try:
VBA Code:
Sub Macro1()

'On Error Resume Next
With UserForm3
    [A:A].Find(DateSerial(Year(.TextBox1), Month(.TextBox1), Day(.TextBox1)), , xlFormulas, xlPart).Offset(, 1).Resize(, 9).Value = Sheets("Data").[G5:O5].Value
End With
'On Error GoTo 0

End Sub

Hi Jack

Thanks, I had altered the offset to get the data correctly placed, I think it is the format of the TestBox which isn't in a date format that is causing the problem when I search for values with a general format they are found.

Dan
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Cool, both issues resolved?

Only way to get it to work is to format the dates in col A as text, found an article here detailing the issues with using dates in textbox's, thanks for your help.

 
Upvote 0
No worries and thanks for sharing!

Noticed very occasionally issues with dates in VBA where if on a non US Windows time/language setting, the code reads the dates in US format, regardless of global setting so your code is processing dates, differently to what the sheet shows, even with formatting set to Date etc.
Seemed to be a typical MS fix (e.g. restart), close Excel, change the setting then revert back to whatever local one it is you want, then reopen Excel. Something to be aware of, more than any other use.
 
Upvote 0
No worries and thanks for sharing!

Noticed very occasionally issues with dates in VBA where if on a non US Windows time/language setting, the code reads the dates in US format, regardless of global setting so your code is processing dates, differently to what the sheet shows, even with formatting set to Date etc.
Seemed to be a typical MS fix (e.g. restart), close Excel, change the setting then revert back to whatever local one it is you want, then reopen Excel. Something to be aware of, more than any other use.

Hi Jack

Funny you should say that I now have that exact issue, I am entering a UK date 11/03/2020 in a textbox and it is being copied across as 03/11/2020 !!!
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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