Hi There,
How do I get the following cell functions in to VBA code?
=LEFT(A5,3)
=RIGHT(A5,1)
many thanks
Jon
Hi There,
How do I get the following cell functions in to VBA code?
=LEFT(A5,3)
=RIGHT(A5,1)
many thanks
Jon
MsgBox Left([A5], 3) MsgBox Right(Range("A5"), 1)
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
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
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:
Of course you can always make A6 contain the formula with:Range("A6").Value = Left(Range("A5"), 3)
Range("A6").Formula = "=Left(A5,3)"
Sounds like you want a worksheet change event. Was it to add a Value or a Formula?
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
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Thanks for your help guys
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)Dim cel As Range For Each cel In Range("A5:A22") cel.Offset(, 5) = Split(cel, " ")(0) cel.Offset(, 6) = Split(cel, " ")(1) Next
thanks again
Jon
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]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Works like a charm
thanks for your help
regards
Jon