PDA

View Full Version : [SOLVED] Finding from till end



Airborne
11-23-2004, 01:46 PM
Hi all. Last time I got great help from Ken about a search engine. Now after some days of using the engine some requests arose. They want to put a date in the form and then search for files and put the files in a sheet as hyperlinks. The problem is that the names of the file are Datadd-mm-yyyy_1_morning, Datadd-mm-yyyy_1_afternoon, Datadd-mm-yyyy_1_night, Datadd-mm-yyyy_2_morning, Datadd-mm-yyyy_2_afternoon, etc. Now for the search only the mm-dd-yyyy are important since they only want to search for instance for files from 10-11-2004 till 22-11-2004 (22-11-2004 being the last day in the Nov-2004 map at that moment). Only search per month.:wot

I want to use the same form and codes as Ken made but they have to be adjusted a bit:blush . I'll upload the form and here are the codes


Option Explicit
Private Sub cmdOkay2_Click()
Const MainPath = "\\Disk1\Data\Year\ (file:///Disk1DataYear)"
Dim Prompt As String, _
FilesToProcess As Integer, _
fso As Object, _
FullFilePath As String
'Make sure both textboxes have values assigned
If Not IsDate(tbDate2.Value) Then Prompt = "Please enter a prompt date" & vbCrLf
If tbSearch2.Value = "" Then Prompt = Prompt & "Please enter something to search for"
'If Prompt is empty, then no problems were detected
If Prompt = "" Then
'Create a file scripting object and set the FullFilePath variable
Set fso = CreateObject("Scripting.FileSystemObject")
FullFilePath = MainPath & Year(tbDate2) & "" & Format(tbDate2, "d-m")
'Check if the file path exists, and count the number of files in it
If fso.FolderExists(FullFilePath) Then
FilesToProcess = fso.GetFolder(FullFilePath).Files.Count
If FilesToProcess > 0 Then
'If folder for the month exists & has files in it, call FindAll routine
Call FindAll2(FullFilePath, tbSearch1.Value, FilesToProcess)
Unload Me
Else
'If no files are in the directory, inform the user
MsgBox "The information you entered generated a file path of:" & _
vbCrLf & MainPath & Year(tbDate2) & "" & Format(tbDate2, "mmm") & _
vbCrLf & vbCrLf & "There are no files in that directory!" & vbCrLf & _
"Please modify your selection and try again!", _
vbOKOnly + vbCritical, "Directory is empty!"
End If
Else
'If the folder for the month does not exist, notify the user
MsgBox "The information you entered generated a file path of:" & vbCrLf & _
MainPath & Year(tbDate2) & "" & Format(tbDate2, "mmm") & vbCrLf & vbCrLf & _
"That file path does not exist! Please modify your selection and try again!", _
vbOKOnly + vbCritical, "Folder does not exist!"
End If
Else
'If Prompt is not empty, tell the user what info need correcting and return to the
'userform
MsgBox "Sorry, but I need more information!" & vbCrLf & Prompt, _
vbCritical + vbOKOnly, "Please try again!"
End If
End Sub

Private Sub cmdCancel2_Click()
'Unload the userform
Unload Me
End Sub

Private Sub UserForm_Initialize()
'Shrink userform so progress bar doesn't show
Me.Height = 108
'Put today's date in the userform
tbDate2.Value = Format(Now(), "mmm-yyyy")
End Sub



Thanks.

Ken Puls
11-23-2004, 03:48 PM
Hi again, Airborne!

So, just to clarify here... your file names look like this?

Data22-11-2004_1_morning.xls
Data22-11-2004_1_afternoon.xls
Data22-11-2004_1_evening.xls
Data22-11-2004_2_morning.xls
Data22-11-2004_2_afternoon.xls
Data22-11-2004_2_evening.xls

Is that correct? If not, can you post a two day sample of the file names you might get?

Cheers,

Airborne
11-23-2004, 04:48 PM
Hi Ken :) ,

