Paste value of Find

[John Keturi]

How do I get the value of c pasted into say C4, after the Do statement?
Should be easy, but I'm overlooking something? Thanks

Sub Find()
Dim myValue As Range
Set myValue = Range("C5")
With Worksheets(1).Range("a1:a100")
Set c = .Find(myValue, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
________________
y = MsgBox("Do you want to find Next?", vbYesNo, "Find Next?")
If y = vbNo Then Exit Do
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

 

[Alan Beban]

How do I get the value of c pasted into say C4, after the Do statement?
Should be easy, but I'm overlooking something? Thanks

Sub Find()
Dim myValue As Range
Set myValue = Range("C5")
With Worksheets(1).Range("a1:a100")
Set c = .Find(myValue, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
________________
y = MsgBox("Do you want to find Next?", vbYesNo, "Find Next?")
If y = vbNo Then Exit Do
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

myValue(0).Value = c

Alan Beban

 

[Tom Ogilvy]

Sub Find()
Dim myValue As Range
Set myValue = Range("C5")
With Worksheets(1).Range("a1:a100")
Set c = .Find(myValue, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Range("C4").Value = c
y = MsgBox("Do you want to find Next?", vbYesNo, "Find Next?")
If y = vbNo Then Exit Do

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

However the value in C will be the value in C5. What would be the basis of
the user wanting to findnext? the user can't see what cell was found and it
shouldn't make much difference to them which value for c is placed in C4
(they will all be the same). In fact, unless you might not be albe to find
the value of C5 in column A, you could shorten your code to

Sub Tester1()
Range("C4").Value = Range("C5").Value
End Sub

 

[Myrna Larson]

I'm confused!

A couple of points re your code:

1. Find is a built-in method. It is confusing to also use it as the name of a
Sub, even though VBA allows it.

2. You are using myValue as the *value* read *from* a range, not as a Range
object.

3. re the Loop statement: "c Is Nothing" is only possible with the very first
Find statement.

Let's say you find myValue in A38. Once you get into the loop, c will always
be found, since FindNext will eventually wrap back to A38. That's why you have
to check the address of c -- to know if you have gone full circle. Testing for
'c Is Nothing' isn't necessary.

4. WRT saving the value you find, You said "after the Do statement". But I if
you want to save the result of the *last* find, the new line belongs after the
Loop statement. As to what you really want to find, see points 5 and 6 below.

5. Why do you want to save "the value of c"? It will always be equal to
myValue, no?

6. I don't understand the point of the loop at all. You are looking in range
A1:A100 for the value in C5. If it's there, you want to put the same value in
C4. Why search for it a 2nd or 3rd or 4th time? c.Value will always be the
same as it was on the first hit, and it will always be equal to myValue. Why
not

myValue = Range("C5").Value
If IsNumeric(Application.Match(myValue, Range("A1:A100"), 0)) Then
Range("C4").Value = myValue
End If

OTOH, if you don't want c.Value at all, but, say, the value that's adjacent to
c, in column B, there's a point to the loop. See line marked with <<<

Assuming you need a loop, I think it should look like this:

Sub FindAValue()
Dim myValue As Variant 'since I don't know what kind of data it is
Dim c As Range
Dim firstAddress as String

myValue = Range("C5").Value

With Worksheets(1).Range("A1:A100")
Set c = .Find(myValue, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
y = MsgBox("Do you want to find Next?", vbYesNo, "Find Next?")
If y = vbNo Then Exit Do
Set c = .FindNext(c)
Loop While c.Address <> firstAddress
Range("C4").Value = myValue
'Range("C4").Value = c.Offset(0, 1).Value '<<<
End If
End With

End Sub

 

[Tom Ogilvy]

This is the help example for FindNext (and Find in xl97).

This example finds all cells in the range A1:A500 that contain the value 2
and makes those cells gray.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

So he just lifted most from what is provided in Help (and not sure why they
use the While Not c Is Nothing - maybe to generalize since the found cell
could be deleted in the loop if adapted to someones specific situation).

 

[Myrna Larson]

I got the feeling he didn't have a tight grasp on what the code was doing <g>.