Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste value of Find
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste value of Find
John Keturi wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste value of Find
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 -- Regards, Tom Ogilvy "John Keturi" wrote in message news:1Gccd.38720$hj.12302@fed1read07... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste value of Find
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 John Keturi wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste value of Find
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). -- Regards, Tom Ogilvy "Myrna Larson" wrote in message ... 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 John Keturi wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste value of Find
I got the feeling he didn't have a tight grasp on what the code was doing <g.
On Sat, 16 Oct 2004 16:05:02 -0400, "Tom Ogilvy" wrote: 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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Cut and paste | Excel Discussion (Misc queries) | |||
Find, copy and paste | Excel Discussion (Misc queries) | |||
Find, Cut, Paste and Delete | Excel Worksheet Functions | |||
Macro to Find, Cut, and Paste | Excel Discussion (Misc queries) |