There are 5 shifts. Shift 1,2,3,4,5 and they all cover morning/afternoon/night. So in one month you will see for instance on the first of november Data1-11-2004_2_night, Data1-11-2004_1_morning, Data1-11-2004_4_afternoon.
If we want to see the reports of the 1st of november we are only interested in the reports and not the shift and name of the shift.

Now when someone comes back from a week off on say the 15th, he wants to look at the reports of the 1st up till the 15th. But he must also have the choice to only open reports from say the 10th up till the 15th.

I thought of using your form where I type the month-year in one textbox and the day from-till in the other box. When I enter the date's it will open the reports of the dayrange and show them as hyperlinks on sheet Found.:wot

Thanks and regards.

Ken Puls
11-24-2004, 12:13 AM
Hi Airborne,

I'm not completely clear on what we're trying to get to here... Are we still searching each file for a specific term in a text box?

If we receive the following list:
Data22-11-2004_1_morning.xls
Data22-11-2004_1_afternoon.xls
Data22-11-2004_1_evening.xls
Data22-11-2004_2_morning.xls
Data22-11-2004_2_afternoon.xls
Data22-11-2004_2_evening.xls

What output are we looking for... just Data22-11-2004? Are we trying to pull all the shifts into one file? Wen someone clicks on the hyperlink, what file should it open?

Sorry, I'm all questions here...

Airborne
11-24-2004, 02:13 AM
:blush Sorry Ken for not being clear enough. No we are not searching for text anymore. The search option is great, no problems. I guess it's much easier (for you:*) ). I just want to put files of a desired date range (e.g. the reports of the 1st of november till the 5th of november) as hyperlinks in sheets("Found") so I can click on them and open them. Just open them one at a time when they are put as hyperlinks in the sheet. Like looking in Explorer but now you only see the desired date range and not all the files.

I know we can open Explorer, go to the network drive, select the desired files and open them but it's nicer to stay in the workbook were we also have the search option. So with this workbook we can search for words of a desired month but now we also just want to find some reports of a desired date range and open them (not to look for words in the text but just to read the report).

Thanks and regards.

Ken Puls
11-24-2004, 12:03 PM
Hi Airbrone,

Truth be told, it probably would have been easier for me if I wasn't part of the last one! I have to get my mind out of what we did last time and into a new frame of mind for this task! ;)

Okay, so here's what I'm thinking:
-You have your userform and put in the 'date from' and 'date to'
-Those dates are passed to a routine
-The routine picks the file folder and creates a hyperlinked list of all files that have any date in that range in the title (one line per file)

That sound about right so far? If I remember correctly, your file structure looks like this though:

\\Disk1\Data\Year\mmm\ (file://\Disk1DataYearmmm) with mmm being the month, and each month's files being stored in those directories. Is that correct?

If so, we're going to have to build it smart enough to search through a couple of different folder should the user enter a range that spans over a month end. (That'll be the tricky part, but it can be done.)

Cheers,

Airborne
11-24-2004, 12:58 PM
Hi Ken, glad you are positive about it because one VBA pupil can ask more then 10 VBA specialists can answer I guess:D . I'm going through the training on the forum now and it's really worthwhile. But I'm just starting on lesson 4, so much to do.

You are right about the path.
I would like to keep the Form you designed. Have one textbox where you type for instance Nov-2004 and then a textbox where you type 10-15 (the reports from the 10th till the 15th of november). But I would also like to have the option to just choose the 10th of november (so a range or just 1 day).:wot

Thanks so far.

Ken Puls
11-25-2004, 12:55 AM
I would like to keep the Form you designed. Have one textbox where you type for instance Nov-2004 and then a textbox where you type 10-15 (the reports from the 10th till the 15th of november). But I would also like to have the option to just choose the 10th of november (so a range or just 1 day).:wot
Okay... well... I tweaked it a bit. :blush

