Offset the replacement

[gordsky]

Hi,

I know I could use a loop or FindNext to achieve this but wondering if there is anyway to do it in a one liner by modifying the below (or another way)

Obviously the below does exactly what I want if the replacement were required in ColB but is there anyway to offset the 'Replacement' to ColC

Range("B:B").Replace What:="*WF", Replacement:="0", LookAt:=xlWhole

Basically if the value WF appears anywhere in colB then change the value of ColC to 0.

 

[Marius44]

Hello
Try this macro (only valid for the first 5 rows of column A)

Sub tryOne()
With Worksheets(1).Range("a1:a5")
    Set c = .Find("*WF*")
    If Not c Is Nothing Then
        Do
            c.Value = 0
            Set c = .FindNext(c)
        Loop While Not c Is Nothing
    End If
End With
End Sub

Hello,
Mario

 

[gordsky]

Thanks for the reply but as per the post looking for a way to do this without a loop

 

[Alex Blakenburg]

What do you want in column C ?
If you want column B replicated exept that WF is replaced by 0 then you could use an array with a substitute command.
Mind you, you could get this by just copying the whole of column B to C and doing the replace there, the array would just be a bit faster.

If you just want the WF's as zero in column C then you could filter on WF in column B, and then using xlCellTypeVisible to enter a 0 in column C for just those rows.

I don't believe there is a way of modifying the find and replace command do what you want to do.

 

[Peter_SSs]

Sample data and expected results is usually helpful to understand exactly what you have and what you are trying to achieve.

(or another way)

Does this do what you want?
Assuming column C is not formulas.

Sub ReplaceInC()
  With Range("C1", Range("C" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(right(" & .Offset(, -1).Address & ",2)=""WF"",0,if(#="""","""",#))", "#", .Address))
  End With
End Sub

Before

gordsky.xlsm
	B	C
1	a	9
2	bWF	6
3	c	5
4
5	eWF	7
6	f	4
7	WFg	3
8	h	3
Sheet1

After

gordsky.xlsm
	B	C
1	a	9
2	bWF	0
3	c	5
4
5	eWF	0
6	f	4
7	WFg	3
8	h	3
Sheet1

 

[gordsky]

Peter_SSs - thank you so much this works perfectly. I wonder if I could as you to explain this bit of the code please

if(#="""","""",#))", "#", .address)

 

[Alex Blakenburg]

Peter_SSs - thank you so much this works perfectly. I wonder if I could as you to explain this bit of the code please

if(#="""","""",#))", "#", .address)

Hello Gordsky,

You can't just look at that part of the expression, the Replace is integral to the section you are showing.

Put a breakpoint on that line and copy the below into the immediate window (including the "?")

? Replace("if(right(" & .Offset(, -1).Address & ",2)=""WF"",0,if(#="""","""",#))", "#", .Address)

The idea is that the formula is being handed to Evaluate as a string but it needs certain parameters to be a variable.
To make that happen normally you need to break up the string into pieces so the variable can be inserted and then reassembled as a string eg as done in the Offset part of the string. To save doing this it is quite common to use a place holder for the variable in this case "#" and then use the Replace command to insert the variable into the string without needing to dissect it and reassemble it.

 

[Peter_SSs]

Peter_SSs - thank you so much this works perfectly. I wonder if I could as you to explain this bit of the code please

if(#="""","""",#))", "#", .address)

That part is to ensure that if a value in column C is blank that it stays blank and does not become zero. For example, like cell C4 in my previous post.
It is like the difference between these two formulas.

gordsky.xlsm
	B	C	D
4		0
Sheet4

Cell Formulas
Range	Formula
C4	C4	=B4
D4	D4	=IF(B4="","",B4)

 

[gordsky]

That part is to ensure that if a value in column C is blank that it stays blank and does not become zero. For example, like cell C4 in my previous post.
It is like the difference between these two formulas.

gordsky.xlsm
	B	C	D
4		0
Sheet4

Cell Formulas
Range	Formula
C4	C4	=B4
D4	D4	=IF(B4="","",B4)

thankyou for the explanation, much appreciated

 

[Peter_SSs]

You're welcome.