Search and replace question

Libby12 · January 17th, 2010, 02:49 AM

Could someone please help with this excel problem? I want to look (search) for all lines that have Nod1, Nod2, Nod3, ect. and remove the 8th and 9th letter from the left (which would be 30 from example below) Just for example: Line A7 would read Nod1=0,60812,0 and on A8 Nod2=1,16978,0 ect and keep doing that on down the line for everything that starts with a Nod. Thank you for any help you can provide
A
1 [POLYLINE]
2 Type=0x6
3 CityIdx=2910
4 RoadID=14249
5 RouteParam=3,2,0,0,0,0,0,0,0,0,0,0
6 Data0=(22.44593,-117.09999),(22.44842,-117.09934)
7 Nod1=0,3060812,0
8 Nod2=1,3016978,0
9 [END]
10
11 [POLYLINE]
12 Type=0x6
13 CityIdx=3310
14 RoadID=14301
15 RouteParam=3,2,0,0,0,0,0,0,0,0,0,0
16 Data0=(22.44836,-117.10010),(22.44816,-117.09973)
17 Nod1=0,3016948,0
18 Nod2=1,3016967,0
19 [END]

Shasur · January 17th, 2010, 09:14 PM

Can you try the following:

Code:

Sub Find_Replace_Cell()
Dim oWS As Worksheet
Dim oRng As Range
Dim FirstUL
Set oWS = ActiveSheet
Application.FindFormat.Clear
Application.FindFormat.Font.Color = vbBlue

