Refer to a Range using offset to determine next action

[bensko]

I am changing around a sheet where previously the cells I needed to index around where fixed at the top because each week we would insert and push down the prior weeks data. Now the new data will be added at the bottom and I'd like to use a date reference in M1 to search a date range in Column BD to begin my indexing sequence. This is all used to build Raw Json API data on a different sheet and upload events via postman to a Tsheets schedule each day.

I figured out how to get to my starting point - so what used to be "Range("C4").Select" is now

    Dim FindString As Date
    Dim Rng As Range
    FindString = Sheets("Schedule").Range("M1").Value
    With Sheets("Schedule").Range("BD:BD")
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlFormulas, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            Application.Goto Rng, True
        Else
            MsgBox "Nothing found"
        End If
    End With
    ActiveCell.Offset(2, -53).Select

But now I have to figure out how to replace the first and second test of the existing code below

    ActiveCell.Offset(2, 0).Select
    If ActiveCell.Row = 12 Then
    ActiveCell.Offset(-8, 2).Select
    End If
    If ActiveCell.Address = "$AY$4" Then
    GoTo 16
    End If

First test index's up 8 rows and right two if it used to reach row 10 in any column
Then once it works it's way across the range and gets to AY4 it ends the sequence.

How would I use offset to work with the date set by Rng
instead of:

If ActiveCell.Row = 12 Then

Something like:

If Rng.Offset(rowOffset:=10) Then

And replace:

If ActiveCell.Address = "$AY$4" Then

with something like:

If Rng.Offset(columnOffset:= -5) Then

Above ends the sequence if it reaches the column 5 to the left of the Rng date

Any help would be sincerely appreciated.

 

[JackDanIce]

Difficult to follow the code without your sheet visible.

Can you use explicit cell addresses and give an example of what needs to happen?

E.g.

In cell M1 is value x
Find x in column BD, x is found in BD3
Select cell C3 (column C on same row as where x is found)
'Rest of the example

 

[bensko]

It would be very difficult to add an additional row under each day and add "XXX" in that row, then index once it reaches "XXX".

That would work putting it to the right of the last column though to stop once it's reached.

Is there a way to use the ActiveRow, then Set an EntireRow range so that if while indexing down and the 10th row (offset from the Set row) is reached using some variation similar to

If Rng.Offset(rowOffset:=10) Then

?

Here is an snippet showing part of our schedule Where the code indexes down and across 1 day at a time

https://drive.google.com/file/d/0BzJg2oDoGeuONGJXMnpxcWhUN2s/view?usp=sharing

 

[bensko]

Figured it out by adding values to column BE and using it as a helper column

    ActiveCell.Offset(2, 0).Select
    If Range("BE" & ActiveCell.Row).Value = 8 Then
    ActiveCell.Offset(-6, 2).Select
    End If
    If ActiveCell.Column = 53 Then
    GoTo 16
    End If

The first test looks to see if the value in column BE for the active row = 8
The second test looks to see after indexing up 6 and over 2 If the active column is column 53