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

' The first row (r) this triggers on is offset(10,0) (cell A11).
' It works on that row, then for (10,1) (should be B11) which
' also has data, it jumps about 8 colums to the right. It makes
' no sense to me.

rs.fields(headers(y)) = Range("A1").Offset(r, y).Value
Else
Range("A1").Offset(r, y).Select

' I added this to see what cell it selects when it fails. Its
' Cell J11 for offset range("A1").offset(10,1). How is this
' possible????

' So essentially it selects, A11, J11, J12, J13, J14, J15 when
' I run it through with F8.

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

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 :D

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.