Set oRng = oWS.Range("A:A").Find(What:="Nod", LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
If Not oRng Is Nothing Then
    FirstUL = oRng.Address
    Do
        oRng.Value = Left(oRng.Value, 7) & Mid(oRng.Value, 9, Len(oRng.Value))
        Set oRng = oWS.Range("A:A").FindNext(oRng)
    Loop While Not oRng Is Nothing And oRng.Address <> FirstUL
End If
End Sub

Cheers
Shasur

Libby12 · January 17th, 2010, 11:13 PM

Thanks that did work, I have another question I was wondering if you could help me with, I sent you my email on your vb blog. Thank you.

Libby12 · January 18th, 2010, 12:15 AM

On Line A16 it start to delete the wrong number, instead of deleting the 8th number from the left, can you tell it to just delete the 8th and 9th character from the right on all Nod , Thank you

A
1 [POLYLINE]
2 Type=0x6
3 Label=HILL ST
4 CityIdx=4566
5 RoadID=34552
6 RouteParam=1,1,0,0,0,0,0,0,0,0,0,0
7 Data0=(37.50024,-121.11381)
8 Nod1=0,3060239,0
9 Nod2=1,3016591,0
10 Nod3=2,3016677,0
11 Nod4=5,3016759,0
12 Nod5=6,3016765,0
13 Nod6=7,3016772,0
14 Nod7=8,3016780,0
15 Nod8=9,3016790,0
16 Nod9=10,3016807,0
17 Nod10=11,3016817,0
18 Nod11=12,3016834,0
19 Nod12=13,3016900,0
20 Nod13=14,3016928,0
21 Nod14=15,3016951,0
22 [END]

Libby12 · January 18th, 2010, 12:16 AM

On Line A16 it start to delete the wrong number, instead of deleting the 8th number from the left, can you tell it to just delete the 8th and 9th character from the right on all Nod , Thank you

A
1 [POLYLINE]
2 Type=0x6
3 Label=HILL ST
4 CityIdx=4566
5 RoadID=34552
6 RouteParam=1,1,0,0,0,0,0,0,0,0,0,0
7 Data0=(37.50024,-121.11381)
8 Nod1=0,3060239,0
9 Nod2=1,3016591,0
10 Nod3=2,3016677,0
11 Nod4=5,3016759,0
12 Nod5=6,3016765,0
13 Nod6=7,3016772,0
14 Nod7=8,3016780,0
15 Nod8=9,3016790,0
16 Nod9=10,3016807,0
17 Nod10=11,3016817,0
18 Nod11=12,3016834,0
19 Nod12=13,3016900,0
20 Nod13=14,3016928,0
21 Nod14=15,3016951,0
22 [END]

Quote:

Originally Posted by Shasur

Can you try the following:

Code:

Sub Find_Replace_Cell()
Dim oWS As Worksheet
Dim oRng As Range
Dim FirstUL
Set oWS = ActiveSheet
Application.FindFormat.Clear
Application.FindFormat.Font.Color = vbBlue

Set oRng = oWS.Range("A:A").Find(What:="Nod", LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
If Not oRng Is Nothing Then
    FirstUL = oRng.Address
    Do
        oRng.Value = Left(oRng.Value, 7) & Mid(oRng.Value, 9, Len(oRng.Value))
        Set oRng = oWS.Range("A:A").FindNext(oRng)
    Loop While Not oRng Is Nothing And oRng.Address <> FirstUL
End If
End Sub

Cheers
Shasur

Shasur · January 18th, 2010, 02:06 AM

Hi

Can you try this:

[code]
Sub Find_Replace_Cell2()
Dim oWS As Worksheet
Dim oRng As Range
Dim FirstUL
Set oWS = ActiveSheet
Application.FindFormat.Clear
Application.FindFormat.Font.Color = vbBlue
Set oRng = oWS.Range("A:A").Find(What:="Nod", LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
If Not oRng Is Nothing Then
FirstUL = oRng.Address
Do
oRng.Value = Mid(oRng.Value, 1, Len(oRng.Value) - Len(Right(oRng.Value, 9))) & Right(oRng.Value, 7)
Set oRng = oWS.Range("A:A").FindNext(oRng)
Loop While Not oRng Is Nothing And oRng.Address <> FirstUL
End If
End Sub
[code]
Cheers
Shasur

Libby12 · January 18th, 2010, 02:20 AM

It deletes the 3 when the first execution is done, then the second execution deletes the (,) comma, need it to delete the 0. Copy What I have below, and try in your excel, thank you for your help...

[POLYLINE]
Type=0x6
Label=Hill St
CityIdx=4310
RoadID=69589
RouteParam=3,2,0,0,0,0
Data0=(32.51700)
Nod1=0,3020635,0
Nod2=1,3020688,0
Nod3=2,3020711,0
Nod4=3,3020739,0
Nod5=4,3020765,0
Nod6=5,3020792,0
Nod7=6,3020802,0
Nod8=7,3020803,0
Nod9=8,3020838,0
Nod10=9,3020839,0
Nod11=10,3020853,0
Nod12=11,3020884,0
Nod13=12,3020917,0
Nod14=13,3020935,0
Nod15=14,3020966,0
Nod16=15,3020997,0
Nod17=16,3021024,0
Nod18=17,3021053,0
Nod19=18,3021077,0
Nod20=19,3021104,0
Nod21=20,3021128,0
Nod22=21,3021144,0
Nod23=22,3021167,0
Nod24=23,3021200,0
Nod25=24,3021240,0
Nod26=25,3021283,0
Nod27=26,3021294,0
[END]

Quote:

Originally Posted by Shasur

Hi

Can you try this:

[code]
Sub Find_Replace_Cell2()
Dim oWS As Worksheet
Dim oRng As Range
Dim FirstUL
Set oWS = ActiveSheet
Application.FindFormat.Clear
Application.FindFormat.Font.Color = vbBlue
Set oRng = oWS.Range("A:A").Find(What:="Nod", LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
If Not oRng Is Nothing Then
FirstUL = oRng.Address
Do
oRng.Value = Mid(oRng.Value, 1, Len(oRng.Value) - Len(Right(oRng.Value, 9))) & Right(oRng.Value, 7)
Set oRng = oWS.Range("A:A").FindNext(oRng)
Loop While Not oRng Is Nothing And oRng.Address <> FirstUL
End If
End Sub
[code]
Cheers
Shasur

Shasur · January 18th, 2010, 03:13 AM

I am getting the following output

Code:

[POLYLINE]Type=0x6Label=Hill StCityIdx=4310RoadID=69589RouteParam=3,2,0,0,0,0Data0=(32.51700)Nod1=0,20635,0Nod2=1,20688,0Nod3=2,20711,0Nod4=3,20739,0Nod5=4,20765,0Nod6=5,20792,0Nod7=6,20802,0Nod8=7,20803,0Nod9=8,20838,0Nod10=9,20839,0Nod11=10,20853,0Nod12=11,20884,0Nod13=12,20917,0Nod14=13,20935,0Nod15=14,20966,0Nod16=15,20997,0Nod17=16,21024,0Nod18=17,21053,0Nod19=18,21077,0Nod20=19,21104,0Nod21=20,21128,0Nod22=21,21144,0Nod23=22,21167,0Nod24=23,21200,0Nod25=24,21240,0Nod26=25,21283,0Nod27=26,21294,0

Is this fine

Code:

Sub Find_Replace_Cell2()
Dim oWS As Worksheet
Dim oRng As Range
Dim FirstUL
Set oWS = ActiveSheet
Application.FindFormat.Clear
'Application.FindFormat.Font.Color = vbBlue
Set oRng = oWS.Range("A:A").Find(What:="Nod", LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
If Not oRng Is Nothing Then
    FirstUL = oRng.Address
    Do
        oRng.Value = Mid(oRng.Value, 1, Len(oRng.Value) - Len(Right(oRng.Value, 9))) & Right(oRng.Value, 7)
         Set oRng = oWS.Range("A:A").FindNext(oRng)
    Loop While Not oRng Is Nothing And oRng.Address <> FirstUL
End If
End Sub

Cheers
Shasur

Libby12 · January 18th, 2010, 01:16 PM

Thank you Shasur, it works good now....