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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.