I am an Access Programmer using automation to populate
Excel named ranges by looping thru an Access Recordset.

The procedure transfers data to 168 Excel named ranges.

However, there are 3 named ranges which result in Error
1004. Here is part of the code. ..

If Not IsNull(!PDRN) Then
M = !TheGroup: N = !PDRN:O = !CapRN
Else
GoTo HeyGo
End If
Set objXLSheet = objXLWb.Worksheets(M)
If N = "GGGTeam2" Then GoTo WasBad
If N = "PTwoATCT33343" Then GoTo WasBad
objXLSheet.Range(N).Clear: objXLSheet.Range(N) = !TotPts
===
The Error 1004 occurs in "objXLSheet.Range(N).Clear"
directly above. I used the 2 "If" statements to ensure the
rest of the code worked.
===
WasBad:
If O = "CWAGT23343" Then GoTo HeyGo
objXLSheet.Range(O).Clear: objXLSheet.Range(O) = !CapPts
===
The Error 1004 occurs in "objXLSheet.Range(N).Clear"
directly above. I used the 2 "If" statements to ensure the
rest of the code worked.
===
HeyGo:
.MoveNext

I don't know that much about Excel, but I suspect the
Worksheet holding the 3 "bad" cells could be a partially-
corrupted file. The 3 errors occur in adjacent rows in
the adjacent columns. The named ranges directly above &
below the "2 bad rows" work fine.

Thinking about making a new Worksheet for the part of the
automation. We are using 6 Worksheets to receive the
Access data. The VBA code is solid & works for the rest
of the named ranges.

Any ideas welcomed. I did insert 2 rows between the 2 bad
rows, but that didn't fix the problem.

TIA - Bob