Search & Replace

**Christopherdj** · 10-13-2011, 11:08 PM

I have the following macro but I need it to replace contents of a variable cell, then paste it into the replace field, it's almost a paste special however this isn't an option that I know of in search and replace. Can anyone fix this for me?

What I want is - copy contents of a cell in row 2 (e.g. B2), then select the entire row B, search for '01 New Client', and replace by a paste command (the contents of cell B2 - which is part of an IF( formula ). The cell reference of B2 for the initial copy is variable, as I want to use the same macro function another rows i.e. cell B3 to copy, search & replace into row 3, then 4, then 5 etc. See problem sheet attached - If formula is in Row D.

Please Login or Register  to view this content.

**Andrew-R** · 10-14-2011, 02:59 AM

I'm just trying to get to grips with what you're trying to do here.

Is the purpose of this macro to update the sheet reference in your formula when you add a new client?

If so then you can do it just with formula, there's no need to use a macro.

Replacing the formula in D3 of the problem sheet with =IF(ISERROR(INDIRECT("'"&B3&"'!A1")),"",IF(COUNTIF(INDIRECT("'"&B3&"'!E88:E100"),"Yes")>0,"Yes","")) should do the job.

**Christopherdj** · 10-16-2011, 06:27 PM

I'm not sure of your reply and its use, but yes I am trying to update the formula every time I add a new client. This is one of many sheets with different forumula's but all are based on a '01 new client' sheet reference that needs amending for each new client.

The macro I created works fine for the first client, however it doesn't allow for any subsequent client using search and replace. With the formula provided below, if the replacement:="SMITH, Robert" and "Jones, John" were a PASTE option/instruction, it would work fine. Is there a function to replace it?? like .PASTE (I tried this and it doesn't work).
{CODE}
Sub Formula_Replace()
'
' Formula_Replace Macro
'

'
Selection.Copy
ActiveCell.Rows("1:1").EntireRow.Select
ActiveCell.Offset(0, 2).Range("A1").Activate
Selection.Replace What:="01 new client", Replacement:="SMITH, Robert" _
, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
:=False, ReplaceFormat:=False
ActiveCell.Offset(1, -2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Rows("1:1").EntireRow.Select
ActiveCell.Activate
Selection.Replace What:="01 new client", Replacement:="Jones, John", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
End Sub
{CODE}

**Andrew-R** · 10-16-2011, 06:59 PM

Your code tags need square brackets, not curly ones.

Did you try putting the formula I gave you into cell D3? It does exactly what you're trying to do with your macro, but it's much more straightforward.