move and modify exiting character in cell

[tlee]

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

 

[muddan madhu]

sub test
Range("C1").Value = Range("B1") & Space(1) & Range("A1")
end sub


u can use function also in Cell C1 put this formula
= B1 & " " & A1

 

[AltaEgo]

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

 

[tlee]

Thanks all,

if I want to loop all cells in columns, how can I code it?

Thanks for in advance.

Tlee

 

[AltaEgo]

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

 

[tlee]

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

Thanks all,

if I want to loop all cells in columns, how can I code it?

Thanks for in advance.

Tlee