Sorry, I figured someone would have seen a letter used in an offset like that
and be able to point me in the right direction. Here is the whole sub, does
this help?
Sub rptOOH()
Application.ScreenUpdating = False
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Sheets("ORDERHEAD").Activate
Range("A1").Select
Sheets("ECLLINE").Activate
Cells.Select
Selection.Copy
Sheets("OOH Details").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("ECSLINESP").Activate
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("OOH Details").Select
x = Cells.Find(What:="*", SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row + 1
Cells(x, 1).Select
ActiveSheet.Paste
x = Cells.Find(What:="*", SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row
While x > 1
ord = Cells(x, 1)
Set FORD = Sheets("ORDERHEAD").Columns("A:A").Find(What:=ord,
LookIn:=xlValues, lookat:=xlWhole)
If FORD Is Nothing Then
Cells(x, 1).EntireRow.Delete
Else
Cells(x, 9) = FORD.Offset(0, 1) 'date
Cells(x, 10) = FORD.Offset(0, 2) 'Cust Code
Cells(x, 11) = FORD.Offset(0, 11) 'cust name
Cells(x, 12) = FORD.Offset(0, 10) 'incom code correct
If Cells(x, 12) = "" Or (Cells(x, 12) <> 1 And Cells(x, 12) <> 2 And
Cells(x, 12) <> 4) Then
Cells(x, 12) = 1
End If
Cells(x, 13) = FORD.Offset(0, 8) 'incom code EHIC
On Error Resume Next
Cells(x, 14) = Cells(x, 13) - Cells(x, 12)
On Error GoTo 0
Cells(x, 15) = Format("'" & Cells(x, 5), "") & Format(Cells(x, 12), "")
Cells(x, 16) = FORD.Offset(o, 13)
End If
x = x - 1
Wend
Cells(1, 9) = "Date"
Cells(1, 10) = "Cust Code"
Cells(1, 11) = "Cust Name"
Cells(1, 12) = "IntCom Code"
Cells(1, 13) = "EHIC"
Cells(1, 8).FormulaR1C1 = "=SUM(R[1]C:R[65535]C)"
Range("A1:M1").Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Sheets("ORDERHEAD").Cells.ClearContents
Sheets("ECLLINE").Cells.ClearContents
Sheets("ECSLINESP").Cells.ClearContents
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Sheets("OOH Details").Select
End Sub
George Nicholson said:
so I'm not sure what it means, column o maybe?
Well, that's sort of a key point, don't u think? What is the value of o when
the line fails (it should be a number)? What is the address of FORD at that
point?
The line in question assigns Cell(x,16) the value of the cell at
FORD.Offset(+/- Num of rows,+/-Num of Cols).
We know FORD is in column A. If FORD's row number + o results in a value
less than zero, you will get an error..
'**************
Frankly, given the snippet provided, I have a hard time this code will even
run as far as you say it does. I don't think the compiler would let it. The
following generates a "End If without block If" error:
While x > 1
' NO If...Then
' NO Wend
End If
but it hangs up on the second to last line
....it hangs with what error message?? Being more specific might make it
easier to help you.
*If* you get the same error message I do, then I'd suspect that the code
(not worksheet) has changed since last month and i would consider opening a
backup copy of the file (from when you 'know" it worked) and look at the
same section of code and see what it USED to be when it was working. My
guess is that the While... should be "If x>1 Then". (Changing the End If to
Wend is a bad idea because it would create an endless loop since x isn't
being changed).
(All this assumes that we're looking at a complete code snippet).
HTH,
I'm afriad not. "o" is used a number of times in the code with no
problem,
however "o" is not defined anywhere in the code, so I'm not sure what it
means, column o maybe?
:
:
Amoung other things the code say what you see below, but it hangs up on
the
second to last line, "Cells(x, 16) = FORD.Offset(o, 13)" the code
worked
last month when we used it. What might have changed in the spreadsheet
to
make it hang up here?
x = Cells.Find(What:="*", SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row + 1
Cells(x, 1).Select
ActiveSheet.Paste
x = Cells.Find(What:="*", SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row
While x > 1
ord = Cells(x, 1)
Set FORD = Sheets("ORDERHEAD").Columns("A:A").Find(What:=ord,
LookIn:=xlValues, lookat:=xlWhole)
Cells(x, 13) = FORD.Offset(0, 8) 'incom code EHIC
On Error Resume Next
Cells(x, 14) = Cells(x, 13) - Cells(x, 12)
On Error GoTo 0
Cells(x, 15) = Format("'" & Cells(x, 5), "") & Format(Cells(x, 12),
"")
Cells(x, 16) = FORD.Offset(o, 13)
End If
Is that supposed to be a zero not the letter O?
