Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOW-TO? Loop through cells in a column
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOW-TO? Loop through cells in a column
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop Until LDate Column Date | Excel Discussion (Misc queries) | |||
Loop Column | Excel Discussion (Misc queries) | |||
loop thru column and highlight the rows | Excel Discussion (Misc queries) | |||
VBA loop cells | Excel Programming | |||
How to loop through cells in macro | Excel Programming |