So here's the deal with what I did. I renamed the form to something slightly different so that you can import it into your project without blowing away the other one. (we can't use exactly the same form, as it was designed for different methods. I'm thinking that it's mainly the progress bar that you want to keep though... I've worked with that.

FYI, all the code in this one is self contained, so we don't have to worry about it conflicting with the other modules.

You'll notice that it asks for a start and end date, instead of a month/year and then day range. The reason for this is that it makes it easier to loop through each day in the range. It then runs a loop inside each day, to see if the files are there.

This is smart enough to deal with ranges spanning over months, or even years.

The only part that concerns me here is that it adds a hyperlink for each file it finds... so if there happen to be 6 files for one day you get six hyperlinks. We can work on that.

Import this into your project and take it for a test drive. Use dates that do exist, dates that you don't have data for, and even a date that it a couple of months from now.

Let me know if this hits the mark, :vv or is way off base from what you were after.

Cheers,

Airborne
11-25-2004, 09:11 AM
Hi Ken, thanks. It works but there are a few problems:

If I search for reports 9-10-2004, the search bar shows searching for files 09-10-2004. They don't excist however but then it shows hyperlinks 19-10-2004 and 29-10-2004 and 9-10-2004. Our reports are in the format 7-10-2004, 8-10-2004, etc.

When the hyperlinks are loaded on the "Found" sheet it messes up my sheet. The cell ranges are changed. But I want to use the same setting as when I search for text in files. I want to put the files in B6:E24.

When the files are shown on the "Found" sheet I only want to see the file name and not the total path.

I feel a little :blush telling you all this, more work!!

Thanks anyway and regards.

Ken Puls
11-25-2004, 11:11 AM
Hi Airborne!

Hmmm... yes I can see that being an issue. I didn't even think about that! I always name my files mm-dd-yyyy (05-05-2004) so that they sort correctly in lists, as I hate it when my list goes 1,11,12...19,2,20...etc. Regardless, we can make that go, but we made need to approach it differently.

I don't know if we'll be able to use the Instr function (currently in use) for the reason you mentioned above, so we may need to count characters and attack it that way. Are you familiar with the Left, Right and Len functions? (If not, you may want to review those in the standard Excel help, as they'll come in to play here.)

As for messing up your sheet... well... yeah! I actually coded it to completely clear out all data and formatting on the sheet! We can change that though. So should we be writing the values to B6, C6, D6, E6 then B7, C7...? Or down then up? It's easier to put them all in one column, but again, we can make this work.

As for the total path, is that what it's showing? You're using XL2003, right? I thought I coded it to only put the filename as the mask. OUt of curiosity, assume that we've pulled file Data5-11-2004_1_morning.xls, what do you want it to say on the hyperlink?

Cheers,

Airborne
11-25-2004, 12:06 PM
Hi Ken,
as I hate it when my list goes 1,11,12...19,2,20...etc. . I guess you are right about that. I hate that too. The problem is that I have to rename many, many files to change that. But does that mean if I want to get the files 1-11-2004 till 20-11-2004 that the hyperlinks on the "Found" sheet will show up in that irritating manner?


So should we be writing the values to B6, C6, D6, E6 then B7, C7...? Or down then up? Down then up please:blush .


As for the total path, is that what it's showing? You're using XL2003, right? I thought I coded it to only put the filename as the maskI'm very sorry:blush , the names of the files are showing like I wanted and like you planned. Forget my remark.

Thanks and regards.

Ken Puls
11-25-2004, 12:20 PM
Hey there,

Don't rename your files. Let's see if we can make it work the way you want first. I think that doing that much work would be a last resort. I only mention it in case you're setting up a new system in the future as it may be something you want to consider then.

I'm pretty sure that we can make them show up in order. I'll have to think a bit about filling the range. Filling the range of B6:E24 means you have a limit of 16 files returned, as well. I assume that we just want to quit entering data (exit the loop) after that point?

Airborne
11-25-2004, 12:29 PM
Hey Ken, sorry:blush I mean Range("B6:F24"), 95 files. The maximum files for one month could be 93. So it should be alright.


I'm glad I don't have to rename the files for now:D .

Regards.

Ken Puls
11-26-2004, 02:02 AM
Hi there,:)

