Setting ranges that could change in VBA

[hermones]

I want to set a range for some coding, but the range will change each time the code is run, it will start on one row and then move down to the next each time.

If ActiveCell.Offset(0, -1).Value Like "*L001*" Then
    ActiveCell.Value = Application.WorksheetFunction.networkdays(range("B2"), range("C2")) - 0.5
    Else
    If ActiveCell.Offset(0, -1).Value Like "*L002*" Then
    ActiveCell.Value = Application.WorksheetFunction.networkdays(range("B2"), range("C2"))
    End If
    End If

The ranges are B2 and C2, then will change to B3 and C3, and so on, how can I change my code to reflect this?
Kind Regards

[Yongle]

There are many ways you could do this. This is one way.
Keeping count
You need to keep "count" of where the code should be going next, even if the workbook is closed. So let's put that value in a cell somewhere in the workbook. Choose a cell that will never be affected by anything else - you cannot insert rows above it or columns to the left of it otherwise VBA will not find it. I would put a note next to it to help me remember in the future.
The code
Include the 3 lines below at the beginning of your code. It will add 1 to the value of the chosen cell each time the code is run, and then the code can use that value
Cell references
Adapt this code by changing the cell reference and the sheet to match the cell/sheet you choose

Dim a As Integer
Worksheets("Sheet1").Range("A1").Value = Worksheets("Sheet1").Range("A1").Value + 1
a = Range("A1").Value

Cell ranges
Change the ranges in your existing code so that it uses this "count" as follows:
- change Range("B2") to Range("B" & a) - with a space between " and & and another one between & and a
- change Range("C2") to Range("C" & a)
And remember to put the correct value in your chosen cell before running the code again. If you put nothing in the cell then it will use B1 and C1 (being its current value of zero plus 1)

[hermones]

Thank you so so much!!

[Yongle]

if you are a satisfied customer please go to Thread Tools (at top of thread) and mark the thread as "solved".
thank you
Yon