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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200904/1

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
---------------
Jacob Skaria


"tkraju via OfficeKB.com" wrote:

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200904/1

To save all the looping, why not use find:


I set up a FindNext in my test Sub so you can see how to handle more than
one occurrence. If you need to loop for more than one occurrence, you will
need to remove the inverted commas and start modifying the code. Otherwise
delete the lines that begin with an inverted comma possibly also modify to
remove references to nextAddress.

Sub FindTxt()

dim findwhat, firstAddress, nextAddress, c

findWhat = Me.TextBox1.Text

With Worksheets(1).Range("a1:a500") 'alter worksheet and range
Set c = .Find(findWhat, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
' Do
nextAddress = c.Address
Me.TextBox2.Text = Range(nextAddress).Offset(, 1).Value

'Set c = .FindNext(c)
' Loop While Not c Is Nothing And c.Address < firstAddress
Else
MsgBox "Value does not exist"
End If
End With

--
Steve

"tkraju via OfficeKB.com" <u16627@uwe wrote in message
news:9446eabab48df@uwe...
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200904/1

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.
Rng = Cells(Sheet1.Rows.Count, "A").EndxlUp.Row

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

"tkraju via OfficeKB.com" wrote:

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200904/1

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

--
If this post helps click Yes
---------------
Jacob Skaria


"tkraju via OfficeKB.com" wrote:

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200904/1

Sorry the below is a wrong post
--
If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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

--
If this post helps click Yes
---------------
Jacob Skaria


"tkraju via OfficeKB.com" wrote:

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200904/1