Mismatch Error w/ REPLACE Function

bruppert

New Member
Joined
May 3, 2019
Messages
4
Hello,

I am working on a macro to edit a fixed width text file. What I want my macro to do is loop through each line of the text file (copy and pasted into column A) and replace a value IF the value of "RecordType" = 1.

However, the line with the Replace function returns a mismatch error. Any ideas?

VBA Code:
Sub Test1()

Dim RecordType As Integer
Dim CurrentLine As String



FinalRow = Cells(65536, 1).End(xlUp).Row

For i = 1 To FinalRow

    CurrentLine = Cells(i, 1).Value
    RecordType = Mid(CurrentLine, 41, 1)
    
    If RecordType = 1 Then
        CurrentLine = Replace(CurrentLine, 122, 1, "E")
    End If
    
    Cells(i, 1).Value = CurrentLine

    
Next i

        

        

End Sub
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
What exactly are you trying to do with the Replace? Here are the syntax rules for it, for reference:
Syntax
Replace(expression, find, replace, [ start, [ count, [ compare ]]])
The Replace function syntax has these named arguments:
PartDescription
expressionRequired. String expression containing substring to replace.
findRequired. Substring being searched for.
replaceRequired. Replacement substring.
startOptional. Start position for the substring of expression to be searched and returned. If omitted, 1 is assumed.
countOptional. Number of substring substitutions to perform. If omitted, the default value is -1, which means, make all possible substitutions.
compareOptional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values.
 
Upvote 0
Hi bruppert,

"Value" could be anything while "CurrentLine" is declared as String.
I suggest changing the first line in the For-Next loop (CurrentLine = Cells(i, 1).Value):
Replace .Value by .Text

When you step trough your code with F8 in debug mode you can follow the behaviour of the CurrentLine variable.
Hopefully this is of some help.
 
Upvote 0
What exactly are you trying to do with the Replace? Here are the syntax rules for it, for reference:
Syntax
Replace(expression, find, replace, [ start, [ count, [ compare ]]])
The Replace function syntax has these named arguments:
PartDescription
expressionRequired. String expression containing substring to replace.
findRequired. Substring being searched for.
replaceRequired. Replacement substring.
startOptional. Start position for the substring of expression to be searched and returned. If omitted, 1 is assumed.
countOptional. Number of substring substitutions to perform. If omitted, the default value is -1, which means, make all possible substitutions.
compareOptional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values.
Hi Glenn,

I am trying to replace position 123 in "CurrentLine" with "E".
 
Upvote 0
Hi bruppert,

"Value" could be anything while "CurrentLine" is declared as String.
I suggest changing the first line in the For-Next loop (CurrentLine = Cells(i, 1).Value):
Replace .Value by .Text

When you step trough your code with F8 in debug mode you can follow the behaviour of the CurrentLine variable.
Hopefully this is of some help.
Hi GWteB,

I tried this, returns the same mismatch error.
 
Upvote 0
The type mismatch is in the arguments of the Replace function.

The fourth argument is supposed to be data type Long, you have it as the string "E"

try
VBA Code:
CurrentLine = Replace(CurrentLine, 122, "E", 1)
 
Upvote 0
I am working on a macro to edit a fixed width text file.
So you can rely on the fact that position 122 in the CurrentLine exists?
Does the error already occur in the first run, or later on?
 
Upvote 0
Hi Glenn,

I am trying to replace position 123 in "CurrentLine" with "E".
that's not the way REPLACE works ... it replaces a string with another string ... it does not overwrite at a certain position ... this is clear from the Syntax rules I posted.

Generate the required CurrentLine using LEFT and MID and concatenate, like:
VBA Code:
CurrentLine = Left(CurrentLine, 121) &  "E" & Mid(Currentline,123,75)
 
Upvote 0
that's not the way REPLACE works ... it replaces a string with another string ... it does not overwrite at a certain position ... this is clear from the Syntax rules I posted.

Generate the required CurrentLine using LEFT and MID and concatenate, like:
VBA Code:
CurrentLine = Left(CurrentLine, 121) &  "E" & Mid(Currentline,123,75)
Hi Glenn,

Whoops. I was using the worksheet version of Replace() syntax. Thanks for the help.
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top