Excel 2007 Find & Replace VBA FALSE Result

[Randy11]

When I run this code it replaces TEXT with FALSE.
Am trying to replace TEMP with the Concatenate text from Column 2, 3, a Space " " & Column 4. (do not want the code to place this in the cell in the form of a formula, would simply like the Concatenate text in the cell. There will be several replacements required in this range. i.e TEMP 3 4 or 5 times in the range. Want to replace all.
Assistance appreciated

Sub Find_Replace2()
Dim SFind As String
Dim SReplace As String

Sheets("Sheet1").activate
SFind = "TEMP"
SReplace = FormulaR1C1 = "RC[2]&RC[3]&" "&RC[4]"

Range("A1:A500").Replace _
What:=SFind, Replacement:=SReplace, _
LookAt:=xlWhole, MatchCase:=True

End Sub

 

[SkipVought]

hi,

What is this supposed to be?

SReplace = FormulaR1C1 = "RC[2]&RC[3]&" "&RC[4]"

In Tools > Options check the Require Variable Declaration box.

In VBA the syntax

a = b = c

is not a valid syntax. You can say...

a = c
b = c

I think that you might want...

SReplace = "=RC[2]&RC[3]&" "&RC[4]"

But I'm curious why you would do a replace like this to begin with.

Do you have MULTIPLE COLUMNS with data to replace with formulas?

Do you have SPORATIC VALUES in a column to replace? In other words DIFFERENT FORMULAS or FORMULAS and VALUES in the same column?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

When I run this code it replaces TEXT with FALSE.

Run this test to discover WHY you got FALSE...
[tt]
Sub test()
Dim a, b, c
c = "what"
a = b = c

Debug.Print a, b, c

a = c
b = c


Debug.Print a, b, c
End Sub
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

Original sample code used
Sub Find_Replace2()
Dim SFind As String
Dim SReplace As String

SFind = "Macrosoft Excel"
SReplace = "Microsoft Excel"

Range("A1:A500").Replace _
What:=SFind, Replacement:=SReplace, _
LookAt:=xlWhole, MatchCase:=False
End Sub

In SReplace line need to replace the defined Text "Miscrosoft Excel" replacement with the Concatenate text from same line in columns 3, 4 & 5. i.e. Concatenate(3,4," ",5)

Have tried a number of ways to do this. Some have not worked at all, a couple are placing a FALSE in the replacement cell? Latest attempt line creating FALSE below.

SReplace = ActiveCell.Offset(0, 1).FormulaR1C1 = _
ActiveCell.Offset(0, 3) & ActiveCell.Offset(0, 4) & " " & ActiveCell.Offset(0, 5)

 

[SkipVought]

What you have described is not a FIND & REPLACE candidate: ie not the right tool.

  dim r as range
  for each r in Range("A1:A500")
    with r
      .value = .Offset(0, 1).Value & .Offset(0, 3).value & .Offset(0, 4).value & " " & .Offset(0, 5).value
    end with
  next

Just another observation comment/question: Why does your data begin in ROW 1, for a list or table???

IMNSHO, EVERY list and EVERY table should have heading(s) in row 1! SOP!!


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

Data was just imported form an outside source. Once of the next steps or can be a prior step can put the Headings in.

On the code you have provided, it is appreciated but do not know how to apply it. Am looking at some other material that may assist me in this regard.

 

[SkipVought]

Paste in a module and RUN...

Sub test()
    Dim r As Range
    For Each r In Range("A1:A500")
      With r
        .Value = .Offset(0, 1).Value & .Offset(0, 3).Value & .Offset(0, 4).Value & " " & .Offset(0, 5).Value
      End With
    Next
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

Can I take it you can replace r with any text to replace?

Ran code exactly as posted.
Run time error 13 Type Mismatch

.Value = .Offset(0, 1).Value & .Offset(0, 3).Value & .Offset(0, 4).Value & " " & .Offset(0, 5).Value

 

[SkipVought]

Can I take it you can replace r with any text to replace?

YES, the column A value is replaced.

At the point of error, what is the EXACT VALUES of
[tt]
r.Offset(0, 1).Value
r.Offset(0, 3).Value
r.Offset(0, 4).Value
r.Offset(0, 5).Value
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Hoaokapohaku]

This is a hybrid between your original code using find and replace, and Skip's solution that loops through the range. This one loops too, but loops only through the cells that meet the criteria. In other words, if you have only 4 instances of "TEMP", it will loop only 4 times rather than 500 times, so it should be faster even though it has more lines of code. This approach also allows your macro to keep working if ever your data extends beyond row 500 since it finds the criteria in the entire column.

Also, the worksheet function CountIf is not case insensitive, but your original replace code is case sensitive. This will cause the loop run one more time than you have instances of "TEMP" when you have other instances of "temp" that are not all upper case. This causes an error which is handled in the code to end the macro.

Sub ReplaceTemp()
Dim TEMPCount As Integer
Dim TEMPRange As Range
Dim i As Integer
Dim iCount As Integer
    iCount = WorksheetFunction.CountIf(Columns(1), "TEMP")
    Set TEMPRange = Range("A1")
    For i = 1 To iCount
        Set TEMPRange = Columns(1).Find(What:="TEMP", After:=TEMPRange, _
              LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
              SearchDirection:=xlNext, MatchCase:=True)
        On Error GoTo Done
        TEMPRange.Value = TEMPRange.Offset(0, 1).Value & TEMPRange.Offset(0, 3) _
              .Value & TEMPRange.Offset(0, 4).Value & " " & TEMPRange.Offset(0, 5).Value
    Next i
Done:
End Sub

 

[Hoaokapohaku]

On second thought, I think your original concept using "replace" was best, and you should skip any kind of looping altogether. The following code works, but you must name the ranges for the entire columns where the text is that you want to concatenate. For illustration purposes, I just used "Text1," "Text2," etc. Doing this eliminates the problem with relative references, which was the primary problem with your original code.

Sub Find_Replace3()
Dim SFind As String
Dim SReplace

    SFind = "TEMP"
    SReplace = "text1" & " " & "text2" & " " & "text3"
    Sheet1.Columns(1).Replace What:=SFind, Replacement:=SReplace, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False

End Sub