VBA Replace values help

[wisemank]

Hi there,

I want to fill in the blank cells in between two designated text strings. I column L, I have either blank cells or the words "DNIS:ARC" or "DNIS".

I need the replace the only the blank cells between "DNIS:ARC" and "DNIS" with the word "ARC" all the way through the data set in column L.

much appreciated.

 

[wisemank]

example: Column L (before) Column L (after)
1 DNIS:ARC DNIS:ARC
2 ARC (filled in)
3 ARC
4 ARC
5 DNIS: DNIS:
6
7
8
9
10 DNIS:ARC DNIS:ARC
11 ARC
12 ARC
13 ARC
14 DNIS: DNIS:

 

[rpaulson]

try this

Sub do_it()

For r = 1 To Cells(Rows.Count, "L").End(xlUp).Row
If Cells(r, "L") = "DNIS:ARC" And Cells(r + 1, "L") = "" And Cells(r + 2, "L") = "DNIS" Then Cells(r + 1, "L") = "Arc"
Next r

End Sub

hth,
Ross

 

[wisemank]

I ran it and nothing happened...?

 

[rpaulson]

what you want it to do is still unclear to me.

 

[wisemank]

Thanks again...

I want to replace all blank cells in between the words DNIS:ARC and DNIS with the word ARC.

 

[rpaulson]

how about this?

Sub do_it()

For r = 1 To Cells(Rows.Count, "L").End(xlUp).Row

If Cells(r, "L") = "DNIS:ARC" Then x = "On"
If Cells(r, "L") = "DNIS" Then x = "Off"

If Cells(r + 1, "L") = "" And x = "On" Then Cells(r + 1, "L") = "ARC"
Next r

End Sub

Ross

 

[wisemank]

Ok... Now I get all the blanks filled with the word ARC (progress). I Only need to put "ARC" from DNIS:ARC to DNIS and the rest of the blanks can remain blank.

 

[rpaulson]

oops: need a colon

If Cells(r, "L") = "DNIS" Then x = "Off"
to
If Cells(r, "L") = "DNIS:" Then x = "Off"

 

[Michael M]

Maybe this

Sub MM1()
Dim lr As Long
lr = Cells(Rows.Count, "L").End(xlUp).Row
Range("A1:A" & lr).SpecialCells(xlCellTypeBlanks).Value = "ARC"
End Sub