CODEWITHSUNDEEP
vbaexcel
heaton124
heaton124

Reputation: 79

VBA Cells.Replace changes the entire spreadsheet

SETUP:

Using ACCESS 2010 VBA to create sheets in Excel. Below issue is already working on an exported excel file;

QUESTION:

I'm having the below code (also tried varieties of it), but sadly it's changing every "-" for "_" in the entire spreadsheet instead only in the cells mentioned. I have no clue why is it happening.

For i = 2 To k
If Cells(i, 15).Value Like "*" & "-" & "*" & ":" & "*" Then
Cells(i, 15).Replace What:="-", Replacement:="_" - changes all
Cells(i, 15).Replace What:=":", Replacement:="." - changes all
End If
Next i

Highly appreciate any help. Thanks in advance!

Upvotes: 0

Views: 3053

Answers (3)

David Witteried
David Witteried

Reputation: 1

I have encountered the same issue. I was building a large array function with MS Access and due to limitations of the VBA rang.arraryfunction = "long expression > 255 char" not being possible, I used place keepers in place of the long expression, then the range.replace method to change the place keepers for the actual equations. At some point I discovered that the place keepers occured in other locations in the worksheet, and were getting replaced too. If the range is one cell in size, the entire worksheet gets searched and replaced, regardless of the range address. If the range is multiple cells, the changes are confined to the range address. The same code run from within Excel works as expected (i.e. only the "single" addressed cell gets updated). Very frustrating.

Upvotes: 0

EEM
EEM

Reputation: 6659

Op’s code seems like a mix of the Replace function and the Range.Replace. method.

Replace Function

Returns: a string in which a specified substring has been replaced with another substring a specified number of times.

Syntax: Replace( expression, find, replace, [start], [count] ,[compare] )

.

Range.Replace Method (Excel)

Returns: a Boolean indicating characters in cells within the specified range. Using this method doesn't change either the selection or the active cell.

Syntax: Range.Replace( What, Replacement, [LookAt], [SearchOrder], [MatchCase], [MatchByte], [SearchFormat], [ReplaceFormat] )

While the Replace function would need to be applied Cell by Cell, the Range.Replace method can be applied to an entire range. However when the Range.Replace method is applied to a single cell the replacements are performed over the entire worksheet.

... and that's what these lines are doing:

Cells(i, 15).Replace What:="-", Replacement:="_"
Cells(i, 15).Replace What:=":", Replacement:="."

The Range.Replace method does not need to be applied cell by cell neither to validate first if the string to be replaced is present (i.e. What), instead apply it directly to the entire range. Try this code:

With Range(Cells(2, 15), Cells(k, 15))  'change as required
    .Replace What:="-", Replacement:="_", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    .Replace What:=":", Replacement:=".", LookAt:=xlPart
End With

Upvotes: 1

SJR
SJR

Reputation: 23081

Not sure why that's not working (I can't replicate it). Can you see if this works?

For i = 2 To k
    If Cells(i, 15).Value Like "*" & "-" & "*" & ":" & "*" Then
        Cells(i, 15) = Replace(Cells(i, 15), "-", "_")
        Cells(i, 15) = Replace(Cells(i, 15), ":", ".")
    End If
Next i

Upvotes: 1

Related Questions