Sorry for the delay in getting back to you on this one. I wanted to be able to sit down and think this one through... mostly to do with the data placement. So let's try this one out... A summary of the changes:

-Set to clear Range("B6:F24") at beginning of procedure
-Had to add a row/column counter set in the routine to allow data to be placed in the range down the column first, then across to next column (uses the cells(r,c) object, rather than range("A65536").end(xlup)
-Also added a bailout in case more than 95 matches were found (19x4 cells) so the procedure just exits at this point
-Dropped InStr in favour of some left/right text functions to ensure that file names can deal with single digit days and/or months in the date strings



Option Explicit
Private Sub cmdOkay_Click()
Const MainPath = "\\Disk1\Data\Year (file://\Disk1DataYear)"
Dim Prompt As String, FullFilePath As String, _
StartDate As Date, EndDate As Date, x As Date, _
fso As Object, fs As Object, _
FilesToProcess As Integer, i As Integer, Countfiles As Integer, _
wsTarget As Worksheet, _
r As Integer, c As Integer, _
datelength As Long
'Turn off screen updating
Application.ScreenUpdating = False
Set wsTarget = Worksheets("Found")
'Make sure both textboxes have date values assigned
If Not IsDate(tbFromDate.Value) Then
Prompt = "Please enter a valid date in the From field" & vbCrLf
Else
StartDate = tbFromDate.Value
End If
If Not IsDate(tbToDate.Value) Then
Prompt = Prompt & "Please enter a valid date in the To field"
Else
EndDate = tbToDate.Value
End If
'If Prompt is empty, then no problems were detected
If Prompt = "" Then
'Clear out the worksheet
With wsTarget
.Range("B6:F24").Clear
End With
'Initialize C to place data in the first column of the data storage field
'(This is used to start in column B below - cells(r+5,c+1)
c = 1
'Create a file scripting object and set the FullFilePath variable
Set fso = CreateObject("Scripting.FileSystemObject")
'Create a file search object to work with the files inside the loop
Set fs = Application.FileSearch
'Expand userform to show progress bar
Me.Height = 174
'For each date in the range entered, search for matching files
For x = StartDate To EndDate
'Create the path to the files based on the date being examined
FullFilePath = MainPath & Year(x) & "" & Format(x, "mmm") & ""
datelength = Len(Format(x, "d-m-yyyy"))
'Update the progress indicator title, and set progress back to start
With Me
.lblProgDesc.Caption = "Searching for files containing " & Format(x, "dd-mm-yyyy")
.frmProgress.Caption = "0% complete"
.lblProgress.Width = 0
.Repaint
End With
'Check if the file path exists (using file scripting object)
If fso.FolderExists(FullFilePath) Then
Countfiles = 0
'Search for matching files (using file search object)
With fs
'Set the directory to look in
.LookIn = FullFilePath
'Search only Excel workbooks in the directory
.FileType = msoFileTypeExcelWorkbooks
'Execute the search
.Execute
'Count total files to process for progress indicator
FilesToProcess = .FoundFiles.Count
'Search through all files for filenames containing the date being
'evaluated, and create a hyperlink to those files
'(Displays only data after the word "Data" in the file name)
For i = 1 To .FoundFiles.Count
'Evaluate if the file name found matches that searched for
'(InStr returned 11-1-2004 as well as 1-1-2004 so chose left/right combo)
If Left(Right(.FoundFiles(i), _
Len(.FoundFiles(i)) - Len(FullFilePath) - 4), _
datelength) = Format(x, "d-m-yyyy") Then
'Increment the Row and Column counters to place the data
If r < 19 Then
r = r + 1
Else
r = 1
c = c + 1
End If
'If column > 5 then too many matches have been found to fit in the data
'range, so exit procedure
If c > 5 Then GoTo ExitPoint
'Add the hyperlink in the correct row/column
wsTarget.Hyperlinks.Add _
Anchor:=wsTarget.Cells(5 + r, 1 + c), _
Address:=.FoundFiles.Item(i), _
TextToDisplay:=Right(.FoundFiles(i), _
Len(.FoundFiles(i)) - Len(FullFilePath) - 4)
End If
'Count files processed for progress indicator
Countfiles = Countfiles + 1
'Update the progress indicator
Me.frmProgress.Caption = Int(Countfiles / FilesToProcess * 100) & "% complete"
Me.lblProgress.Width = Countfiles / FilesToProcess * (Me.frmProgress.Width - 10)
Me.Repaint
Next i
End With
Else
'If directory does not exist, mark this in the list
wsTarget.Range("A65536").End(xlUp).Offset(1, 0).Value _
= FullFilePath & " does not exist. No data found for " & Format(x, "d-mm-yyyy")
End If
Next x
Else
'If Prompt is not empty, tell the user what info need correcting and return to the
'userform
MsgBox "Sorry, but I need more information!" & vbCrLf & Prompt, _
vbCritical + vbOKOnly, "Please try again!"
End If
ExitPoint:
'Turn screen updating back on, release all objects, and unload the userform
Application.ScreenUpdating = True
Unload Me
End Sub


I tried to document the code fairly well so you can follow what it's doing all the way through. It can be a bit confusing though, as it uses loops within loops... particularly when some variables are set within the first, and not the second vs when they're set in the innermost... :bug:

If you have any questions on any of it, just let me know, and I'll see if I can explain better. And, of course, if something doesn't work, or you want to modify it a bit, let me know!

Cheers,

Airborne
11-26-2004, 04:39 AM
Hi Ken. I don't know what the problem is but when I start the search the progress bar appears. Then after some time you see the color of the bar filling up very fast and then the form unloads and then nothing. When I look at the "Found" sheet I can see that something happened because the cells sizes are changed but the files are not there.

When I try 12-Dec-2006 I will get the text on the "Found" sheet because of 'If directory does not exist, mark this in the list
wsTarget.Range("A65536").End(xlUp).Offset(1, 0).Value _
= FullFilePath & " does not exist. No data found for " & Format(x, "d-mm-yyyy")

So that works. But if I try 12-Dec-2004 it will start searching and after a while the form will unload without any message.

I'm sorry, I guess I'm starting to create this situation:bore


:) thanks and regards.

