Excel: Importing from Access based on two date fields on multiple rows 2

[AngelB]

Hi All

I am very new to VBA and am trying to pull some data into an Excel Spreadsheet from an Access database (both 2002 edition) using a VBA written macro.

My spreadsheet has several rows, of which the first two cells in each row contain a start date and end date respectively. The goal is to import into these rows, several fields from a table in the database where the start and end dates match the ones in the excel worksheet. I have no problems connecting to the database and retrieving matching data for one row.

The real question is how to introduce some level of recursion into the VBA, as the number of rows in the worksheet is variable and I do not want to have to write the same code for each line where I would have to hard code cell references for the two dates.

Please ask if this needs more explanation - any help is appreciated.

Many thanks


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[Bong]

If the data on your worksheet are contiguous, then sheetn.usedrange, where "n" is the sheet number, should return the range you want. Then you can use find to locate the appropriate dates, or for each r in sheetn.usedrange.rows to march down those rows and look in the cells individually.

_________________
Bob Rashkin

 

[AngelB]

Thanks for the quick reply Bob.

I knew I would have missed something out in my initial description. There are other sections to the spreadsheet - above and below the date section I mentioned so I cannot use the usedrange property. I will know where to start the section as I will know where the previous section ends, and once the date section is populated I will know where to start the following section. Any ideas on what I could use instead of usedrange in this case? Unfortunately the vba help files are not installed on my machine yet as we are due to move offices next week so it won't be done until then.

Thanks again


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[Bong]

As I learned in this forum (I can't remember the thread), if you start at a cell, say cells(n,m) then you can mimic the End/Down Arrow behavior on the spreadsheet as:
cells(n,m).end(xlDown).row
will return the row number of the last occupied cell in the column, "m".

_________________
Bob Rashkin

 

[SkipVought]

Set MyRange = SheetObject.Range(cells(n,m), cells(n,m).end(xldown))

assuming that the range has contiguous values.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[AngelB]

Thank you both very much - very useful posts


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy