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?



"jhahes" <[email protected]> schreef in
bericht news:[email protected]...
>
> 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
>

[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


--
HTH

Bob Phillips

"jhahes" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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" <[email protected]> skrev i
melding news:[email protected]...
>
> 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
>

[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)

--
HTH

Bob Phillips

"jhahes" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>