prompt a msg in loop code

[tkraju via OfficeKB.com]

I am testing a range whether a value exists or not.If does not exists prompt
a msg else give me cell address.
My code is:
Private sub CommandButton1_Click()
Dim i as integer
Rng=Cells(Sheet1.Rows.Count,"A").EndXlUp.Row
For i= 1 to Rng
If Cells(i, "A")=Me.TextBox1.Text Then
Me.TextBox2.Text=Cells(i, "B").Address
End If
Next
End Sub
I need a Msg"Value does not exists in your sheet".Where I should put line of
code to get the desired results.

 

[Jacob Skaria]

Use a boolean variable and set the value to true if found. Also exit the loop
if found

Private Sub CommandButton1_Click()

Dim i As Integer
Dim blnFound As Boolean
Rng = Cells(Sheet1.Rows.Count, "A").EndXlUp.Row
For i = 1 To Rng
If Cells(i, "A") = Me.TextBox1.Text Then
Me.TextBox2.Text = Cells(i, "B").Address
blnFound = True: Exit For
End If
Next

If blnFound <> True Then MsgBox "Value does not exists in your sheet"
End Sub

If this post helps click Yes

 

[FSt1]

hi
first see if the loop can find it. if not, after the loop, put the not
found message.
and this line had an error. i fixed it.
Private Sub CommandButton1_Click()
Dim i As Long
Dim c As Long
c = 0
Rng = Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
For i = 1 To Rng
If Cells(i, "A") = Me.TextBox1.Text Then
Me.TextBox2.Text = Cells(i, "B").Address
c = c + 1
End If
Next
If c = 0 Then 'if c is greater than 0, found else not found
MsgBox "not found"
End If
End Sub

regards
FSt1

 

[Jacob Skaria]

The below macro will populate the date in A1 8 times and continue to do so
until end of month.. If you are new to macros launch VBE using Alt+F11.
Insert Module and paste the below macro.Save and get back to workbook. Run
macro1 under. Tools|Macro

Sub Macro1()
Dim dtStart
Dim lngRow
Dim intTemp

lngRow = 2
For dtStart = Range("A1") To Range("A1") + 30
If Month(dtStart) <> Month(Range("A1")) Then Exit Sub
For intTemp = 1 To 8
If lngRow = 2 Then intTemp = intTemp + 1
Range("A" & lngRow) = dtStart
lngRow = lngRow + 1
Next
Next
End Sub

 

[Jacob Skaria]

Sorry the below is a wrong post