move and modify exiting character in cell
Hi Steve,
Thanks for your help !!!
tlee
Part 1 - modify the strings in column A.
SelectColAValues selects EVERYTHING in column A
ProcessChange pulls all values into and array and runs the function to
strip the first 'word'
Note : xtractAftrDelim modified to pass the string from the array ByVal
Sub runit()
Call SelectColAValues
Call ProcessChange
End Sub
Private Function xtractAftrDelim(ByVal sValue As String, Optional
sSeparator = " ") As String
' extracts the end of the string after the first separator
' if no other separator is specified, default separator is a blank space
Dim iPos
iPos = InStr(sValue, sSeparator)
xtractAftrDelim = Right(sValue, Len(sValue) - iPos)
End Function
Private Sub SelectColAValues()
Application.Goto Reference:="R65536C1"
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select
End Sub
Private Sub ProcessChange()
Dim buf As Variant
Dim i As Long
'Get values as an array
buf = Selection
'loop through the array and modify each element
For i = LBound(buf, 1) To UBound(buf, 1)
buf(i, 1) = xtractAftrDelim(buf(i, 1))
Next
'Put the array back into the worksheet
Selection = buf
End Sub
Part 2 add a formula to column C to combine column A and B values
Sub AddFormulaToColC()
SelectColAValues' use the same sub as above to select
Selection.Offset(, 2).FormulaR1C1 = "=RC[-2]& "" "" & RC[-1]"
End Sub
--
Steve
"tlee" wrote in message
...
Thanks all,
if I want to loop all cells in columns, how can I code it?
Thanks for in advance.
Tlee
Function xtractAftrDelim(sValue As String, Optional sSeparator = " ") As
String
' extracts the end of the string after the first separator
' if no other separator is specified, default separator is a blank
space
Dim iPos
iPos = InStr(sValue, sSeparator)
xtractAftrDelim = Right(sValue, Len(sValue) - iPos)
End Function
Sub test()
Range("A1") = xtractAftrDelim(Range("A1"))
End Sub
Sub CombineCells(rng)
'combine values of cell with one cell to the right in 2nd cell to right
Range(rng).Offset(, 2) = Range(rng).Value & " " & Range(rng).Offset(,
1).Value
End Sub
Sub test2()
CombineCells ("A1")
End Sub
--
Steve
"tlee" wrote in message
...
Hi,
Could anyone help how to use Macro to move and modify character in cell
for the below situation:
Case 1) String "7 units here" at cell A1 on column A. Modify to adding
some spaces at the cell A1. As the result, the content of A1 become " 7
units here.
Case 2) String "7 units here" at cell A1 in column A. String "There
are" at Cell B2 in columnB. How to move and combine them into "There
are 7 units here" store at Cell C1 in columnC.
Thanks for in advance.
Tlee
|