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

--
rochelle
-----------------------------------------------------------------------
rochelles's Profile: http://www.excelforum.com/member.php...fo&userid=2780
View this thread: http://www.excelforum.com/showthread.php?threadid=47308

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

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

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