Cut and paste using a macro

[rochelles]

I have an excel spreadsheet with 1000's of rows. One of the columns is file path, I need to copy the file extension and paste it into a blank column. I was able to create a macro that will do it for one cell, but I need it to repeat throughout the entire worksheet. Any suggestions???

[Otto Moehrbach]

Within your macro you need to define (Set) the range of one column, say the
column that has the path. I'll use Column A.
Sub CopyData()
Dim TheRange As Range
Dim i As Range
Set TheRange = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In TheRange
'Your code
Next i
End Sub
This macro loops through all the cells in Column A from A1 to the last
occupied cell in Column A.
The cell that your code will operate on each time is "i". So if you want to
copy the extension in a full path you would write something like:
Right(i, 3).Copy
If you want to copy and paste, say paste 3 columns over from Column A you
would write:
Right(i, 3).Copy i.Offset(,3)
Note that there has to be a space after the word Copy.
HTH Otto
"rochelles" <[email protected]> wrote
in message news:[email protected]...
>
> I have an excel spreadsheet with 1000's of rows. One of the columns is
> file path, I need to copy the file extension and paste it into a blank
> column. I was able to create a macro that will do it for one cell, but
> I need it to repeat throughout the entire worksheet. Any suggestions???
>
>
>
> --
> rochelles
> ------------------------------------------------------------------------
> rochelles's Profile:
> http://www.excelforum.com/member.php...o&userid=27802
> View this thread: http://www.excelforum.com/showthread...hreadid=473089
>

[Dave Peterson]

One way is to use a formula.

With the path in A1, I used this formula in B1:
=MID(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="."),
ROW(INDIRECT("1:"&LEN(A1))))+1,255)

(all one cell)



rochelles wrote:
>
> I have an excel spreadsheet with 1000's of rows. One of the columns is
> file path, I need to copy the file extension and paste it into a blank
> column. I was able to create a macro that will do it for one cell, but
> I need it to repeat throughout the entire worksheet. Any suggestions???
>
>
> --
> rochelles
> ------------------------------------------------------------------------
> rochelles's Profile: http://www.excelforum.com/member.php...o&userid=27802
> View this thread: http://www.excelforum.com/showthread...hreadid=473089

--

Dave Peterson