PDA

View Full Version : Solved: TRIM in VBA



Blackie50
01-31-2011, 08:24 AM
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
01-31-2011, 08:39 AM
MsgBox Left([A5], 3)
MsgBox Right(Range("A5"), 1)

Blackie50
01-31-2011, 08:43 AM
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
01-31-2011, 08:54 AM
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
01-31-2011, 08:55 AM
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
01-31-2011, 09:53 AM
Sounds like you want a worksheet change event. Was it to add a Value or a Formula?

Kenneth Hobs
01-31-2011, 10:50 AM
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
01-31-2011, 12:14 PM
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
02-01-2011, 05:15 AM
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
02-01-2011, 05:59 AM
This will give you from A5 to the last cell
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

Blackie50
02-01-2011, 06:53 AM
Works like a charm

thanks for your help

regards

Jon