Ken Puls
11-26-2004, 10:47 AM
Hmmm.. puzzling...

I'm assuming that the Year2006\Dec directory obviously doesn't exist. Out of curiosity, do you have a directory created already for Year2004\Dec, and if so, does it have any relevant files in it?

If I'm right (:vv ) and you do have the folder created, but no relevant files... well, then it's working exactly as designed. We could add something in the loop to add a line if no data was found for that day, which would make it a little more obvious to the user.

If not... well... I'll need some more details on what folders you do have set up (like the December one) and what file names you do have in there. I set up a mock directory here, and created a bunch of files with names per you convention to test, so I should be able to work it out.

Let me know,

Airborne
11-26-2004, 12:02 PM
You are right Ken, there are no files in de Dec map. So that's working as you designed and thought it should work.

But searching on a map filled with files (e.g. Nov) gives the result as I mentioned..
I don't know what the problem is but when I start the search the progress bar appears. Then after some time you see the color of the bar filling up very fast and then the form unloads and then nothing. When I look at the "Found" sheet I can see that something happened because the cells sizes are changed but the files are not there.

Ken Puls
11-26-2004, 01:23 PM
Hi Airborne,

I'm not sure if this will make a difference, but maybe try doing a find/replace, replacing "d-m-yyyy" with "d-mm-yyyy"

If that doesn't work, can you post exactly what date range you tried, exactly what the path to the directory is, and a list of files you expected to find?

Thanks,

