For Each Statement confusion!

[joshisms]

xpost: http://www.ozgrid.com/forum/showthread.php?t=181982&p=681810#post681810
xpost: http://www.excelforum.com/showthread...=1#post3385106 (READ ME posted here!)

I'm in the middle of finishing a lengthy macro and cannot get my head around how to make this For Each statement work properly. Any brave souls up for a challenge?

-----------------------------------------

Calling all Excel Experts!! Your help is desperately needed!! Will anyone please help me solve this issue?

Visit the link https://app.box.com/s/r4k7nvgzwk7wqq0ciqjj and read the _README file. It will help to understand how this macro is supposed to work. There are screenshots and descriptions clearly explaining the steps. All files can be previewed in your browser and do not require download.

I have re-uploaded new files to this on the xposts above. The source worksheet is a .csv file saved as .xls because ozgrid does not allow .csv files to be uploaded. You will have to resave the source file from ".csv.xls" to ".csv" when you save it to your hard drive locally to make it work.

The macro is in the target workbook file. Best thing would be to allow all the formatting to run and start stepping through it at the point in the code where the IMPORTING BEGINS with the For Each Statement (near the end of code).

 

[James Snyder]

The error I saw was in the way you find the 'last row'. By using xlDown, you find the first empty cell, which is what you describe the problem as being. Use xlUp and a large number to narrow down:

lastRow = valWkSht.Range("K65535").End(xlUp).Row

 

[joshisms]

could you tell me where this should be placed in my code?

The error I saw was in the way you find the 'last row'. By using xlDown, you find the first empty cell, which is what you describe the problem as being. Use xlUp and a large number to narrow down:

lastRow = valWkSht.Range("K65535").End(xlUp).Row

 

[James Snyder]

It is in the TimeCardDataImport at the third declaration...

Set targetRange = ...

 

[Rick Rothstein]

Use xlUp and a large number to narrow down:

lastRow = valWkSht.Range("K65535").End(xlUp).Row

Don't just use a "large number"... use the last row available in the worksheet...

lastRow = valWkSht.Cells(Rows.Count, "K").End(xlUp).Row

 

[joshisms]

I truely appreciate you helping to improve my code and will certainly look into implementing your suggestions, but this is not where the issue lies, at least I do not think this is where the issue exists. The target range is

It is in the TimeCardDataImport at the third declaration...

Set targetRange = ...

 

[James Snyder]

It isn't trying to improve your code as much as fix the range you are looking at. When you use xlDown, it stops at the first empty cell defining your range as top to empty cell, not top to bottom...

 

[joshisms]

But "K" refers to the source sheet. "A" is the target sheet ..... this is why I am confused. Should this be the sourceRange instead?

It is in the TimeCardDataImport at the third declaration...

Set targetRange = ...

 

[joshisms]

So could this be why it stops when it encounters a blank cell in OutDate ("T:T")?

It isn't trying to improve your code as much as fix the range you are looking at. When you use xlDown, it stops at the first empty cell defining your range as top to empty cell, not top to bottom...

 

[James Snyder]

Copy, comment out the original line, paste the line in below it and make the change and try it. It may be something else, but that was what popped out at me.