VBA - command not affected

[Halos]

Dear All,

Please see below code which I use for copy information from sheet 1 to sheet 2 in different formats. There is command that if column "J" contains "CN" word then put column "G" number 5 if not leave as number 4. The problem is if column "J" contains "CN" word, it puts number 4 instead of 5. Could you please help where the mistake?

Option Explicit
Sub TransferData()
Dim rSrc As Range
Dim rStart As Range
Dim rDest As Range
Dim rC As Range
Dim lRow As Long, i As Long
    'Get start point and set variables...
    Worksheets("Invoice").Activate
    On Error Resume Next
    Set rStart = Application.InputBox("Select the cell containing the invoice number to start processing.", Type:=8)
    On Error GoTo 0
    If Not rStart Is Nothing Then
        Set rStart = Range(rStart.Offset(0, -2), Cells(Rows.Count, "A").End(xlUp).Offset(0, 11))
        Set rSrc = rStart.Columns(3).SpecialCells(xlCellTypeConstants).Cells
        Set rDest = Worksheets("UK").Range("A1").CurrentRegion.Offset(1, 0).Rows(Worksheets("UK").Range("A1").CurrentRegion.Rows.Count)
        Application.ScreenUpdating = False
        Worksheets("Invoice").Cells(Rows.Count, 1).End(xlUp).Offset(1, 2).Value = "End"
        Worksheets("UK").Activate
        For Each rC In rSrc
            If rC.Value = "End" Then Exit For
            'Set up main account detail...
            With rC.EntireRow
                .Columns("C").Copy rDest.Columns("J")
                .Columns("D").Copy rDest.Columns("H")
                .Columns("K").Copy rDest.Columns("E")
                .Columns("J").Copy rDest.Columns("C")
                .Columns("A").Copy rDest.Columns("D")
                rDest.Columns("A").Value = "SH"
                rDest.Columns("F").Value = 0
                If Mid(rDest.Columns("J").Value, Len(rDest.Columns("J").Value) - 2, 3) = "CN" Then
                    rDest.Columns("G").Value = 5
                Else
                    rDest.Columns("G").Value = 4
                End If
                If IsDate(rDest.Columns("H")) Then rDest.Columns("I") = rDest.Columns("H").Value + 30
            End With
            rDest.Columns("A").Offset(1).Value = "SV"
            rDest.Columns("E").Copy
            rDest.Columns("D").Offset(1).PasteSpecial
            Range(rC.Offset(1, -1), rC.End(xlDown).Offset(-1, -1)).Copy rDest.Columns("D").Offset(2)
            Range(rC.Offset(1, -1), rC.End(xlDown).Offset(-1, -1)).Copy rDest.Columns("K").Offset(2)
            Range(rC.Offset(1, -2), rC.End(xlDown).Offset(-1, -2)).Copy rDest.Columns("E").Offset(2)
            Application.DisplayAlerts = False
            Range(rDest.Columns("E").Offset(2), Columns("E").Rows(Rows.Count).End(xlUp)).TextToColumns _
                Destination:=rDest.Columns("E").Offset(2), DataType:=xlFixedWidth, _
                FieldInfo:=Array(Array(0, 9), Array(1, 1), Array(4, 1)), TrailingMinusNumbers:=True
            Range(rC.Offset(1, 8), rC.End(xlDown).Offset(-1, 8)).Copy rDest.Columns("G").Offset(2)
            Range(rC.Offset(1, 4), rC.End(xlDown).Offset(-1, 4)).Copy rDest.Columns("H").Offset(2)
            Range(rC.Offset(1, 5), rC.End(xlDown).Offset(-1, 5)).Copy rDest.Columns("I").Offset(2)
            Range(rC.Offset(1, 6), rC.End(xlDown).Offset(-1, 6)).Copy rDest.Columns("J").Offset(2)
            Range(rC.Offset(1, 7), rC.End(xlDown).Offset(-1, 7)).Copy rDest.Columns("C").Offset(2)
            lRow = Range(rC.Offset(1, -1), rC.End(xlDown).Offset(-1, -1)).Rows.Count
            rDest.Columns("A").Offset(2).Resize(lRow).Value = "SN"
            rDest.Columns("B").Resize(lRow + 2).Value = 106
            rDest.Columns("C").Offset(1).Value = rDest.Columns("C").Value
            rDest.Columns("E").Offset(1).Resize(, 2).Value = 0
            'Format info...
            With Range(rDest.Cells(1), Range("K" & Cells(Rows.Count, "K").End(xlUp).Row))
                .HorizontalAlignment = xlGeneral
                .VerticalAlignment = xlBottom
                .NumberFormat = "General"
                With .Font
                    .Name = "Arial"
                    .Size = 10
                    .Bold = False
                End With
            End With
            rDest.Columns("E").NumberFormat = "0.00"
            rDest.Columns("D").Offset(1).NumberFormat = "0.00"
            rDest.Columns("G").Offset(1).Resize(lRow + 1).NumberFormat = "0.00"
            rDest.Columns("H").Resize(, 2).NumberFormat = "dd\/mm\/yy"
            'Loop to next invoice...
            Set rDest = Worksheets("UK").Range("A1").CurrentRegion.Offset(1, 0).Rows(Worksheets("UK").Range("A1").CurrentRegion.Rows.Count)
        Next rC
        Range("A1").Select
        Application.ScreenUpdating = True
        MsgBox "Processing of invoices is complete.", vbInformation
    End If
End Sub

 

[njimack]

Could there be leading/trailing spaces or non-printable characters that are affecting your MID function?

 

[Halos]

Could there be leading/trailing spaces or non-printable characters that are affecting your MID function?

We put invoice number in this column - ABC008. When we have credit notes (CN) we need to put in the end "CN" word - ABC008 CN. As you can see command like below:

[LEFT][COLOR=#574123][FONT=monospace]If Mid(rDest.Columns("J").Value, Len(rDest.Columns("J").Value) - 2, 3) = "CN" Then
                    rDest.Columns("G").Value = 5
                Else
                    rDest.Columns("G").Value = 4
                End If
[/FONT][/COLOR][/LEFT]

 

[njimack]

Why is the 3rd argument in your MID function 3, if you're only testing for 2 characters?

 

[Halos]

Why is the 3rd argument in your MID function 3, if you're only testing for 2 characters?

If we change 3 to 2, it will not change. I can send excel spreadsheet to your email. I cannot send through MrExcel.

Thanks

 

[njimack]

Your attachment would probably be blocked by my firewall (I'm at work). 2 suggestions:
1 - you don't need to use MID, the below is more efficient:

If Right(rDest.Columns("J"), 2) = "CN" Then

2 - Enter the below at the start of your For/Next loop and then review the results using the immediate window (Ctrl+G)

Depug.Print Right(rDest.Columns("J"), 2)

 

[Halos]

Your attachment would probably be blocked by my firewall (I'm at work). 2 suggestions:
1 - you don't need to use MID, the below is more efficient:

If Right(rDest.Columns("J"), 2) = "CN" Then

2 - Enter the below at the start of your For/Next loop and then review the results using the immediate window (Ctrl+G)

Depug.Print Right(rDest.Columns("J"), 2)

Thank you! I Appreciate your help.