HOW-TO? Loop through cells in a column

  • Thread starter Thread starter Mr. Clean
  • Start date Start date
M

Mr. Clean

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?
 
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
 
MC

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

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

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

Mr.C
Click to expand...

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