HOW-TO? Loop through cells in a column
 

I've got 3 sheets with date columns like below:

ColumnE ColumnJ
-------- --------
3.31.04 10.1.03
11.26.03
11.30.04 7.28.03
12.09.03
12.31.03
1.30.03
3.30.05 7.11.03
12.31.03
7.29.03
3.31.03
8.31.04 1.04.03
6.20.03
9.30.05 12.25.02
12.31.04
11.30.05
9.13.03
10.10.03

9.19.05 7.22.03
6.19.03
2.28.04 10.6.03
12.31.03 10.15.03
8.31.04 12.15.03

3.31.04 8.26.03
10.31.04 7.30.03
10.31.04 4.1.03

Now, I need to iterate through the columns and
check to see if the date in the cell is <30 days
from today. Then I want to copy the info in Columns
A, E and J for that particluar row to a new sheet
created. This needs to be done for each of 3 sheets
in the workbook.

How would I do that?

HOW-TO? Loop through cells in a column
 

MC

Here's an untested example of how you might do that.

Sub Untested()

Dim sh As Worksheet
Dim cell As Range
Dim rng As Range
Dim NwSht As Worksheet

Set NwSht = ThisWorkbook.Worksheets.Add _
(, ThisWorkbook.Worksheets(ThisWorkbook.Sheets.Count) )
NwSht.Name = "NewSheet"

For Each sh In ThisWorkbook.Worksheets
If sh.Name < "NewSheet" Then
Set rng = sh.Range("E1", sh.Range("E65536").End(xlUp))
For Each cell In rng.Cells
If cell.Value + 30 Date Or _
cell.Offset(0, 5).Value + 30 Date Then

With NwSht.Range("A65536").End(xlUp).Offset(1, 0)
.Value = cell.Offset(0, -4).Value
.Offset(0, 1).Value = cell.Value
.Offset(0, 2).Value = cell.Offset(0, 5).Value
End With
End If
Next cell
End If
Next sh


End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Mr. Clean" wrote in message
om...
I've got 3 sheets with date columns like below:

ColumnE ColumnJ
-------- --------
3.31.04 10.1.03
11.26.03
11.30.04 7.28.03
12.09.03
12.31.03
1.30.03
3.30.05 7.11.03
12.31.03
7.29.03
3.31.03
8.31.04 1.04.03
6.20.03
9.30.05 12.25.02
12.31.04
11.30.05
9.13.03
10.10.03

9.19.05 7.22.03
6.19.03
2.28.04 10.6.03
12.31.03 10.15.03
8.31.04 12.15.03

3.31.04 8.26.03
10.31.04 7.30.03
10.31.04 4.1.03

Now, I need to iterate through the columns and
check to see if the date in the cell is <30 days
from today. Then I want to copy the info in Columns
A, E and J for that particluar row to a new sheet
created. This needs to be done for each of 3 sheets
in the workbook.

How would I do that?

HOW-TO? Loop through cells in a column
 

In article ,
says...
MC

Here's an untested example of how you might do that.


Thanks for the feedback. I was beginning to wonder if anyone
was going to help out. The things I tried failed miserably...

Mr.C

HOW-TO? Loop through cells in a column
 

In article ,
says...
In article ,
says...
MC

Here's an untested example of how you might do that.


Thanks for the feedback. I was beginning to wonder if anyone
was going to help out. The things I tried failed miserably...

Mr.C


Is there a way to make a toolbar button execute this query?

HOW-TO? Loop through cells in a column
 

In article ,
says...
In article ,
says...
In article ,
says...
MC

Here's an untested example of how you might do that.


Thanks for the feedback. I was beginning to wonder if anyone
was going to help out. The things I tried failed miserably...

Mr.C


Is there a way to make a toolbar button execute this query?


Nevermind, I found out how to do that.
Thanks for the help

Mr.C