copy from a specific character and paste

[Sheela]

I am having trouble to do the following in a VBA macro.

Find cell value in last row and fist column
Copy the string starting from “:†(eg if the cell value is “abcd: ID†copy
only ID), in that cell
And paste that to the last column and second row in the same sheet

I know I can use the instr , right and len functions to do get the string
value I am looking for, but I am not able to figure out how to copy that and
paste into in terms of code.
Thank you so much in advance for your help

 

[B Lynn B]

Instead of copy/paste, store the value in a variable, then put it where you
want it. e.g...

Sub forSheela()

Dim myRng As Range, LastRow As Long, LastColumn As Long
Dim myTxt As String

Set myRng = ActiveSheet.UsedRange
LastRow = myRng.Row + myRng.Rows.Count - 1
LastColumn = myRng.Column + myRng.Columns.Count - 1
myTxt = Cells(LastRow, 1)
myTxt = Right(myTxt, Len(myTxt) - InStr(1, myTxt, ": ") - 1)
Cells(2, LastColumn - 1) = myTxt

End Sub

 

[Otto Moehrbach]

The following macro does what you want. Note that in your example, "abcd:
ID" has a space after the colon. This macro picks up that space. If you
don't want that space, change the line:
TheValue = Right(TheValue, Len(TheValue) - InStr(TheValue, ":"))
to
TheValue = Right(TheValue, Len(TheValue) - InStr(TheValue, ":")+1)
You can do this with a formula, but you asked for a macro. Otto
Sub CopyIt()
Dim TheValue As String
Dim Dest As Range
TheValue = Range("A" & Rows.Count).End(xlUp).Value
If InStr(TheValue, ":") > 0 Then
TheValue = Right(TheValue, Len(TheValue) - InStr(TheValue, ":"))
Set Dest = Cells(1, Columns.Count).End(xlToLeft).Offset(1)
Dest = TheValue
End If
End Sub

 

[Sheela]

That's perfect. Thank you very much

Instead of copy/paste, store the value in a variable, then put it where you
want it. e.g...

Sub forSheela()

Dim myRng As Range, LastRow As Long, LastColumn As Long
Dim myTxt As String

Set myRng = ActiveSheet.UsedRange
LastRow = myRng.Row + myRng.Rows.Count - 1
LastColumn = myRng.Column + myRng.Columns.Count - 1
myTxt = Cells(LastRow, 1)
myTxt = Right(myTxt, Len(myTxt) - InStr(1, myTxt, ": ") - 1)
Cells(2, LastColumn - 1) = myTxt

End Sub