Airborne
11-26-2004, 02:10 PM
Hello Ken, I've tried
replacing "d-m-yyyy" with "d-mm-yyyy"
but no files on the "Found" sheet.:confused:
I fill in the date on your form textboxes, e.g. 9-Nov-2004 and 10-Nov-2004.

Your first form in this thread worked. It found the files and placed them in the sheet. Problem was, it couldn't see the difference between 9-11-2004 and 19-11-2004.

So the path is correct "\\Disk1\Data\Year (file:///Disk1DataYear)". The files are stored in the month. So the total path is for instance \\Disk1\Data\2004\Nov (file:///Disk1Data2004Nov) and the filename Data9-11-2004_1_Night.

Thanks so far and regards.:)

Ken Puls
11-26-2004, 03:02 PM
Ahh!

Okay, I think I know what the problem is then... why it worked the first time though, I'm not sure... :confused:

Check out this line:

Const MainPath = "\\Disk1\Data\Year (file:///Disk1DataYear)"

Later in the sub, we add the year to this string with this:

FullFilePath = MainPath & Year(x) & "\" & Format(x, "mmm") & "\"
So, for the date of 9-Nov-2004, we would end up with a path of:
\\Disk1\Data\Year2004\Nov\ (file://\Disk1DataYear2004Nov)

That's not right, though... although I'm not sure why you'd be getting no data, and not the error about no folder exists...

Try changing that Const line to =\\disk1\data\ (file://\disk1data) and see if that makes a difference.

Let me know,

Airborne
11-26-2004, 03:48 PM
Sorry Ken:blush , I think I'm making you confused because the path is \\Disk1\Data\Year (file://\Disk1DataYear). So for Nov-2004 the complete path is \\Disk1\Data\Year\2004\Nov (file://\Disk1DataYear2004Nov).

I've tried your suggestion but if I change it to any other then "\\Disk1\Data\Year\ (file://\Disk1DataYear)" I get the error message you designed (FullFilePath & " does not exist. No data found for " & Format(x, "d-mm-yyyy") ).

Is it something in

FullFilePath = MainPath & Year(x) & "\" & Format(x, "mmm") & "\"
datelength = Len(Format(x, "d-m-yyyy"))?

Ken Puls
11-26-2004, 03:56 PM
Hmmm...

Okay, regardless, the constant still wasn't correct, as it should be Const MainPath = "\\Disk1\Data\Year (file:///Disk1DataYear)\" (I never had that trailing slash on it)

I'll run some more tests here and try to simulate the issue.

Ken Puls
11-26-2004, 04:15 PM
Okay, so what's different between us, here?


I've attached a picture of the directory (with the full path) and all the files that I have in there. If I run a test from 9-Nov-2004 to 10-Nov-2004, I get the correct hyperlinks returned.

Obviously, my full path starts "D:\VBA Tests" instead of "\\Disk1 (file:///Disk1)", but apart from that, everything should be the same. Check everything... punctuation, spelling.. everything. Are the file names set up in the same manner?

My Const is:

Const MainPath = "D:\VBA Tests\Data\Year\"

To my way of thinking, yours should be

Const MainPath = "\\Disk1\Data\Year\"

:confused:



PS: Tried it using Night instead of Morning on the file name, and it also worked.

Airborne
11-27-2004, 10:41 AM
OK Ken:super: :dance: , it works! But.....:blush :blush :blush :blush , it turned out that it worked all along.
I was starting to get very confused so I went to the office and tried it there. It worked right away. Coming back home I found out that I used the wrong filename (testdatad-m-yyyy). At home I changed


Len(FullFilePath) - 8)

and then that worked too. Sorry!
I'm now trying to find out how the code below works



'Increment the Row and Column counters to place the data
If r < 19 Then
r = r + 1
Else
r = 1
c = c + 1
End If


I know I took more of your time then was necessary. But one question.....when the search starts and the progressbar is showing, is it possible to modify the code in a way that you can still activate the cancel button and stop the search:wot ?

Thanks very much for your time and regards.

Ken Puls
11-27-2004, 09:54 PM
:rofl No apologies necessary. I usually test my solutions pretty comprehensively before I post, as I don't want to lead anyone astray. I figured that something must be different...:yes

At any rate, let me see if I can explain the placement code. It actually has a whole bunch of pieces which work together, so I'll try and chop it up a bit.

It works on the concept that the data range is B6:F24, which is 19 rows x 5 columns. We start off well before the first loop, by setting the value of c (to symbolise the column) to 1 here:


'Initialize C to place data in the first column of the data storage field
'(This is used to start in column B below - cells(r+5,c+1)
c = 1
So from this point forward, until we change it, c will refer to column 1 of the data range.

Next comes the first part of the If statement that you asked about. (We'll come back to the Else part once we run the rest of the process)

If r < 19 Then
r = r + 1
{snip}

This part only fires if we have found a file name match, since it's inside that loop. The first time through, r will have a default value of 0 since we have not set it to anything else. We increase the value of r to r+1 which then makes r=1. So now we have r (row) of 1, and c (column) of 1. The next time we successfully find a file, we will again add 1 to r to get 2... at that point, we will have r (row) 2, but still have c (column) 1.

The next relevant part of the code at this point is where we set the hyperlink to the file name... particularly where we set the anchor cell (where the hyperlink is placed) with this:

Anchor:=wsTarget.Cells(5 + r, 1 + c) {snip}

Let's go back to the first match, where we end up with r=1,c=1. When we set the anchor, we're using the cells collection, which consists of all cells on the sheet, from A1:IV65536. We set the anchor to row 5+r. Since r=1, this is row 6. We're also setting the anchor to column c+1. Since c=1, this means column 2... or column B. So your anchor has now been set to B6 for the first match. In the second iteration of the loop, we've added one to r, so it will become B7, then B8, etc..

Let's also fast forward a bit. Assume that r = 18 (we already have 18 successful matches)... we still add 1 to r, giving us 19... + 5 gives us 24, so our data (assuming c is still 1) will be placed in B24... in the first column, that is last cell we want to use.

This is where the Else part of the loop comes in. We already know that the range we want to place data in has only 19 rows, right? So when r = 19, it will get caught by this part of the if statement that you asked about:

If r < 19 Then
r = r + 1
Else
r = 1
c = c + 1
End If
Since r = 19, which is NOT less than 19, it diverts to the else section and sets r back to 1. In addition, though, it also adds 1 to c. So at this point, we now have r=1,c=2. Running those values into this line:

Anchor:=wsTarget.Cells(5 + r, 1 + c) {snip}
...will give us an anchor of cell row 6 (5+1), and column 3 (1+2). The 3rd column is C, so your data is placed in C6. At this point, because r is again less than 19, we start hitting the first part of the IF statement again. That will lead us to C7, C8,...

The final part to note is that we only want data up to F24. Since your data placement range only has 5 columns, we can set the routine to bail out the instant the value of column becomes greater than 5 with this:

'If column > 5 then too many matches have been found to fit in the data
'range, so exit procedure
If c > 5 Then Goto ExitPoint

Does that all make sense?:vv

On your second question... unfortunately no.:mkay I'm not sure of any way to do this. As code processing is linear in nature, I think that one procedure has to end before another can start. You could instruct the users to press CTRL+Break, though, as that would work.

If anyone does know a way, though, I'd appreciate hearing about it as well!

Cheers,:hi:

Airborne
11-28-2004, 11:36 AM
Ken:thumb , thanks for the lesson. And of course thanks for your patience en help.

I guess CTRL+Break will do.

Another thread solved. Again thanks very much and :vv .

Regards.

Ken Puls
11-29-2004, 10:30 AM
Hey Airborne,

You're welcome! It's a pleasure to be helping someone who is genuinly interested in learning how this stuff all works!:yes

Cheers!