If Cell Contains Put in another Cell

[Dennis]

Hi, the following is all in Column A. what I'm trying to accomplish with VBA
is to search down column A and each time a cell contains "*c:\*" in the the
cell it puts the entire string of the cell to Column S and each cell down
until another cell in Column A contains *c:\*. So the first one is A1, it
finds the c:\ and puts that in S1 thru S7. then it finds the c:\ in
c:\Dennis\Docs\Fin and puts c:\Dennis\Docs\Fin in S8 and S9, then it finds
the c:\ in c:\Dennis\Internet\AIM and puts c:\Dennis\Internet\AIM in S10 thru
S21 and so on down until finds no more "*c:\*.

TIA, Dennis





c:\
06/06/2006 03:21 AM 23 AUTOEXEC.BAT
03/11/2006 07:12 AM 12,565,281 AVG7QT.DAT
05/09/2006 06:29 PM 209 boot.ini
02/26/2005 03:07 AM 0 CONFIG.SYS
05/09/2006 06:29 PM 209 Copy of boot.ini
06/04/2006 03:03 AM 27,262,976 VIRTPART.DAT
c:\Dennis\Docs\Fin
07/08/2006 03:54 PM 2,073,600 dlx.xls
c:\Dennis\Internet\AIM
11/14/2001 10:47 AM 28,672 Admin.ocm
11/14/2001 10:47 AM 28,672 advert.ocm
11/14/2001 10:47 AM 24,576 aim.exe
11/14/2001 10:47 AM 2,986 aim.odl
11/14/2001 10:47 AM 9,526 aim95.cnt
11/14/2001 10:47 AM 408,753 aim95.hlp
01/30/2001 06:04 PM 1,375 aimalert.gif
11/14/2001 10:47 AM 122,880 aimauto.exe
11/14/2001 10:47 AM 1,138,688 aimres.dll
11/14/2001 10:47 AM 36,864 aimtalk.dll
01/08/2002 12:06 PM 53,248 AlertUI.ocm

 

[Ken Johnson]

Hi Dennis,
does this do what you want?...

Public Sub Dennis()
Application.ScreenUpdating = False
Dim ilastrow As Long
ilastrow = Range("A" & Range("A:A").Rows.Count).End(xlUp).Row
Dim I As Long
Dim vaData As Variant
For I = 1 To ilastrow
With Range("A" & I)
If Not .Find("c:\") Is Nothing Then
vaData = .Value
End If
End With
Range("S" & I).Value = vaData
Next I
End Sub


Ken Johnson

 

[Dennis]

Hi Dennis,
does this do what you want?...

Public Sub Dennis()
Application.ScreenUpdating = False
Dim ilastrow As Long
ilastrow = Range("A" & Range("A:A").Rows.Count).End(xlUp).Row
Dim I As Long
Dim vaData As Variant
For I = 1 To ilastrow
With Range("A" & I)
If Not .Find("c:\") Is Nothing Then
vaData = .Value
End If
End With
Range("S" & I).Value = vaData
Next I
End Sub


Ken Johnson

==========================
Ken , I modded ("c:\") to ("*c:\*") and it works great!!!! thanks so much for
the help!

Dennis

 

[Ken Johnson]

Hi Dennis,

Your welcome.
Thanks for the feedback.

Ken Johnson