Looping through a specific column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carlee - 21 Apr 2007 00:06 GMT

Hi,

I want to be able to loop through column B and search for a value that is
stored in a text box (txtCriteria1) on my userform. If the value is found in
column B, display message saying 'exists', else, display a message that says
'does not exist'.

Can anyone help?

Signature

Carlee

Reply to this Message

Norman Jones - 21 Apr 2007 00:50 GMT

Hi Carlee,

One way:

'=============>>
Private Sub CommandButton1_Click()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim sStr As String
Dim msg As String

Set WB = ThisWorkbook
Set SH = WB.Sheets("Sheet1")
Set Rng = SH.Columns("B:B")
sStr = Me.txtCriteria1.Value

If Application.CountIf(Rng, sStr) Then
msg = sStr & " found"
Else
msg = sStr & " not found"
End If

MsgBox Prompt:=msg

End Sub
'<<=============

---
Regards,
Norman

> Hi,
>
[quoted text clipped - 6 lines]
>
> Can anyone help?

Reply to this Message