Macro doesnt seem to run final part / run fully

[PureBluff]

Bit of a strange one here, when I run this macro, the loop at the end doesn't take place or when it does, b46,b47,b48 are correct - but then the next formula in b49 is corrupted

Sub Macro2()

order1 = Sheets("Customer Order").Range("c9").Value
order2 = Sheets("Customer Order").Range("c10").Value

If Sheets("customer order").Range("f7").Value = "" Then
MsgBox ("No date specified in cell F7, please fix!")
Exit Sub
End If


'#order 1 blank
If order1 = "" Then
MsgBox ("Order not speficied!")
Exit Sub
End If
'#end order 1 blank

'#2 order syntax
'# -- order lines --
If order1 <> "" And order2 <> "" Then
With ActiveWorkbook.Connections("Query from FOPS").ODBCConnection
.BackgroundQuery = True
.CommandText = Array( _
"SELECT *" & Chr(13) & "" & Chr(10) & "FROM FOPS6.ORDER_LINES ORDER_LINES" & Chr(13) & "" & Chr(10) & "WHERE (ORDER_LINES.Order_number=" & order1 & ") AND (ORDER_LINES.Boxes_despatched>0) OR (ORDER_LINES.Order_number=" & order2 & ") AND (ORDER_LINES.Boxes_despatched>0)" & Chr(13) & "" & Chr(10) & "ORDER BY ORDER_LINES.Product_Code asc")
.CommandType = xlCmdSql
.Connection = Array(Array( _
"ODBC;DSN=FOPS;ServerName=UKHUNSPVP21.1583;ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP;DBQ=FOPS6;OpenMode=0;ClientVersion=14.1" _
), Array( _
"1.011.000;CodePageConvert=1252;PvClientEncoding=CP1252;PvServerEncoding=CP1252;" _
))
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("Query from FOPS")
.Name = "Query from FOPS"
.Description = ""
End With
    ActiveWorkbook.Connections("Query from FOPS").Refresh



' -- end order lines


' -- order header

        With ActiveWorkbook.Connections("Query from FOPS1").ODBCConnection
.BackgroundQuery = True
.CommandText = Array( _
"SELECT *" & Chr(13) & "" & Chr(10) & "FROM FOPS6.ORDER_HEADER ORDER_HEADER" & Chr(13) & "" & Chr(10) & "WHERE (ORDER_HEADER.Order_number=" & order1 & ") OR (ORDER_HEADER.Order_number=" & order2 & ")")
.CommandType = xlCmdSql
.Connection = Array(Array( _
"ODBC;DSN=FOPS;ServerName=UKHUNSPVP21.1583;ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP;DBQ=FOPS6;OpenMode=0;ClientVersion=14.1" _
), Array( _
"1.011.000;CodePageConvert=1252;PvClientEncoding=CP1252;PvServerEncoding=CP1252;" _
))
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("Query from FOPS1")
.Name = "Query from FOPS1"
.Description = ""
End With
    ActiveWorkbook.Connections("Query from FOPS1").Refresh


' -- end order header
End If
'#end of 2 order syntax







'#single order syntax

' -- order lines --
If order1 <> "" And order2 = "" Then


With ActiveWorkbook.Connections("Query from FOPS").ODBCConnection
.BackgroundQuery = True
.CommandText = Array( _
"SELECT *" & Chr(13) & "" & Chr(10) & "FROM FOPS6.ORDER_LINES ORDER_LINES" & Chr(13) & "" & Chr(10) & "WHERE (ORDER_LINES.Order_number=" & order1 & ") AND (ORDER_LINES.Boxes_despatched>0)" & Chr(13) & "" & Chr(10) & "ORDER BY ORDER_LINES.Product_Code asc")
.CommandType = xlCmdSql
.Connection = Array(Array( _
"ODBC;DSN=FOPS;ServerName=UKHUNSPVP21.1583;ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP;DBQ=FOPS6;OpenMode=0;ClientVersion=14.1" _
), Array( _
"1.011.000;CodePageConvert=1252;PvClientEncoding=CP1252;PvServerEncoding=CP1252;" _
))
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("Query from FOPS")
.Name = "Query from FOPS"
.Description = ""
End With
ActiveWorkbook.Connections("Query from FOPS").Refresh
' -- end order lines --

' -- order header

        With ActiveWorkbook.Connections("Query from FOPS1").ODBCConnection
.BackgroundQuery = True
.CommandText = Array( _
"SELECT *" & Chr(13) & "" & Chr(10) & "FROM FOPS6.ORDER_HEADER ORDER_HEADER" & Chr(13) & "" & Chr(10) & "WHERE (ORDER_HEADER.Order_number=" & order1 & ")")
.CommandType = xlCmdSql
.Connection = Array(Array( _
"ODBC;DSN=FOPS;ServerName=UKHUNSPVP21.1583;ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP;DBQ=FOPS6;OpenMode=0;ClientVersion=14.1" _
), Array( _
"1.011.000;CodePageConvert=1252;PvClientEncoding=CP1252;PvServerEncoding=CP1252;" _
))
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("Query from FOPS1")
.Name = "Query from FOPS1"
.Description = ""
End With
    ActiveWorkbook.Connections("Query from FOPS1").Refresh



' -- end order header
End If

'#end of single order syntax

Sheets("customer order").Activate




Range("b46:b144").ClearContents


Dim i, j As Long
j = 2
For i = 46 To 144

ActiveSheet.Range("b" & i).Formula = _
"=IF(ISERROR(TRIM('Order Lines'!C" & j & ")),"""",(TRIM('Order Lines'!C" & j & ")))"

j = j + 1
Next I

''
'Call SaveFile

end Sub

If it's corrupted B49, the formula in there should be:

=IF(ISERROR(TRIM('Order Lines'!C5)),"",(TRIM('Order Lines'!C5)))

however it's always

=IF(ISERROR(TRIM('Order Lines'!C49)),"",(TRIM('Order Lines'!C49)))

Can anyone shed some light why this corruption may be happening?

Thanks

 

[offthelip]

try replacing these lines of code:

Dim i, j As Long
j = 2
For i = 46 To 144

ActiveSheet.Range("b" & i).Formula = _
"=IF(ISERROR(TRIM('Order Lines'!C" & j & ")),"""",(TRIM('Order Lines'!C" & j & ")))"

j = j + 1
Next i

with these:

Dim i, j As Long
Dim outarr(1 To 99, 1 To 1)
j = 2
For i = 1 To 99
outarr(i, 1) = "=IF(ISERROR(TRIM('Order Lines'!C" & j & ")),"""",(TRIM('Order Lines'!C" & j & ")))"
'ActiveSheet.Range("b" & i).Formula = _
'"=IF(ISERROR(TRIM('Order Lines'!C" & j & ")),"""",(TRIM('Order Lines'!C" & j & ")))"

j = j + 1
Next i
ActiveSheet.Range("b46:b144").Formula = outarr

at the very least it will be faster!!