| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying Data inside an array
Hello People, THANK YOU for everyones help. Here is my question tonight: I have an array with a bunch of filenames in them, I want to modify the file names to get rid of the extension ".gif". FOR I = 1 TO .FOUNDFILES.COUNT SCANMAPS(I, 1) = DIR(.FOUNDFILES.ITEM(I)) NEXT I If it was in a Column I would do this: Columns("A:A").Select Selection.Replace What:=".GIF", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False How can I change the array without having to paste it in a row modify it and put it back into the array? Thank You in adavance -- icdoo ------------------------------------------------------------------------ icdoo's Profile: http://www.excelforum.com/member.php...o&userid=27342 View this thread: http://www.excelforum.com/showthread...hreadid=501422 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modifying Data inside an array
if xl2000 or later
FOR I = 1 TO .FOUNDFILES.COUNT SCANMAPS(I, 1) = Replace(DIR(.FOUNDFILES.ITEM(I)),.Gif,"") NEXT I -- Regards, Tom Ogilvy "icdoo" wrote in message ... Hello People, THANK YOU for everyones help. Here is my question tonight: I have an array with a bunch of filenames in them, I want to modify the file names to get rid of the extension ".gif". FOR I = 1 TO .FOUNDFILES.COUNT SCANMAPS(I, 1) = DIR(.FOUNDFILES.ITEM(I)) NEXT I If it was in a Column I would do this: Columns("A:A").Select Selection.Replace What:=".GIF", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False How can I change the array without having to paste it in a row modify it and put it back into the array? Thank You in adavance -- icdoo ------------------------------------------------------------------------ icdoo's Profile: http://www.excelforum.com/member.php...o&userid=27342 View this thread: http://www.excelforum.com/showthread...hreadid=501422 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modifying Data inside an array
Dim Scanmaps() As String ReDim Scanmaps(1 To .FoundFiles.Count, 1 To 2) As String 'Dim Scanmaplength As Integer For i = 1 To .FoundFiles.Count Scanmaps(i, 1) = Dir(.FoundFiles.Item(i)) Scanmaplength = Len(Scanmaps(i, 1)) - 4 Scanmaps(i, 1) = Left(Scanmaps(i, 1), Scanmaplength) Next i -- icdoo ------------------------------------------------------------------------ icdoo's Profile: http://www.excelforum.com/member.php...o&userid=27342 View this thread: http://www.excelforum.com/showthread...hreadid=501422 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modifying Data inside an array
Just a typo alert:
SCANMAPS(I, 1) = Replace(DIR(.FOUNDFILES.ITEM(I)),.Gif,"") becomes SCANMAPS(I, 1) = Replace(DIR(.FOUNDFILES.ITEM(I)),".Gif","") But after further review <bg, I think I'd use: SCANMAPS(I, 1) = Replace(DIR(.FOUNDFILES.ITEM(I)),".Gif","",1,-1,vbTextCompare) or SCANMAPS(I, 1) = Replace(ucase(DIR(.FOUNDFILES.ITEM(I))),".GIF","") (Replace is case sensitive unless it's told not to be.) Tom Ogilvy wrote: if xl2000 or later FOR I = 1 TO .FOUNDFILES.COUNT SCANMAPS(I, 1) = Replace(DIR(.FOUNDFILES.ITEM(I)),.Gif,"") NEXT I -- Regards, Tom Ogilvy "icdoo" wrote in message ... Hello People, THANK YOU for everyones help. Here is my question tonight: I have an array with a bunch of filenames in them, I want to modify the file names to get rid of the extension ".gif". FOR I = 1 TO .FOUNDFILES.COUNT SCANMAPS(I, 1) = DIR(.FOUNDFILES.ITEM(I)) NEXT I If it was in a Column I would do this: Columns("A:A").Select Selection.Replace What:=".GIF", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False How can I change the array without having to paste it in a row modify it and put it back into the array? Thank You in adavance -- icdoo ------------------------------------------------------------------------ icdoo's Profile: http://www.excelforum.com/member.php...o&userid=27342 View this thread: http://www.excelforum.com/showthread...hreadid=501422 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modifying Data inside an array
Thanks for the help, here is what I came up with, requires an extr variable and it assumes you want to get rid off all the extensions (Lucky thats what I needed): Dim Scanmaplength as Interger Scanmaplength = Len(Scanmaps(I, 1)) - 4 Scanmaps(I, 1) = Left(Scanmaps(I, 1), Scanmaplength -- icdo ----------------------------------------------------------------------- icdoo's Profile: http://www.excelforum.com/member.php...fo&userid=2734 View this thread: http://www.excelforum.com/showthread.php?threadid=50142 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modifying Data inside an array
Excellent catch, Dave. Slightly slower (probably unnoticeable) and
available in all versions without the need for extra arguments SCANMAPS(I, 1) = Application.Substitute(DIR(.FOUNDFILES.ITEM(I)),". Gif","") -- Regards, Tom Ogilvy' "Dave Peterson" wrote in message ... Just a typo alert: SCANMAPS(I, 1) = Replace(DIR(.FOUNDFILES.ITEM(I)),.Gif,"") becomes SCANMAPS(I, 1) = Replace(DIR(.FOUNDFILES.ITEM(I)),".Gif","") But after further review <bg, I think I'd use: SCANMAPS(I, 1) = Replace(DIR(.FOUNDFILES.ITEM(I)),".Gif","",1,-1,vbTextCompare) or SCANMAPS(I, 1) = Replace(ucase(DIR(.FOUNDFILES.ITEM(I))),".GIF","") (Replace is case sensitive unless it's told not to be.) Tom Ogilvy wrote: if xl2000 or later FOR I = 1 TO .FOUNDFILES.COUNT SCANMAPS(I, 1) = Replace(DIR(.FOUNDFILES.ITEM(I)),.Gif,"") NEXT I -- Regards, Tom Ogilvy "icdoo" wrote in message ... Hello People, THANK YOU for everyones help. Here is my question tonight: I have an array with a bunch of filenames in them, I want to modify the file names to get rid of the extension ".gif". FOR I = 1 TO .FOUNDFILES.COUNT SCANMAPS(I, 1) = DIR(.FOUNDFILES.ITEM(I)) NEXT I If it was in a Column I would do this: Columns("A:A").Select Selection.Replace What:=".GIF", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False How can I change the array without having to paste it in a row modify it and put it back into the array? Thank You in adavance -- icdoo ------------------------------------------------------------------------ icdoo's Profile: http://www.excelforum.com/member.php...o&userid=27342 View this thread: http://www.excelforum.com/showthread...hreadid=501422 -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modifying Data inside an array
Thanks for the lesson. Guess I stepped on it that time <g
-- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... Ah, but =substitute() is also case sensitive. I'd add that ucase() stuff and change ".Gif" to match (".GIF"). Tom Ogilvy wrote: Excellent catch, Dave. Slightly slower (probably unnoticeable) and available in all versions without the need for extra arguments SCANMAPS(I, 1) = Application.Substitute(DIR(.FOUNDFILES.ITEM(I)),". Gif","") -- Regards, Tom Ogilvy' "Dave Peterson" wrote in message ... Just a typo alert: SCANMAPS(I, 1) = Replace(DIR(.FOUNDFILES.ITEM(I)),.Gif,"") becomes SCANMAPS(I, 1) = Replace(DIR(.FOUNDFILES.ITEM(I)),".Gif","") But after further review <bg, I think I'd use: SCANMAPS(I, 1) = Replace(DIR(.FOUNDFILES.ITEM(I)),".Gif","",1,-1,vbTextCompare) or SCANMAPS(I, 1) = Replace(ucase(DIR(.FOUNDFILES.ITEM(I))),".GIF","") (Replace is case sensitive unless it's told not to be.) Tom Ogilvy wrote: if xl2000 or later FOR I = 1 TO .FOUNDFILES.COUNT SCANMAPS(I, 1) = Replace(DIR(.FOUNDFILES.ITEM(I)),.Gif,"") NEXT I -- Regards, Tom Ogilvy "icdoo" wrote in message ... Hello People, THANK YOU for everyones help. Here is my question tonight: I have an array with a bunch of filenames in them, I want to modify the file names to get rid of the extension ".gif". FOR I = 1 TO .FOUNDFILES.COUNT SCANMAPS(I, 1) = DIR(.FOUNDFILES.ITEM(I)) NEXT I If it was in a Column I would do this: Columns("A:A").Select Selection.Replace What:=".GIF", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False How can I change the array without having to paste it in a row modify it and put it back into the array? Thank You in adavance -- icdoo ------------------------------------------------------------------------ icdoo's Profile: http://www.excelforum.com/member.php...o&userid=27342 View this thread: http://www.excelforum.com/showthread...hreadid=501422 -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modifying Data inside an array
It doesn't really require the extra variable
Scanmaps(I,1) = Left(Scanmaps(I, 1),len(Scanmaps(I,1))-4) but it does assume you want to get rid of all extensions and that all the files have a 3 character extension -- Regards, Tom Ogilvy "icdoo" wrote in message ... Thanks for the help, here is what I came up with, requires an extra variable and it assumes you want to get rid off all the extensions, (Lucky thats what I needed): Dim Scanmaplength as Interger Scanmaplength = Len(Scanmaps(I, 1)) - 4 Scanmaps(I, 1) = Left(Scanmaps(I, 1), Scanmaplength) -- icdoo ------------------------------------------------------------------------ icdoo's Profile: http://www.excelforum.com/member.php...o&userid=27342 View this thread: http://www.excelforum.com/showthread...hreadid=501422 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modifying Data inside an array
Just trying to make it more palliative???
Tom Ogilvy wrote: Thanks for the lesson. Guess I stepped on it that time <g -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... Ah, but =substitute() is also case sensitive. I'd add that ucase() stuff and change ".Gif" to match (".GIF"). Tom Ogilvy wrote: Excellent catch, Dave. Slightly slower (probably unnoticeable) and available in all versions without the need for extra arguments SCANMAPS(I, 1) = Application.Substitute(DIR(.FOUNDFILES.ITEM(I)),". Gif","") -- Regards, Tom Ogilvy' "Dave Peterson" wrote in message ... Just a typo alert: SCANMAPS(I, 1) = Replace(DIR(.FOUNDFILES.ITEM(I)),.Gif,"") becomes SCANMAPS(I, 1) = Replace(DIR(.FOUNDFILES.ITEM(I)),".Gif","") But after further review <bg, I think I'd use: SCANMAPS(I, 1) = Replace(DIR(.FOUNDFILES.ITEM(I)),".Gif","",1,-1,vbTextCompare) or SCANMAPS(I, 1) = Replace(ucase(DIR(.FOUNDFILES.ITEM(I))),".GIF","") (Replace is case sensitive unless it's told not to be.) Tom Ogilvy wrote: if xl2000 or later FOR I = 1 TO .FOUNDFILES.COUNT SCANMAPS(I, 1) = Replace(DIR(.FOUNDFILES.ITEM(I)),.Gif,"") NEXT I -- Regards, Tom Ogilvy "icdoo" wrote in message ... Hello People, THANK YOU for everyones help. Here is my question tonight: I have an array with a bunch of filenames in them, I want to modify the file names to get rid of the extension ".gif". FOR I = 1 TO .FOUNDFILES.COUNT SCANMAPS(I, 1) = DIR(.FOUNDFILES.ITEM(I)) NEXT I If it was in a Column I would do this: Columns("A:A").Select Selection.Replace What:=".GIF", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False How can I change the array without having to paste it in a row modify it and put it back into the array? Thank You in adavance -- icdoo ------------------------------------------------------------------------ icdoo's Profile: http://www.excelforum.com/member.php...o&userid=27342 View this thread: http://www.excelforum.com/showthread...hreadid=501422 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create an array inside a For...Next loop | Excel Programming | |||
| Modifying an Array Formula | Excel Worksheet Functions | |||
| Assigning array to range inside Excel Object | Excel Programming | |||
| Check if a String is inside an Array | Excel Programming | |||
| Check if a String is inside an Array | Excel Programming | |||
![icdoo[_8_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
