Text Box
 

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


--
jhahes
------------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=385678

Text Box
 

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

Added .Value after your TextBox1. Will that work?



"jhahes" schreef in
bericht ...

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


--
jhahes
------------------------------------------------------------------------
jhahes's Profile:
http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=385678

Text Box
 

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


--
HTH

Bob Phillips

"jhahes" wrote in
message ...

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


--
jhahes
------------------------------------------------------------------------
jhahes's Profile:
http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=385678

Text Box
 

ACtually, all you need is

Range("B3").Select

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


--
HTH

Bob Phillips

"jhahes" wrote in
message ...

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


--
jhahes
------------------------------------------------------------------------
jhahes's Profile:
http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=385678

Text Box
 

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" skrev i
melding ...

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


--
jhahes
------------------------------------------------------------------------
jhahes's Profile:
http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=385678

Text Box
 

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


--
jhahes
------------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=385678

Text Box
 

CDbl(TextBox1.value)

--
HTH

Bob Phillips

"jhahes" wrote in
message ...

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


--
jhahes
------------------------------------------------------------------------
jhahes's Profile:
http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=385678