CODEWITHSUNDEEP
vbaexcel
Eric King
Eric King

Reputation: 103

Trim a string while it is being copied to new cell

I am currently using this to get the unique values from a column. I am trying to modify that unique value and trim out characters before it pastes to the destination.

Dim Cl As Range

    With CreateObject("scripting.dictionary")
        For Each Cl In wbFrom.Sheets("Sheet0").Range("X9", Range("X" & Rows.Count).End(xlUp))
            If Not .exists(Cl.Value) Then
            .Add Cl.Value, Nothing
            'Cl.Value = Left(Cl, Len(Cl) - 5)
            End If
        Next Cl
        wbTo.Sheets("Sheet1").Range("F12").Resize(.Count).Value = Application.Transpose(.keys)
    End With

I am not sure where or how to implement the line: Cl.Value = Left(Cl, Len(Cl) - 7) or if this is even the best route.

The source data is '36 Months' and I am trying to trim out anything except the numbers themselves, resulting in just '36' being paste into my field. All of the values will be in an 'XX Months' format, and should always be 2 digits.

EDIT: I also just noticed that the code above pastes into cell F13 instead of F12 - Any ideas why it is adding a row before being paste? I suspect the 'transpose' aspect, but can't see any other reason.

Upvotes: 0

Views: 62

Answers (2)

Eric King
Eric King

Reputation: 103

This is a solution for those with the same issue, wanted to post it here. 

Dim Cl As Range
With CreateObject("scripting.dictionary")
    For Each Cl In wbFrom.Sheets("Sheet0").Range("X9", Range("X" & Rows.Count).End(xlUp))

        Cl.Value = Left$(Cl.Value, 2)  '<--- Magic spot

        If Not .exists(Cl.Value) Then
        .Add Cl.Value, Nothing
        End If
    Next Cl
    wbTo.Sheets("Sheet1").Range("D11").Resize(.Count).Value = Application.Transpose(.keys)
End With

This also seemed to work for me: 

           Cla.Value = Replace(Cla.Value, " Months", "")

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Untested:

For Each Cl In wbFrom.Sheets("Sheet0").Range("X9", Range("X" & Rows.Count).End(xlUp))
            v = Left(Cl, Len(Cl) - 5)
            If Not .exists(v) Then
                .Add v, Nothing
            End If
Next Cl

Upvotes: 1

Related Questions