Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error in Sub
Hi
I've copied this out of the help in Excel 2003 and amended it for my use, it works and corrects the data but I get a debug error on the Loop line. The data is JW F1871810 and I want it to be F1871810 With Worksheets("Sheet2").Range("A:A") Set c = .Find("JW F", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Value = Mid((c), 4, 8) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Thanks Wendy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error in Sub
With Worksheets("Sheet2").Range("A:A")
Set c = .Find("JW F", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Value = Mid((c), 4, 8) Set c = .FindNext(c) if c is nothing then exit do end if Loop End If End With After the last cell with "JW F" is found and changed, then the next .Findnext will return nothing (same as not found). The loop statent tries to use c.address and that won't work when c is nothing. Ps. I'd change that .find line to something like: Set c = .Find(What:="JW F*", After:=.cells(1), LookIn:=xlvalues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If you don't specify exactly what you want, you'll inherit the settings from the previous find--either from the user (via edit|Find) or by code. And by looking for "JW F*" and xlwhole, you'll be looking for strings that begin with "JW F". Wendy wrote: Hi I've copied this out of the help in Excel 2003 and amended it for my use, it works and corrects the data but I get a debug error on the Loop line. The data is JW F1871810 and I want it to be F1871810 With Worksheets("Sheet2").Range("A:A") Set c = .Find("JW F", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Value = Mid((c), 4, 8) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Thanks Wendy -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error in Sub
If you are looking for a cell with JW F in it, the Mid(c,4,8) is pointless,
it will always return just F. And because you modify the cell, it will never loop back to the firstcell, so testing for it is pointless. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Wendy" wrote in message ... Hi I've copied this out of the help in Excel 2003 and amended it for my use, it works and corrects the data but I get a debug error on the Loop line. The data is JW F1871810 and I want it to be F1871810 With Worksheets("Sheet2").Range("A:A") Set c = .Find("JW F", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Value = Mid((c), 4, 8) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Thanks Wendy |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error in Sub
Thanks, Dave that worked. It does mean that microsoft's excel 2003's help
doesnt! - At least in that example. Wendy "Bob Phillips" wrote in message ... If you are looking for a cell with JW F in it, the Mid(c,4,8) is pointless, it will always return just F. And because you modify the cell, it will never loop back to the firstcell, so testing for it is pointless. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Wendy" wrote in message ... Hi I've copied this out of the help in Excel 2003 and amended it for my use, it works and corrects the data but I get a debug error on the Loop line. The data is JW F1871810 and I want it to be F1871810 With Worksheets("Sheet2").Range("A:A") Set c = .Find("JW F", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Value = Mid((c), 4, 8) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Thanks Wendy |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error in Sub
MS changed the sample in its help.
At one time, it changed the color of the font (or fill). This meant that the ..findnext() would still find it again. But when they decided to change the value of that found cell, they broke their sample code. Wendy wrote: Thanks, Dave that worked. It does mean that microsoft's excel 2003's help doesnt! - At least in that example. Wendy "Bob Phillips" wrote in message ... If you are looking for a cell with JW F in it, the Mid(c,4,8) is pointless, it will always return just F. And because you modify the cell, it will never loop back to the firstcell, so testing for it is pointless. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Wendy" wrote in message ... Hi I've copied this out of the help in Excel 2003 and amended it for my use, it works and corrects the data but I get a debug error on the Loop line. The data is JW F1871810 and I want it to be F1871810 With Worksheets("Sheet2").Range("A:A") Set c = .Find("JW F", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Value = Mid((c), 4, 8) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Thanks Wendy -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|