VBA Range.Replace Method not working

Question

This is my code:

Dim ValDate As String
Dim FName As String
Dim rows As Integer
Dim col As Integer

ValDate = InputBox("Enter cell range:")

If ValDate = "" Then
    Exit Sub
End If

r = 1
c = 20
rows = Range(ValDate).Row
col = Range(ValDate).Column


FName = InputBox("Enter name:")

Range("B2:G22").Select
Selection.Copy
Range(ValDate).Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Range(ValDate).Offset(0, 1) = FName


With Range(Cells(rows, col), Cells(rows + c, col)).Offset(1, 0)
.Replace "ABCDE", FName
End With
With Range(Cells(rows, col), Cells(rows + c, col)).Offset(1, 1)
.Replace "ABCDE", FName
End With
With Range(Cells(rows, col), Cells(rows + c, col)).Offset(1, 3)
.Replace "ABCDE", FName
End With

Problem is when macro trying to Replace word. Nothing happen. Yesterday I was working on this and all was good. Today I tried to continue work on this macro and run for test. Nothing. Can someone explain why? I haven't changed anything. No errors when I run macro. Just reading code and skips it.

PASUMPON V N · Accepted Answer · 2016-03-23 10:25:39Z

2

Try to use Replace() Function instead of .Replace

For Example

Dim Rng as Range

Set Rng = Range(Cells(rows, col), Cells(rows + c, col)).Offset(1, 0)

Rng.Value = Replace(Rng.Value, "ABCDE", FName)

answered Mar 23, 2016 at 10:25

PASUMPON V N

1,1862 gold badges10 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MrDominikku Over a year ago

Error 13: Type mismatch when trying execute Replace function

Ken Ingram Over a year ago

This worked perfectly for me: associated_char = Chr(151) For Each cells In Selection cells.Value = Replace(cells.Value, associated_char, " - ") Next Change all the emdashes to hyphens

Ryszard Jędraszyk · Accepted Answer · 2016-03-23 10:44:24Z

1

I could give you a fish, but I will hand you a fishing pole instead.

When you ever happen to have any similar problems, navigate to your Developer tab and select Visual Basic. Find the object containing your code, right click it, view code (or if the code is directly in your sheet, then just right click + view code on your sheet tab).

Remember to always add Option Explicit on top of your macro (before declaring variables) for easier debugging.

By repeatedly pressing F8 in your code, you execute your macro line by line and unless screen updating is false, you can preview current results of the macro on your sheet. You will also know at what point the macro doesn't behave as You would like and can narrow it down to part of the code which is causing problems.

edited Mar 23, 2016 at 10:44

answered Mar 23, 2016 at 10:36

Ryszard Jędraszyk

2,4414 gold badges26 silver badges56 bronze badges

1 Comment

MrDominikku Over a year ago

This is my macro created by me from the beginning so I need to use Visual Basic. Option Explicit is set up by default and I debugging my macro with F8 all the time to see if macro use good variable. So you comment is unrelevant here... No error and no problem with code. Just can't understand why macro SKIPS code with .Replace and do nothing.

Ryszard Jędraszyk · Accepted Answer · 2016-03-23 11:56:00Z

0

I hope this helps, even if you want to do things in a different way than I expect. The code replaces "ABCDE" text with your input FName (even if "ABCDE" is only a part of cell's content). All changes are applied to original data.

You must first specify sheet's name by editing: sheetname = "Sheet1"

Then specify data range by editing: v_data = .Range("b2:g" & thelastrow)

The last row will be automatically set as last row in your sheet containing any data.

If you want any modifications to code's behaviour, let me know.

Dim FName As String, sheetname As String
Dim thelastrow As Integer, dataRow As Integer, dataCol As Integer
Dim v_data As Variant

Application.ScreenUpdating = False

sheetname = "Sheet1"

With ThisWorkbook.Worksheets(sheetname)
    thelastrow = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v_data = .Range("b2:g" & thelastrow)

    FName = InputBox("Enter name:")

    For dataRow = LBound(v_data) To UBound(v_data)
        For dataCol = LBound(v_data, 2) To UBound(v_data, 2)
            If InStr(1, v_data(dataRow, dataCol), "ABCDE") <> 0 Then
                v_data(dataRow, dataCol) = Replace(v_data(dataRow, dataCol), "ABCDE", FName)
            End If
        Next dataCol
    Next dataRow

    .Range("b2:g" & thelastrow) = v_data

End With

Application.ScreenUpdating = True

answered Mar 23, 2016 at 11:56

Ryszard Jędraszyk

2,4414 gold badges26 silver badges56 bronze badges

1 Comment

MrDominikku Over a year ago

Thank you for you comment. I've just solved it. Simple thing:

With Range(Cells(rows, col), Cells(rows + c, col)).Offset(1, 0) .Replace What:="ABCDE", Replacement:=FName, LookAt:=xlPart, MatchCase:=True

. Now works every time :) I think macro couldn't find word in range so it skips it or do nothing.

J. Chomel · Accepted Answer · 2016-03-23 09:54:19Z

0

In your code, specify which Workbook / Sheet you work on:

this_workbook = "myWorkBook.xlsm"
mySheet = "theSheet"
' then specify the range like this   
yourRange = "[" & this_workbook & "]" & mySheet & "!B2:G22"

answered Mar 23, 2016 at 9:54

J. Chomel

8,44215 gold badges44 silver badges72 bronze badges

Comments

Chinwobble · Accepted Answer · 2016-03-23 10:09:18Z

0

You can also specify the range you want to work with using this code:

dim ws as worksheet
set ws = ThisWorkBook.sheets(1) 'set the first tab of this work book

then when you want to interact with you a cell you use:

ws.Range("B2:G22").select

Also if you want to paste a value to a cell then you do not need to "select" it unless you want to show the user. Your code:

Range("B2:G22").Select
Selection.Copy
Range(ValDate).Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

can be changed to:

Range("B2:G22").Copy
Range(ValDate).PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

answered Mar 23, 2016 at 10:09

Chinwobble

6631 gold badge9 silver badges15 bronze badges

1 Comment

MrDominikku Over a year ago

Still the same, all range are ws.Range. Maybe you misunderstood me because I only copy ws.Range("B2:G22").Select. I work on I2 cell. Let me explain: rows=2 col = 9 (I2 = Cells(2,9)) so With ws.Range(Cells(2,9),Cells(22,9)).Offset(1,0) = ws.Range(I2:I22) . I want macro to replace word "ABCDE" in range I2:I22 with word from Inputbox.

Collectives™ on Stack Overflow

VBA Range.Replace Method not working

5 Answers 5

2 Comments

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related