Member Avatar for shena

Hi,

I created a macro in excel by importing XML file and then make amendments and then run to export as textfile. I have a column with specific characters like "A" or "C" as last characters of a string. I want to export that column strings after removing the last "A" or "C" character. My code is this:

'remove MATNR last character if it is "A" or "C"
    If Len(ws9B.Cells(iRow, 4).Text) <> 0 Then
    If Right(ws9B.Cells(iRow, 4).Text, 1) = "A" Or "C" Then
        ws9B.Cells(iRow, 4) = Left(ws9B.Cells(iRow, 4).Text, Len(ws9B.Cells(iRow, 4).Text) - 1)
    End If
    End If

But i keep on getting type mismatch error for the following code. Please anyone help on this problem. Thank you in advance for any help.

  • 2 Contributors
  • 3 Replies
  • 331 Views
  • 21 Hours Discussion Span
  • Latest Post Latest Post by shena
Member Avatar for dspnhn

amend ur Right(ws9B.Cells(iRow, 4).Text, 1) = "A" Or "C" to this

Right(ws9B.Cells(iRow, 4).Text, 1) = "A" Or Right(ws9B.Cells(iRow,4).text,1)= "C" Then

USE THIS

RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

SEE IF IT HELPS

Member Avatar for shena

Hi friend,
Thank you for the reply.
I managed to remove the last character from the string by using your first suggestion.

Right(ws9B.Cells(iRow, 4).Text, 1) = "A" Or Right(ws9B.Cells(iRow,4).text,1)= "C" Then

But now in the excel column also the last characters are removed. I want it to be like this; where in excel column the strings remain unchanged but the textfile format should be without the last specific characters. Any idea or advice on this.

Thank you very much:)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.