Loop Through all records in a spreadsheet containing Data 1

[EliseFreedman]

Can someone tell me how to loop through rows in an excel spreadsheet using VBA

 

[SkipVought]

Hi,

Are all your rows contiguous?

Is there a column that has data in each row?

Let's say its column A on the active sheet, where row 1 is headings...

dim r as range
for each r in range(cells(2,1), cells(2, 1).end(xldown))
   msgbox r.value
next

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[EliseFreedman]

Thanks for that Skip,

I can now loop through the rows and read the values into a message box.

I would now like to be able to calculate the number of days elapsed based on dates in two columns and then read the values in the days elapsed column and place different values in another column depending on the value in the days elapsed column. The TLS should have different values depending on the number of days elapsed for example if <30 days have elapsed, then TLS should be =2, if exactly 30 days have elapsed TLS should be 3 and if > than 30 days have elapsed TLS should be 4

My spreadsheet is as follows

Date Received Todays Date Days Elapsed TLS
01/03/2006 27/04/2006 57
01/04/2006 27/04/2006 26
28/03/2006 27/04/2006 30

Any ideas on how i should do this would be appreciated

 

[Loomah]

Hi
You don't need code for this
To get the day simply take the rec'd date from today's date ie add the following formula to the next column (C?) and copy down
=B1-A1

Then add this formula to the next column (D?)
=IF(C1<30,2,IF(C1>30,4,3))

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

This of course can be done in VBA.

However, what you describe can be done with a simple spreadsheet formula...
[tt]
=if(Days_Elapsed<30,2,If(Days_Elapsed>30,4,3))
[/tt]
in VBA...

dim r as range
for each r in range(cells(2,1), cells(2, 1).end(xldown))
   with r.offset(0,2)
     select case .value
        case <30
           .offset(0,1).value = 2
        case =30
           .offset(0,1).value = 3
        case else
           .offset(0,1).value = 4
     end select
   end with
next

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[Loomah]

But, if you really wasnt the code option here is one possibility

just replace the msgbox statement with this

    r.Offset(0, 2) = r.Offset(0, 1) - r
        Select Case r.Offset(0, 2)
            Case Is < 30
                r.Offset(0, 3) = 2
            Case Is > 30
                r.Offset(0, 3) = 4
            Case Else
                r.Offset(0, 3) = 3
        End Select

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​