Text Box

[jhahes]

I am having a problem with a text box trying to find a number format in
a column


Here is what i have


Range("B3").select

Do

If activecell.value <> TextBox1 then
activecell.offset(1,0).select
End if

Loop Until activecell.value = TextBox1


I enter 61630 in TextBox1

when it loops it goes all the way until 65655, and 61630 is in the 3rd
row.

I think it is trying to find text when 61630 is a number. However, I
really don't want to change the field to a text field, because other
columns are linked to them.

any help would be helpful

Thanks
Josh

 

[moi]

If Activecell.Value <> TextBox1.Value then
Activecell.Offset(1,0).Select
End if

Added .Value after your TextBox1. Will that work?

 

[Bob Phillips]

Textbox is a string, so you need to compare like to like

Range("B3").Select

Do
If CStr(ActiveCell.Value) <> ActiveSheet.TextBox1.Value Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until CStr(ActiveCell.Value) = ActiveSheet.TextBox1

 

[Bob Phillips]

ACtually, all you need is

Range("B3").Select

Do Until CStr(ActiveCell.Value) = ActiveSheet.TextBox1
ActiveCell.Offset(1, 0).Select
Loop

 

[Harald Staff]

Hi Josh

This will work umpteen times faster if you use the built-in search engine
instead of a VBA loop:

Private Sub CommandButton1_Click()
Dim R As Range
On Error Resume Next
Set R = Range("B3:B60000").Find(What:=TextBox1.Text, _
LookAt:=xlWhole)
If R Is Nothing Then
MsgBox "Bummer"
Else
R.Select
End If
End Sub

HTH. Best wishes Harald

 

[jhahes]

Thanks all for the responses, I haven't tried your solution Harold, but
Bob, your solution works great. I have one question regarding your
solution though. Say I wanted to reverse the logic. I wanted to put
the text box value in a sheet as a number. I know how to do this with
Curreny = Ccur(TextBox1.value), how do I do this to convert this to a
number format.

Thanks
Josh

 

[Bob Phillips]

CDbl(TextBox1.value)