Solved: TRIM in VBA

[Blackie50]

Hi There,

How do I get the following cell functions in to VBA code?

=LEFT(A5,3)
=RIGHT(A5,1)

many thanks
Jon

[Kenneth Hobs]

MsgBox Left([A5], 3)
MsgBox Right(Range("A5"), 1)

[Blackie50]

Sorry - my fault

e.g cell A5 = CMF C

= LEFT(A5,3) returns CMF in the cell
= RIGHT(A5,1) returns C in the cell

I need to do this in VBA as part of a subroutine

thanks
Jon

[Blackie50]

and if this is possible can the following also be done

Put it in a loop so that for every time there is an occurrence of 3 letters/space/letter in column A starting at A5 (as follows)

CMF C
ETF C
IGF C

the results in cell F5 & G5 are the formula results

thanks
Jon

[Kenneth Hobs]

That is just what the code does. I gave you two examples to get the value of A5. What you want to do with that is up to you.

Guessing that you might to do something else with the code like putting the result into another cell like A6:

Range("A6").Value = Left(Range("A5"), 3)

Of course you can always make A6 contain the formula with:

Range("A6").Formula = "=Left(A5,3)"

[Kenneth Hobs]

Sounds like you want a worksheet change event. Was it to add a Value or a Formula?

[Kenneth Hobs]

For the worksheet change event to put values, right click the sheet's tab, View Code, and paste.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, rr As Range, cell As Range
    Set rr = Range("A5", Range("A" & Rows.Count))
    Set r = Intersect(Target, rr)
    If r Is Nothing Then Exit Sub
    If r.Row < 5 Then Exit Sub
    Application.EnableEvents = False
    For Each cell In r
        If cell.Column = 1 And cell.Row >= 5 Then
            If Len(cell.Value) = 5 Then
                Range("F" & cell.Row).Value = Left(cell.Value, 3)
                Range("G" & cell.Row).Value = Right(cell.Value, 1)
            Else
                Range("F" & cell.Row).Value = ""
                Range("G" & cell.Row).Value = ""
            End If
        End If
    Next cell
    Application.EnableEvents = True
End Sub

[mdmackillop]

Where the data is more variable, you can use the Split function using Space as a delimiter
eg

    Dim cel As Range
    For Each cel In Range("A5:A7")
        cel.Offset(, 5) = Split(cel, " ")(0)
        cel.Offset(, 6) = Split(cel, " ")(1)
    Next

[Blackie50]

Thanks for your help guys

Dim cel As Range
    For Each cel In Range("A5:A22")
        cel.Offset(, 5) = Split(cel, " ")(0)
        cel.Offset(, 6) = Split(cel, " ")(1)
    Next

works fine because the data is variable - always starts at A5 but the number of rows used will be variable - any help with the code for identifying number of used rows please ( if I put the end of the range higher than used range it fails)

thanks again
Jon

[mdmackillop]

This will give you from A5 to the last cell
[VBA]Dim cel As Range
For Each cel In Range(Cells(5, 1), Cells(Rows.Count, 1).End(xlUp))
If InStr(cel, " ") > 0 Then
cel.Offset(, 5) = Split(cel, " ")(0)
cel.Offset(, 6) = Split(cel, " ")(1)
End If
Next
[/VBA]

[Blackie50]

Works like a charm

thanks for your help

regards

Jon