Issue with range...very odd

[chaostheory]

Im probably doing something totally stupid and simply dont see the mistake in my code, but...i really dont know its driving me nuts. Code is below. The problem is highlighted in red with a comment.

Sub SendDataBack1stFire()
Dim greenTicket As String
Dim strSearchCrit As String
Dim headers(0 To 8) As String
Dim db As Database, rs As DAO.Recordset, r As Long
    Set db = OpenDatabase("Z:\_AC_LBP\Database\03 Converted AC Tracking - Raw Data.mdb")
    ' open the database
    Set rs = db.OpenRecordset("Skid List", dbOpenDynaset)
    ' open a recordset
    ' all records in a table
    For x = 0 To 8
      headers(x) = Range("A3").Offset(0, x)
    Next x
   r = 4 ' the start row in the worksheet
   Do While Len(Range("A" & r).Formula) > 0
     'repeat until first empty cell in column A
     greenTicket = Range("A1").Offset(r, 0)
     rs.MoveFirst
     strSearchCrit = "[Green Ticket] = """ & greenTicket & """"
     rs.FindFirst strSearchCrit
     If rs.NoMatch = False Then
        rs.Edit
        For y = 0 To 8
          If Range("A1").Offset(r, y) <> "" Then 
 
[COLOR=darkred]          ' The first row (r) this triggers on is offset(10,0) (cell A11).[/COLOR]
[COLOR=darkred]          '  It works on that row, then for (10,1) (should be B11) which[/COLOR]
[COLOR=darkred]          ' also has data, it jumps about 8 colums to the right.  It makes[/COLOR]
[COLOR=darkred]          ' no sense to me.[/COLOR]
[COLOR=#8b0000][/COLOR]
            rs.fields(headers(y)) = Range("A1").Offset(r, y).Value
          Else
            Range("A1").Offset(r, y).Select
 
[COLOR=darkred]          ' I added this to see what cell it selects when it fails.  Its[/COLOR]
[COLOR=darkred]          ' Cell J11 for offset range("A1").offset(10,1).  How is this[/COLOR]
[COLOR=darkred]          ' possible????[/COLOR]
[COLOR=#8b0000][/COLOR] 
[COLOR=#8b0000]          ' So essentially it selects, A11, J11, J12, J13, J14, J15 when[/COLOR]
[COLOR=#8b0000]          ' I run it through with F8.[/COLOR]

          End If
        Next y
        rs.Update
     Else
        Range("K1").Offset(r, 0) = "Green Ticket does not exist"
     End If
    r = r + 1 ' next row
   Loop
   rs.Close
   Set rs = Nothing
   db.Close
   Set db = Nothing
End Sub

 

[chaostheory]

Good lord i figured it out.

I have cell 1 merged 8 rows for the Title of the sheet, It must read cell A1, then read the column, which makes it jump to Column J, then count down 11 rows. Seems a little backwards since the formula is (row,column) for offset.

Oh well i got it, closed

 

[chergh]

Merged cells are always a bad idea, better to fake them than have real merged cells.

AFAIK when caluclating an offset it will calculate it from the maximum of your merged cell range e.g. If you have A1:C3 merged then range("A1").offset(3,3) would be cell F6, as would any other cell reference in the merged range.