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

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


"Sheela" wrote:

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

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" wrote in message
...
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

That's perfect. Thank you very much
"B Lynn B" wrote:

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


"Sheela" wrote:

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