Mismatch Error w/ REPLACE Function

[bruppert]

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?

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

 

[GlennUK]

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:

Part	Description
expression	Required. String expression containing substring to replace.
find	Required. Substring being searched for.
replace	Required. Replacement substring.
start	Optional. Start position for the substring of expression to be searched and returned. If omitted, 1 is assumed.
count	Optional. Number of substring substitutions to perform. If omitted, the default value is -1, which means, make all possible substitutions.
compare	Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values.

 

[GWteB]

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.

 

[bruppert]

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:

Part	Description
expression	Required. String expression containing substring to replace.
find	Required. Substring being searched for.
replace	Required. Replacement substring.
start	Optional. Start position for the substring of expression to be searched and returned. If omitted, 1 is assumed.
count	Optional. Number of substring substitutions to perform. If omitted, the default value is -1, which means, make all possible substitutions.
compare	Optional. 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".

 

[bruppert]

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.

 

[mikerickson]

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

CurrentLine = Replace(CurrentLine, 122, "E", 1)

 

[GWteB]

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?

 

[GWteB]

@mikerickson I need a pair of -OO-

 

[GlennUK]

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:

CurrentLine = Left(CurrentLine, 121) &  "E" & Mid(Currentline,123,75)

 

[bruppert]

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:

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.