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" <(E-Mail Removed)> wrote in message
news:699B74B2-05BD-4B92-A5E3-(E-Mail Removed)...
>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
> >