Replace function doesn't appear to be working VBA excel 2010

Vote count:

0

I am trying to use the replace function in VBA, but in the end my code always ends up returning the original string that I am trying to run the find and replace on. This is the code I have been trying

    Sub Change_mod_names_to_codes()

    Dim LRmodsdata As Long         'stands for Last row of modules input data
    Dim LRmodsSTAR                'Last row of the STAR module codes
    Dim idata As Long                 'first counter
    idata = 1
    Dim iMC As Long                'second counter
    iMC = 6
    Dim compareMS As String
    Dim CompareMC As String
    Dim Submc As String
    Application.ScreenUpdating = False
    With ActiveWorkbook
    Sheets("Data").Activate
    LRmodsdata = Cells(Rows.Count, "G").End(xlUp).Row
    Sheets("Modules").Activate
    LRmodsSTAR = Cells(Rows.Count, "B").End(xlUp).Row

    ' MsgBox LRmodsdata & ", " & LRmodsSTAR   - commented this out, just a message box to check

    End With

    Do While idata <= LRmodsdata
        idata = idata + 1
        compareMS = Sheets("Data").Cells(idata, 7).Value2
            Do While iMC <= LRmodsSTAR
            CompareMC = Sheets("Modules").Cells(iMC, 2).Value2
            Submc = CStr(Sheets("Modules").Cells(iMC, 1).Value2)
            compareMS = replace(compareMS, CompareMC, Submc)
            iMC = iMC + 1
            Loop

        Sheets("data").Cells(idata, 25).Value = compareMS
        Loop

    End Sub

Can anyone help me? First time posting to this forum but I have definitely tried to research before asking.

Cheers

asked 46 secs ago

Kaspi83

1

Replace function doesn't appear to be working VBA excel 2010