Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using search function in VBA
Hi,
I'm very new to VBA. sorry for asking dumb question. I'm trying to do the below and it is not working. x = 500 Do While Cells(x, 2).Value < "" y = 100 Do While Cells(y, 5).Value < "" if worksheetfunction.search(cells(x, 2), cells(y, 5)) 0 then ' do something end if y = y + 1 loop x = x + 1 loop Here I want to search the text string of each cell from one range into another range. How do I do this. The above code compiles fine. But I'm getting a runtime error on the line "if worksheetfunction.search(cells(x, 2), cells(y, 5)) 0 then". Looks like the search function can not take variable such as x and y. Thanks for any help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using search function in VBA
Rather than solve the problem I think you should look at Chip Pearson's wesite. It will give you a lot of answers and good examples 'CPearson.com Topic Index' (http://www.cpearson.com/Excel/topic.aspx) -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=163425 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using search function in VBA
I think this is what you are wanting. Currently, the code will search all
the data you have in Col. A in Range("B1:B100"). You may need to tweak it to fit your application. Let me know if I can help more. Hope this helps! If so, let me know, click "YES" below. Option Explicit Sub SearchRange() Dim lngLastRow As Long Dim rngMyRange As Range Dim rngFound As Range Dim rng As Range ' find last cell with data in Col.A lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row ' range you want to find stuff in Set rngMyRange = Range("B1:B100") ' go thru each cell in For Each rng In Range("A1:A" & lngLastRow) Set rngFound = rngMyRange.Find(What:=rng.Text, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True, _ SearchFormat:=False) If Not rngFound Is Nothing Then MsgBox "Found " & rng.Value & " in " & rngFound.Address ' do something if found Else MsgBox "Didn't find anything." ' do this if not found End If Next rng End Sub -- Cheers, Ryan "firsttimevba" wrote: Hi, I'm very new to VBA. sorry for asking dumb question. I'm trying to do the below and it is not working. x = 500 Do While Cells(x, 2).Value < "" y = 100 Do While Cells(y, 5).Value < "" if worksheetfunction.search(cells(x, 2), cells(y, 5)) 0 then ' do something end if y = y + 1 loop x = x + 1 loop Here I want to search the text string of each cell from one range into another range. How do I do this. The above code compiles fine. But I'm getting a runtime error on the line "if worksheetfunction.search(cells(x, 2), cells(y, 5)) 0 then". Looks like the search function can not take variable such as x and y. Thanks for any help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
using search function in VBA
Fully describe what you want to do (don't include code)
Regards, Peter T "firsttimevba" wrote in message ... Hi, I'm very new to VBA. sorry for asking dumb question. I'm trying to do the below and it is not working. x = 500 Do While Cells(x, 2).Value < "" y = 100 Do While Cells(y, 5).Value < "" if worksheetfunction.search(cells(x, 2), cells(y, 5)) 0 then ' do something end if y = y + 1 loop x = x + 1 loop Here I want to search the text string of each cell from one range into another range. How do I do this. The above code compiles fine. But I'm getting a runtime error on the line "if worksheetfunction.search(cells(x, 2), cells(y, 5)) 0 then". Looks like the search function can not take variable such as x and y. Thanks for any help! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
using search function in VBA
Hi Ryan,
thanks so much. It is almost working except that the FIND function is trying to find an identical match (whole word). What I'm looking for is a substring match. For example, a match if "abc" is found inside "abcdef" and I will want to update a write the abcdef to another cell. I can try to dig it up to see how to do some type of wildcard matching. But if you can give me another hint I will appreciate a lot. Thank you Charles "Ryan H" wrote: I think this is what you are wanting. Currently, the code will search all the data you have in Col. A in Range("B1:B100"). You may need to tweak it to fit your application. Let me know if I can help more. Hope this helps! If so, let me know, click "YES" below. Option Explicit Sub SearchRange() Dim lngLastRow As Long Dim rngMyRange As Range Dim rngFound As Range Dim rng As Range ' find last cell with data in Col.A lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row ' range you want to find stuff in Set rngMyRange = Range("B1:B100") ' go thru each cell in For Each rng In Range("A1:A" & lngLastRow) Set rngFound = rngMyRange.Find(What:=rng.Text, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True, _ SearchFormat:=False) If Not rngFound Is Nothing Then MsgBox "Found " & rng.Value & " in " & rngFound.Address ' do something if found Else MsgBox "Didn't find anything." ' do this if not found End If Next rng End Sub -- Cheers, Ryan "firsttimevba" wrote: Hi, I'm very new to VBA. sorry for asking dumb question. I'm trying to do the below and it is not working. x = 500 Do While Cells(x, 2).Value < "" y = 100 Do While Cells(y, 5).Value < "" if worksheetfunction.search(cells(x, 2), cells(y, 5)) 0 then ' do something end if y = y + 1 loop x = x + 1 loop Here I want to search the text string of each cell from one range into another range. How do I do this. The above code compiles fine. But I'm getting a runtime error on the line "if worksheetfunction.search(cells(x, 2), cells(y, 5)) 0 then". Looks like the search function can not take variable such as x and y. Thanks for any help! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
using search function in VBA
Change this part of the Find method...
LookAt:=xlWhole to this... LookAt:=xlPart By the way, all the possible arguments for the Find function are shown in the help files for it. Just put your text cursor on or next to the word find (in your code) and press F1... you want the section that applies to the Range object. -- Rick (MVP - Excel) "firsttimevba" wrote in message ... Hi Ryan, thanks so much. It is almost working except that the FIND function is trying to find an identical match (whole word). What I'm looking for is a substring match. For example, a match if "abc" is found inside "abcdef" and I will want to update a write the abcdef to another cell. I can try to dig it up to see how to do some type of wildcard matching. But if you can give me another hint I will appreciate a lot. Thank you Charles "Ryan H" wrote: I think this is what you are wanting. Currently, the code will search all the data you have in Col. A in Range("B1:B100"). You may need to tweak it to fit your application. Let me know if I can help more. Hope this helps! If so, let me know, click "YES" below. Option Explicit Sub SearchRange() Dim lngLastRow As Long Dim rngMyRange As Range Dim rngFound As Range Dim rng As Range ' find last cell with data in Col.A lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row ' range you want to find stuff in Set rngMyRange = Range("B1:B100") ' go thru each cell in For Each rng In Range("A1:A" & lngLastRow) Set rngFound = rngMyRange.Find(What:=rng.Text, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True, _ SearchFormat:=False) If Not rngFound Is Nothing Then MsgBox "Found " & rng.Value & " in " & rngFound.Address ' do something if found Else MsgBox "Didn't find anything." ' do this if not found End If Next rng End Sub -- Cheers, Ryan "firsttimevba" wrote: Hi, I'm very new to VBA. sorry for asking dumb question. I'm trying to do the below and it is not working. x = 500 Do While Cells(x, 2).Value < "" y = 100 Do While Cells(y, 5).Value < "" if worksheetfunction.search(cells(x, 2), cells(y, 5)) 0 then ' do something end if y = y + 1 loop x = x + 1 loop Here I want to search the text string of each cell from one range into another range. How do I do this. The above code compiles fine. But I'm getting a runtime error on the line "if worksheetfunction.search(cells(x, 2), cells(y, 5)) 0 then". Looks like the search function can not take variable such as x and y. Thanks for any help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SEARCH Function | Excel Discussion (Misc queries) | |||
SEARCH function | Excel Programming | |||
Search Function | Excel Discussion (Misc queries) | |||
search function | New Users to Excel | |||
search function | Excel Programming |