Array Formula Issue Between XP and WINDOWS7

HotNumbers · Sep 19, 2011

Can someone PLEASE HELP.

I have been using the following Array formula for years in XP with no Issues. Recently PC was updated to WINDOWS 7. The following formula is inserted into the worksheet via VB coding. CAN SOMEONE PLEASE TELL ME WHY WINDOWS & will not recognize the following entry symbol { }

IN XP this is how the formula looks.
{=IF(ISERROR(MATCH(B12,S1C3,FALSE)),0,-SUM(IF(S1R2&S1R3="Gross RevenuesActual Qty",IF(S1R0*1>8,INDIRECT("'FY2011'!$F$"&MATCH(B12,'FY2011'!$C$1:$C$1883,FALSE)&":$BY$"&MATCH(B12,'FY2011'!$C$1:$C$1883,FALSE)),0),0)))}

IN WINDOWS7 this is how the formula looks.

=IF(ISERROR(MATCH(B12,S1C3,FALSE)),0,-SUM(IF(S1R2&S1R3="Gross RevenuesActual Qty",IF(S1R0*1>8,INDIRECT("'FY2011'!$F$"&MATCH(B12,'FY2011'!$C$1:$C$1883,FALSE)&":$BY$"&MATCH(B12,'FY2011'!$C$1:$C$1883,FALSE)),0),0)))

Trebor76 · Sep 19, 2011

Hi HotNumbers,

Could you please post the line of code inserting the formula?

Robert

Yahya · Sep 19, 2011

i don't think the Problem With windows version
may the Problem based on Macro Setting
as you said

The following formula is inserted into the worksheet via VB coding.

make sure the Security level For macro

HotNumbers · Sep 19, 2011

I APLOGIZE ... this was done few years ago by a developer who is NO loner with me. I belive this is part of the code that generats the formula. If there is addtional code you require PLEASE email back... I DO APPREICATE your HELP.

Case Is >= zMonthsLag
zString = "-SUM(IF(S1R2&S1R3=""Gross RevenuesActual Qty"",IF(S1R0*1>" & _
zMonths & "," & zzString1 & ",0),0))"

zString = "=IF(ISERROR(MATCH(B" & zReportFirstDataRow & ",S1C3,FALSE)),0," & zString & ")"

Case Is < zMonthsLag

zStringa = "-SUM(IF(S1R2&S1R3=""Gross RevenuesActual Qty""," & zzString1 & ",0))"

zStringb = "-SUM(IF(S2R2&S2R3=""Gross RevenuesActual Qty"",IF(S2R0*1>" & _
12 + zMonths & "," & zzString2 & ",0),0))"

zString = "=IF(ISERROR(MATCH(B" & zReportFirstDataRow & ",S1C3,FALSE)),0," & zStringa & ")"
zString = zString & "+IF(ISERROR(MATCH(B" & zReportFirstDataRow & ",S2C3,FALSE)),0," & _
zStringb & ")"

End Select

zFormulaString1 = zString

End Function

Trebor76 · Sep 19, 2011

How is the udf being called?

HotNumbers · Sep 19, 2011

By udf if you Mean User Defined FIelds all a users does is click on a link that generats a worksheet which utilized this formula in one of the columns. Below is the full formula. Hope this gives your a bit more insite.

'_____________________________________________________________________________________________
Function zFormulaString1() As String
Dim zString As String
Dim zzString1 As String
Dim zzString2 As String
Dim zStringa As String
Dim zStringb As String

Dim zMonths As Long

zMonths = zCurrentPeriod - zMonthsLag

zzString1 = "INDIRECT(""'" & zSheet1 & "'!$F$""&MATCH(B" & zReportFirstDataRow & ",'" & _
zSheet1 & "'!$C$1:$C$" & zQuerySheet1LastRow & ",FALSE)&"":" & zQS1LastColString & _
"""&MATCH(B" & zReportFirstDataRow & ",'" & zSheet1 & _
"'!$C$1:$C$" & zQuerySheet1LastRow & ",FALSE))"

zzString2 = "INDIRECT(""'" & zSheet2 & "'!$F$""&MATCH(B" & zReportFirstDataRow & ",'" & _
zSheet2 & "'!$C$1:$C$" & zQuerySheet2LastRow & ",FALSE)&"":" & zQS2LastColString & _
"""&MATCH(B" & zReportFirstDataRow & ",'" & zSheet2 & _
"'!$C$1:$C$" & zQuerySheet2LastRow & ",FALSE))"

Select Case zCurrentPeriod

Case Is >= zMonthsLag
zString = "-SUM(IF(S1R2&S1R3=""Gross RevenuesActual Qty"",IF(S1R0*1>" & _
zMonths & "," & zzString1 & ",0),0))"

zString = "=IF(ISERROR(MATCH(B" & zReportFirstDataRow & ",S1C3,FALSE)),0," & zString & ")"

Case Is < zMonthsLag

zStringa = "-SUM(IF(S1R2&S1R3=""Gross RevenuesActual Qty""," & zzString1 & ",0))"

zStringb = "-SUM(IF(S2R2&S2R3=""Gross RevenuesActual Qty"",IF(S2R0*1>" & _
12 + zMonths & "," & zzString2 & ",0),0))"

zString = "=IF(ISERROR(MATCH(B" & zReportFirstDataRow & ",S1C3,FALSE)),0," & zStringa & ")"
zString = zString & "+IF(ISERROR(MATCH(B" & zReportFirstDataRow & ",S2C3,FALSE)),0," & _
zStringb & ")"

End Select

zFormulaString1 = zString

End Function

Trebor76 · Sep 19, 2011

By udf if you Mean User Defined FIelds

No, it means User Defined Function. In your case it is the zFormulaString1" udf the programmer developed for you.

Can you see somewhere else in the code where "zFormulaString1" is being returned to a cell(s)?

HotNumbers · Sep 19, 2011

Trebor76 - -- THANKS FOR TRYING TO HELP. Below is the code for this Module. I saw three places where it is used... Not sure if you can narrow it down for me and make the modification.. But thought i give you all of it.

'--------

Option Explicit
Public zRecordset As ADODB.Recordset
Private Const zReportFirstDataRow As Long = 12
Private zReportLastDataRow As Long
Private zCurrentQuarter As Integer

'_____________________________________________________________________________________________
Sub zFormatReturnReserveTier2Issue()
zCalculation = Application.Calculation

Application.Calculation = xlManual

On Error Resume Next

'Find Sheets used for the calculation
zQuerySheets

'if the range name does not exist set MonthsLag to 0
zMonthsLag = CInt([Months_Lag_CY])

If zMonthsLag > 13 Then zMonthsLag = 0

zCurrentQuarter = WorksheetFunction.RoundUp(zCurrentPeriod / 3, 0)

Application.ScreenUpdating = False

zAddFormatHeadings

zCreateReport
zFormatReport
zTotalFormulas

Columns("AD:AF").EntireColumn.Hidden = True
Columns("M:S").EntireColumn.Hidden = True
Columns("AA:AC").EntireColumn.Hidden = True

'set calculation to original
Application.Calculation = zCalculation
Application.ScreenUpdating = True
End Sub

'____________________________________________________________________
Private Sub zAddFormatHeadings()
Dim zCompanyNameColumn As Long
Dim zCompanyName As String
Dim zFormula As String

With Range("A1:A5").Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
End With

With Range("U4")
.Value = "Budget Rate:"
.Font.Size = 10
.Font.Bold = True
.HorizontalAlignment = xlRight
End With

zFormula = "=VLOOKUP("""
zFormula = zFormula & zCompanyCode
zFormula = zFormula & """,'FX Rates'!$1:1000,MATCH(""Budget Rate"",'FX Rates'!$5:$5,FALSE),FALSE)"

With Range("V4")
.Value = zFormula
.Font.Size = 10
.Font.Bold = True
.HorizontalAlignment = xlRight
.Interior.ColorIndex = 15
.NumberFormat = "_(* #,##0.0000_);_(* (#,##0.0000);_(* ""-""??_);_(@_)"
.BorderAround xlContinuous, xlMedium
End With

'added new col to
With Range("B6:G6,I6:K6,M6:R6,T6:V6,X6:Z6,AB6:AC6,AH6:AI6,Ak6:Am6,Ao6:Aq6")
.Font.Size = 9
.Font.FontStyle = "Italic"
.WrapText = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With

'added new col to
With Range("B7:G7,I7:K7,M7:R7,T7:V7,X7:Z7,AB7:AC7,AH7:AI7,Ak7:Am7,Ao7:Aq7")
.Font.Size = 9
.Font.Bold = True
.Font.ColorIndex = 3
.WrapText = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With

'added new col to
With Range("B8:G8,I8:K8,M8:R8,T8:V8,X8:Z8,AB8:AC8,AH8:AI8,Ak8:Am8,Ao8:Aq8")
.Font.Size = 9
.Font.FontStyle = "Italic"
.WrapText = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With

'added new col to
With Range("B10:G10,I10:K10,M10:R10,T10:V10,X10:Z10,AB10:AC10,AH10:AI10,Ak10:Am10,Ao10:Aq10")
.Font.Size = 9
.Font.Bold = True
.WrapText = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With

Range("A1") = "Q6 Returns Reserve Analysis - Tier 2"
Range("A2") = "Buena Vista Home Entertainment International"

zCompanyNameColumn = zFindCompanyNameColumn
zCompanyName = WorksheetFunction.VLookup(zCompanyCode, [CompanyCodeName], zCompanyNameColumn, False)

Range("A3") = zCompanyName & " (" & zCompanyCode & "-" & zBusinessArea & ")"
'Units PL FY2006
Range("A4") = "For the Period Ended: Q" & _
WorksheetFunction.RoundUp(zCurrentPeriod / 3, 0) & " (" & zDate & ")"

Range("A5") = "In Units and Dollars"

Range("B6:D6,F6:G6,I6,M6,O6,Q6") = "TL BW"
Range("E6") = "WRS"
Range("J6") = "Lookup"

'added new col to
Range("K6,N6,P6,R6,T6,U6:V6,X6,Y6:Z6,AB6:AC6,AH6:AI6,Ak6:Am6,Ao6:Aq6") = "Calc"

'added
Range("T6,X6") = "Calc / Input"
Range("ak6,ao6") = "Input"

Range("B7:G7") = Array("A", "A2", "B", "C", "D", "E")
Range("I7:K7") = Array("F", "G", "H = F x G") 'Array("F", "G", "H = F x G") ZG 5/14/08
Range("M7:R7") = Array("L", "M = J - L", "N", "O = N - M", "P", "Q = -O / P")
Range("T7:V7") = Array("R", "S = H x R", "T = S x Rate")
Range("X7:Z7") = Array("U", "V = H x U", "W = V x Rate")
Range("AB7:AC7") = Array("X = O x R", "Y = X x Rate")
Range("AH7:AI7") = Array("R", "U")

'added
Range("Ak7:Am7") = Array("Z", "AA = H x Z", "AB = AA x Rate")
Range("Ao7:Aq7") = Array("AC", "AD = H x AC", "AE = AD x Rate")

Range("I8,K8,M8:Q8") = "Units"
Range("J8,R8") = "%"

'added new col to
Range("T8,U8,X8:Y8,AB8,AH8:AI8,ak8:al8,ao8:ap8") = "$LC"

'added new col to
Range("V8,Z8,AC8,am8,aq8") = "$US"

Range("B10:G10") = Array("MPM Issue #", "MPM Product #", "Title", "Release Date", _
"CBL Category", "Format")
'Range("I10") = "=""Prior ""&IF(ISERROR(Round(Months_Lag_CY,0)),0,Round(Months_Lag_CY,0))&"" months + previous half month's Gross Revenue"""
Range("I10") = "=""Prior ""&IF(ISERROR(Round(Months_Lag_CY,0)),0,Round(Months_Lag_CY,0))&"" months Gross Revenue""" 'ZG 5/14
Range("J10:K10") = Array("CBL Reserve %", "Returns Reserve")
Range("M10:R10") = Array("PQ Returns Reserve", "Change in Returns Reserve", _
"Current Qtr. Actual Returns", "Net P&L Impact Favorable / (Unfavorable)", _
"CQ Gross Revenue Units", "Total Returns Reserve Rate")
Range("T10:V10") = Array("Price (per unit)", "Returns Reserve $LC", "Returns Reserve $US")
Range("X10:Z10") = Array("Standard Cost (per unit)", "Extended Cost $LC", "Extended Cost $US")
Range("AB10:AC10") = Array("Net P&L Impact $LC", "Net P&L Impact $US")
Range("AH10:AI10") = Array("Price (per unit)", "Standard Cost (per unit)")

'added
Range("Ak10:Am10") = Array("Freight (per unit)", "Freight $LC", "Freight $USD")
Range("Ao10:Aq10") = Array("Returns Processing (per unit)", "Returns Processing $LC", "Returns Processing $USD")

Rows("10:10").RowHeight = 84

ActiveWindow.Zoom = 75

End Sub

'____________________________________________________________________
Private Sub zCreateReport()
zCreateRecordset
zPopulateRecordset

zPopulateReport

End Sub

'____________________________________________________________________
Private Sub zCreateRecordset()

Set zRecordset = New ADODB.Recordset

With zRecordset
.Fields.Append "zMPMIssue", adVarChar, 50
.Fields.Append "zMediaProduct", adVarChar, 50
.Fields.Append "zTitle", adVarChar, 50
.Fields.Append "zCBLCategory", adVarChar, 50
.Fields.Append "zFormat", adVarChar, 50
.Open
End With

End Sub

'____________________________________________________________________
Private Sub zPopulateRecordset()
Dim i As Long
Dim zMPMIssueUsed As String
Dim zMediaProductUsed As String
Dim zTitleUsed As String
Dim zCBLCategoryUsed As String
Dim zFormatused As String

'''On Error Resume Next

zCBLCategoryUsed = "zzz"

With Sheets(zSheet1) 'currently "Units PL FY2006"

For i = zQuerySheetDataRow To zQuerySheet1LastRow
zMediaProductUsed = CStr(.Cells(i, 1))

If Left(zMediaProductUsed, 1) = 7 Then
If zProductExists(zMediaProductUsed) = False Then
zMPMIssueUsed = .Cells(i, 3)
zTitleUsed = .Cells(i, 2)
zFormatused = .Cells(i, 5)

zRecordset.AddNew Array("zMPMIssue", "zMediaProduct", "zTitle", "zCBLCategory", "zFormat"), Array(zMPMIssueUsed, zMediaProductUsed, zTitleUsed, zCBLCategoryUsed, zFormatused)
End If
Else
zCBLCategoryUsed = .Cells(i, 2)
End If
Next
End With

End Sub

'____________________________________________________________________
Private Sub zPopulateReport()
Dim i As Long
Dim zF As Long
Dim zRowOffset As Integer

zRowOffset = zReportFirstDataRow - 1
zRecordset.MoveFirst

For i = 1 To zRecordset.RecordCount
Cells(i + zRowOffset, 1) = i
Cells(i + zRowOffset, 2) = zRecordset!zMPMIssue
Cells(i + zRowOffset, 3) = zRecordset!zMediaProduct
Cells(i + zRowOffset, 4) = zRecordset!zTitle
Cells(i + zRowOffset, 6) = zRecordset!zCBLCategory
Cells(i + zRowOffset, 7) = zRecordset!zformat
zRecordset.MoveNext
Next

zReportLastDataRow = i - 1 + zRowOffset
zF = zReportFirstDataRow

Range("E" & zF) = "=IF(ISERROR(VALUE(VLOOKUP(C" & zF & ",'Release Dates'!$A$" & _
zRDatesDataRow & ":$E$" & zRDatesLastRow & _
",5,FALSE))),"""",VALUE(VLOOKUP(C" & zF & ",'Release Dates'!$A$" & _
zRDatesDataRow & ":$E$" & zRDatesLastRow & ",5,FALSE)))"

Range("I" & zF) = zFormulaString1

Range("I" & zF).Select
SendKeys ("{F2}")
SendKeys "^+{ENTER}", True

Range("J" & zF) = zFormulaString2
Range("K" & zF) = "=IF(I" & zF & "<0,0,Round(I" & zF & "*J" & zF & ",0))"

Range("M" & zF) = zFormulaString3
Range("M" & zF).Select
SendKeys ("{F2}")
SendKeys "^+{ENTER}", True

Range("N" & zF) = "=ROUND(K" & zF & "-M" & zF & ",0)"

Range("O" & zF) = zFormulaString4("Actual Returns")
Range("O" & zF).Select
SendKeys ("{F2}")
SendKeys "^+{ENTER}", True

Range("P" & zF) = "=O" & zF & "-N" & zF

Range("Q" & zF) = zFormulaString4("Gross Revenues")
Range("Q" & zF).Select
SendKeys ("{F2}")
SendKeys "^+{ENTER}", True

Range("R" & zF) = "=IF(ISERROR(-P" & zF & "/Q" & zF & "),0,-P" & zF & "/Q" & zF & ")"

Application.ScreenUpdating = True

Range("AD" & zF) = zFormulaString5a
Range("AD" & zF).Select
SendKeys ("{F2}")
SendKeys "^+{ENTER}", True

Range("T" & zF) = zFormulaString5
Range("T" & zF).Select
SendKeys ("{F2}")
SendKeys "^+{ENTER}", True

Range("U" & zF) = "=ROUND(K" & zF & "*T" & zF & ",2)"
Range("V" & zF) = "=U" & zF & "*$V$4"

Range("AE" & zF) = zFormulaString6a
Range("AE" & zF).Select
SendKeys ("{F2}")
SendKeys "^+{ENTER}", True

Range("AF" & zF) = zFormulaString6
Range("AF" & zF).Select
SendKeys ("{F2}")
SendKeys "^+{ENTER}", True

Range("X" & zF) = "=IF(AF12<0,0,AF12)"

Range("Y" & zF) = "=ROUND(K" & zF & "*X" & zF & ",2)"
Range("Z" & zF) = "=Y" & zF & "*$V$4"
Range("AB" & zF) = "=P" & zF & "*T" & zF
Range("AC" & zF) = "=AB" & zF & "*$V$4"

Range("AH" & zF) = zFormulaString5
Range("AH" & zF).Select
SendKeys ("{F2}")
SendKeys "^+{ENTER}", True

Range("AI" & zF) = "=IF(AF12<0,0,AF12)"

'added
Range("AL" & zF) = "=K" & zF & "* AK" & zF
Range("AM" & zF) = "=AL" & zF & "*$V$4"

Range("AP" & zF) = "=K" & zF & "* AO" & zF
Range("AQ" & zF) = "=AP" & zF & "*$V$4"

Application.ScreenUpdating = False
End Sub

'_____________________________________________________________________________________________
Function zFormulaString1() As String
Dim zString As String
Dim zzString1 As String
Dim zzString2 As String
Dim zStringa As String
Dim zStringb As String

Dim zMonths As Long

zMonths = zCurrentPeriod - zMonthsLag

zzString1 = "INDIRECT(""'" & zSheet1 & "'!$F$""&MATCH(B" & zReportFirstDataRow & ",'" & _
zSheet1 & "'!$C$1:$C$" & zQuerySheet1LastRow & ",FALSE)&"":" & zQS1LastColString & _
"""&MATCH(B" & zReportFirstDataRow & ",'" & zSheet1 & _
"'!$C$1:$C$" & zQuerySheet1LastRow & ",FALSE))"

zzString2 = "INDIRECT(""'" & zSheet2 & "'!$F$""&MATCH(B" & zReportFirstDataRow & ",'" & _
zSheet2 & "'!$C$1:$C$" & zQuerySheet2LastRow & ",FALSE)&"":" & zQS2LastColString & _
"""&MATCH(B" & zReportFirstDataRow & ",'" & zSheet2 & _
"'!$C$1:$C$" & zQuerySheet2LastRow & ",FALSE))"

Select Case zCurrentPeriod

Case Is >= zMonthsLag
zString = "-SUM(IF(S1R2&S1R3=""Gross RevenuesActual Qty"",IF(S1R0*1>" & _
zMonths & "," & zzString1 & ",0),0))"

zString = "=IF(ISERROR(MATCH(B" & zReportFirstDataRow & ",S1C3,FALSE)),0," & zString & ")"

Case Is < zMonthsLag

zStringa = "-SUM(IF(S1R2&S1R3=""Gross RevenuesActual Qty""," & zzString1 & ",0))"

zStringb = "-SUM(IF(S2R2&S2R3=""Gross RevenuesActual Qty"",IF(S2R0*1>" & _
12 + zMonths & "," & zzString2 & ",0),0))"

zString = "=IF(ISERROR(MATCH(B" & zReportFirstDataRow & ",S1C3,FALSE)),0," & zStringa & ")"
zString = zString & "+IF(ISERROR(MATCH(B" & zReportFirstDataRow & ",S2C3,FALSE)),0," & _
zStringb & ")"

End Select

zFormulaString1 = zString

End Function

'____________________________________________________________________
Private Function zFormulaString2() As String
Dim zString As String
Dim zLastCountryAssumptionRow As Integer

On Error Resume Next

'Set zlast row to 5 in case sheet Country Assumptions does not exist
zLastCountryAssumptionRow = 5
zLastCountryAssumptionRow = Sheets("Country Assumptions").Cells(65000, 6).End(xlUp).Row

If zLastCountryAssumptionRow = 5 Then
zString = 0
Else
zString = "VLOOKUP(F12,'Country Assumptions'!$A$11:$D$" & zLastCountryAssumptionRow & ",4,FALSE)"
zString = "=IF(ISERROR(" & zString & "),0," & zString & ")"
End If

zFormulaString2 = zString

End Function

'_____________________________________________________________________________________________
Function zFormulaString3() As String
Dim zString As String
Dim zzString1 As String
Dim zzString2 As String

On Error Resume Next

zzString1 = "INDIRECT(""'" & zBSheet & "'!$H$""&MATCH(B" & zReportFirstDataRow & ",'" & _
zBSheet & "'!$C$1:$C$" & zBSheetLastRow & ",FALSE)&"":" & zBSheetLastColString & _
"""&MATCH(B" & zReportFirstDataRow & ",'" & zBSheet & "'!$C$1:$C$" & zBSheetLastRow & ",FALSE))"

zString = "-Sum(If(BSR1=""120075"",IF(BSR3=""Actual Qty"",IF(BSR0=""#""," & _
zzString1 & ",IF(BSR0*1<" & zCurrentQuarterPeriod & "," & zzString1 & ",0)),0),0))"

zzString2 = "MATCH(B" & zReportFirstDataRow & ",'" & zBSheet & "'!$C$1:$C$" & zBSheetLastRow & ",FALSE)"
zFormulaString3 = "=IF(ISERROR(" & zzString2 & "),0," & zString & ")"
End Function

'________________________________________________________________________________________
Function zFormulaString4(zCr As String) As String
Dim zString As String
Dim zzString1 As String

zzString1 = "INDIRECT(""'" & zSheet1 & "'!$F$""&MATCH(B" & zReportFirstDataRow & ",'" & _
zSheet1 & "'!$C$1:$C$" & zQuerySheet1LastRow & ",FALSE)&"":" & zQS1LastColString & _
"""&MATCH(B" & zReportFirstDataRow & ",'" & zSheet1 & _
"'!$C$1:$C$" & zQuerySheet1LastRow & ",FALSE))"

zString = "=-SUM(IF(S1R2=""" & zCr & """,IF(S1R3=""Actual Qty"",IF(S1R0*1>=" & zCurrentQuarterPeriod & "," & zzString1 & ",0),0),0))"
zFormulaString4 = zString
End Function

'________________________________________________________________________________________
Private Function zFormulaString5a() As String
Dim zString As String
Dim zStringa As String
Dim zStringb As String
Dim zzString1 As String
Dim zzString2 As String

Dim zMonths As Long

zzString1 = "INDIRECT(""'" & zSheet1 & "'!$F$""&MATCH(B" & zReportFirstDataRow & ",'" & _
zSheet1 & "'!$C$1:$C$" & zQuerySheet1LastRow & ",FALSE)&"":" & zQS1LastColString & _
"""&MATCH(B" & zReportFirstDataRow & ",'" & zSheet1 & _
"'!$C$1:$C$" & zQuerySheet1LastRow & ",FALSE))"

zzString2 = "INDIRECT(""'" & zSheet2 & "'!$F$""&MATCH(B" & zReportFirstDataRow & ",'" & _
zSheet2 & "'!$C$1:$C$" & zQuerySheet2LastRow & ",FALSE)&"":" & zQS2LastColString & _
"""&MATCH(B" & zReportFirstDataRow & ",'" & zSheet2 & _
"'!$C$1:$C$" & zQuerySheet2LastRow & ",FALSE))"

zMonths = zCurrentPeriod - zMonthsLag

Select Case zCurrentPeriod

Case Is >= zMonthsLag

zStringa = "SUM(IF(S1R2=""Gross Revenues"",IF(S1R3=""Actual Qty"",IF(S1R0*1>" & _
zMonths & "," & zzString1 & ",0),0),0))"

zString = "=IF(ISERROR(" & zStringa & "),0," & zStringa & ")"

Case Else

zStringa = "SUM(IF(S1R2=""Gross Revenues"",IF(S1R3=""Actual Qty""," & zzString1 & ",0),0))"
zStringb = "SUM(IF(S2R2=""Gross Revenues"",IF(S2R3=""Actual Qty"",IF(S2R0*1>" & _
12 + zMonths & "," & zzString2 & ",0),0),0))"

zString = "=IF(ISERROR(MATCH(B" & zReportFirstDataRow & ",S1C3,FALSE)),0," & zStringa & ")"
zString = zString & "+IF(ISERROR(MATCH(B" & zReportFirstDataRow & _
",S2C3,FALSE)),0," & zStringb & ")"
End Select

zFormulaString5a = zString

End Function

'_____________________________________________________________________________________________
Private Function zFormulaString5() As String
Dim zString As String
Dim zStringa As String
Dim zStringb As String
Dim zzString1 As String
Dim zzString2 As String

Dim zMonths As Long

zzString1 = "INDIRECT(""'" & zSheet1 & "'!$F$""&MATCH(B" & zReportFirstDataRow & ",'" & _
zSheet1 & "'!$C$1:$C$" & zQuerySheet1LastRow & ",FALSE)&"":" & zQS1LastColString & _
"""&MATCH(B" & zReportFirstDataRow & ",'" & zSheet1 & _
"'!$C$1:$C$" & zQuerySheet1LastRow & ",FALSE))"

zzString2 = "INDIRECT(""'" & zSheet2 & "'!$F$""&MATCH(B" & zReportFirstDataRow & ",'" & _
zSheet2 & "'!$C$1:$C$" & zQuerySheet2LastRow & ",FALSE)&"":" & zQS2LastColString & _
"""&MATCH(B" & zReportFirstDataRow & ",'" & zSheet2 & _
"'!$C$1:$C$" & zQuerySheet2LastRow & ",FALSE))"

zMonths = zCurrentPeriod - zMonthsLag

Select Case zCurrentPeriod

Case Is >= zMonthsLag

zStringa = "SUM(IF(S1R2=""Gross Revenues"",IF(S1R3=""Actual"",IF(S1R0*1>" & zMonths & _
"," & zzString1 & ",0),0),0))"

zString = "=IF(ISERROR(1/AD" & zReportFirstDataRow & "),0," & _
zStringa & "/AD" & zReportFirstDataRow & ")"

Case Else

zStringa = "SUM(IF(S1R2=""Gross Revenues"",IF(S1R3=""Actual""," & zzString1 & ",0),0))"
zStringa = "IF(ISERROR(MATCH(B" & zReportFirstDataRow & ",S1C3,FALSE)),0," & zStringa & ")"

zStringb = "SUM(IF(S2R2=""Gross Revenues"",IF(S2R3=""Actual"",IF(S2R0*1>" & _
12 + zMonths & "," & zzString2 & ",0),0),0))"
zStringb = "IF(ISERROR(MATCH(B" & zReportFirstDataRow & ",S2C3,FALSE)),0," & zStringb & ")"

zString = "=IF(ISERROR(1/AD" & zReportFirstDataRow & "),0,(" & zStringa & "+" & _
zStringb & ")/AD" & zReportFirstDataRow & ")"

End Select

zFormulaString5 = zString

End Function

'_____________________________________________________________________________________________
Private Function zFormulaString6a() As String
Dim zString As String
Dim zStringa As String
Dim zStringb As String
Dim zzString1 As String
Dim zzString2 As String

Dim zMonths As Long

zzString1 = "INDIRECT(""'" & zSheet1 & "'!$F$""&MATCH(B" & zReportFirstDataRow & ",'" & _
zSheet1 & "'!$C$1:$C$" & zQuerySheet1LastRow & ",FALSE)&"":" & zQS1LastColString & _
"""&MATCH(B" & zReportFirstDataRow & ",'" & zSheet1 & _
"'!$C$1:$C$" & zQuerySheet1LastRow & ",FALSE))"

zzString2 = "INDIRECT(""'" & zSheet2 & "'!$F$""&MATCH(B" & zReportFirstDataRow & ",'" & _
zSheet2 & "'!$C$1:$C$" & zQuerySheet2LastRow & ",FALSE)&"":" & zQS2LastColString & _
"""&MATCH(B" & zReportFirstDataRow & ",'" & zSheet2 & _
"'!$C$1:$C$" & zQuerySheet2LastRow & ",FALSE))"

zMonths = zCurrentPeriod - zMonthsLag

Select Case zCurrentPeriod

Case Is >= zMonthsLag

zStringa = "SUM(IF(S1R2=""COGS - General"",IF(S1R3=""Actual Qty"",IF(S1R0*1>" & _
zMonths & "," & zzString1 & ",0),0),0))"

zString = "=IF(ISERROR(" & zStringa & "),0," & zStringa & ")"

Case Else

zStringa = "SUM(IF(S1R2=""COGS - General"",IF(S1R3=""Actual Qty""," & zzString1 & ",0),0))"
zStringb = "SUM(IF(S2R2=""COGS - General"",IF(S2R3=""Actual Qty"",IF(S2R0*1>" & _
12 + zMonths & "," & zzString2 & ",0),0),0))"

zString = "=IF(ISERROR(MATCH(B" & zReportFirstDataRow & ",S1C3,FALSE)),0," & zStringa & ")"
zString = zString & "+IF(ISERROR(MATCH(B" & zReportFirstDataRow & _
",S2C3,FALSE)),0," & zStringb & ")"
End Select

zFormulaString6a = zString

End Function

'_____________________________________________________________________________________________
Private Function zFormulaString6() As String
Dim zString As String
Dim zStringa As String
Dim zStringb As String
Dim zzString1 As String
Dim zzString2 As String

Dim zMonths As Long

zzString1 = "INDIRECT(""'" & zSheet1 & "'!$F$""&MATCH(B" & zReportFirstDataRow & ",'" & _
zSheet1 & "'!$C$1:$C$" & zQuerySheet1LastRow & ",FALSE)&"":" & zQS1LastColString & _
"""&MATCH(B" & zReportFirstDataRow & ",'" & zSheet1 & _
"'!$C$1:$C$" & zQuerySheet1LastRow & ",FALSE))"

zzString2 = "INDIRECT(""'" & zSheet2 & "'!$F$""&MATCH(B" & zReportFirstDataRow & ",'" & _
zSheet2 & "'!$C$1:$C$" & zQuerySheet2LastRow & ",FALSE)&"":" & zQS2LastColString & _
"""&MATCH(B" & zReportFirstDataRow & ",'" & zSheet2 & _
"'!$C$1:$C$" & zQuerySheet2LastRow & ",FALSE))"

zMonths = zCurrentPeriod - zMonthsLag

Select Case zCurrentPeriod

Case Is >= zMonthsLag

zStringa = "SUM(IF(S1R2=""COGS - General"",IF(S1R3=""Actual"",IF(S1R0*1>" & zMonths & _
"," & zzString1 & ",0),0),0))"
zString = "=IF(ISERROR(1/AE" & zReportFirstDataRow & "),0," & _
zStringa & "/AE" & zReportFirstDataRow & ")"
' zString = "=IF((" & zString & ")<0,0,(" & zString & "))"

Case Else

zStringa = "SUM(IF(S1R2=""COGS - General"",IF(S1R3=""Actual""," & zzString1 & ",0),0))"
zStringa = "IF(ISERROR(MATCH(B" & zReportFirstDataRow & ",S1C3,FALSE)),0," & zStringa & ")"

zStringb = "SUM(IF(S2R2=""COGS - General"",IF(S2R3=""Actual"",IF(S2R0*1>" & _
12 + zMonths & "," & zzString2 & ",0),0),0))"
zStringb = "IF(ISERROR(MATCH(B" & zReportFirstDataRow & ",S2C3,FALSE)),0," & zStringb & ")"

zString = "=IF(ISERROR(1/AE" & zReportFirstDataRow & "),0,(" & zStringa & "+" & _
zStringb & ")/AE" & zReportFirstDataRow & ")"

' zString = "IF((" & zString & ")<0,0,(" & zString & "))"
End Select

zFormulaString6 = zString

End Function

'________________________________________________________________________________________
Private Sub zFormatReport()
zColumnWidthFormat
zNumberFormat
zColorFormat
zLockedFormat
zCopyFormulas
zFrameFormat
zColumnAlignment
zPrint
End Sub

'________________________________________________________________________________________
Private Sub zColumnWidthFormat()
Dim zColumns() As Variant

zColumns = Array(4.43, 12.43, 12.43, 46.43, 9.43, 20.29, 9.71, 2, 15, 8.43, 15, 2, _
11.14, 10.29, 11.86, 12.86, 11.86, 9.43, 2, 11.86, 15, 15, 2, 11.86, _
15, 15, 2, 15, 15, 8, 8, 8, 2, 11.86, 11.86, 2, 11.86, 15, 15, 2, 11.86, 15, 15)

zFormatColumnWidth zColumns

End Sub

'_____________________________________________________________________________________________
Private Sub zNumberFormat()
Dim zString As String
Dim zF As Long
Dim zT As Long

zF = zReportFirstDataRow
zT = zReportLastDataRow + 2

zFormatNumber "E" & zF, "m/d/yyyy"

'added new col
zString = "I" & zF & ",K" & zF & ",M" & zF & ":Q" & zF & ",U" & zF & ":V" & zF & ",Y" & zF & _
":Z" & zF & ",AB" & zF & ":AC" & zF & ",AL" & zF & ":AM" & zF & ",AP" & zF & ":AQ" & zF
zFormatNumber zString, "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"

'added new col
zString = "I" & zT & ",K" & zT & ",M" & zT & ":Q" & zT & ",U" & zT & ":V" & zT & ",Y" & zT & _
":Z" & zT & ",AB" & zT & ":AC" & zT & ",AL" & zT & ":AM" & zT & ",AP" & zT & ":AQ" & zT
zFormatNumber zString, "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"

zString = "J" & zF
zFormatNumber zString, "0.0%"

zString = "J" & zT
zFormatNumber zString, "0.0%"

zString = "R" & zF
zFormatNumber zString, "0%"

zString = "R" & zT
zFormatNumber zString, "0%"

'added new col
zString = "T" & zF & ",X" & zF & ",AH" & zF & ",AI" & zF & ",AK" & zF & ",AO" & zF
zFormatNumber zString, "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"

'added new col
zString = "T" & zT & ",X" & zT & ",AH" & zT & ",AI" & zT & ",AK" & zT & ",AO" & zT
zFormatNumber zString, "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"

'added
zString = "AK" & zF & ",AO" & zF
zFormatNumber zString, "_(* #,##0.0000_);_(* (#,##0.0000);_(* ""-""??_);_(@_)"

'added
zString = "AK" & zT & ",AO" & zT
zFormatNumber zString, "_(* #,##0.0000_);_(* (#,##0.0000);_(* ""-""??_);_(@_)"

End Sub

'_____________________________________________________________________________________________
Private Sub zColorFormat()
Dim zString As String
Dim zF As Long

zF = zReportFirstDataRow

'added new col
zString = "T" & zF & ":T" & zReportLastDataRow & ",X" & zF & ":X" & zReportLastDataRow & ",ak" & zF & ":ak" & zReportLastDataRow & ",ao" & zF & ":ao" & zReportLastDataRow

zFormatBackColor zString, 36

End Sub

'_____________________________________________________________________________________________
Private Sub zLockedFormat()
Dim zString As String
Dim zF As Long

zF = zReportFirstDataRow

'added new columns
zString = "T" & zF & ",X" & zF & ",AK" & zF & ",AO" & zF
zFormatLocked zString, False

End Sub

'_____________________________________________________________________________________________
Private Sub zCopyFormulas()
Dim zF As Long
Dim zL As Long

zF = zReportFirstDataRow
zL = zReportLastDataRow

zCopyFormulaAsValue "E" & zF, "E" & zF + 1 & ":E" & zL
zCopyFormulaAsValue "I" & zF & ":AI" & zF, "I" & zF + 1 & ":AI" & zL

zCopyFormula "U" & zF & ":V" & zF, "U" & zF + 1 & ":V" & zL
zCopyFormula "Y" & zF & ":Z" & zF, "Y" & zF + 1 & ":Z" & zL
zCopyFormula "AB" & zF & ":AC" & zF, "AB" & zF + 1 & ":AC" & zL

'added
zCopyFormula "AK" & zF & ":AM" & zF, "AK" & zF + 1 & ":AM" & zL
zCopyFormula "AO" & zF & ":AQ" & zF, "AO" & zF + 1 & ":AQ" & zL

End Sub

'_____________________________________________________________________________________________
Private Sub zFrameFormat()
Dim zString As String
Dim zT As String
Dim zF As Long
Dim zL As Long

zF = zReportFirstDataRow
zL = zReportLastDataRow
zT = zReportLastDataRow + 2

'added new col
zString = "B10:G10,I10:K10,M10:R10,T10:V10,X10:Z10,AB10:AC10,AH10:AI10,ak10:am10,ao10:aq10"

zFormatTDV zString

'added new col
zString = "B" & zF & ":G" & zL & ",I" & zF & ":k" & zL & ",M" & zF & ":R" & zL & ",T" & zF & _
":V" & zL & ",X" & zF & ":Z" & zL & ",AB" & zF & ":AC" & zL & ",AH" & zF & ":AI" & zL & ",Ak" & zF & ":Am" & zL & ",Ao" & zF & ":Aq" & zL

zFormatTDV zString

'added new col
zString = "I" & zT & ":K" & zT & ",M" & zT & ":R" & zT & ",T" & zT & ":V" & zT & ",X" & zT & _
":Z" & zT & ",AB" & zT & ":AC" & zT & ",AH" & zT & ":AI" & zT & ",Ak" & zT & ":Am" & zT & ",Ao" & zT & ":Aq" & zT

zFormatTDD zString

End Sub

'________________________________________________________________________________________
Private Sub zColumnAlignment()
Range("G" & zReportFirstDataRow & ":G" & zReportLastDataRow).HorizontalAlignment = xlCenter
End Sub

'________________________________________________________________________________________
Private Function zCurrentQuarterPeriod()

Select Case zCurrentPeriod

Case 1 To 3
zCurrentQuarterPeriod = 1
Case 4 To 6
zCurrentQuarterPeriod = 4
Case 7 To 9
zCurrentQuarterPeriod = 7
Case 10 To 12
zCurrentQuarterPeriod = 10
End Select

End Function

'_____________________________________________________________________________________________
Private Sub zTotalFormulas()
Dim zF As Long
Dim zL As Long
Dim zT As Long
Dim zColumns() As Variant 'ZG 6/10/08

zF = zReportFirstDataRow 'ZG 5/13/08
zL = zReportLastDataRow
zT = zReportLastDataRow + 2

'added new col
zColumns = Array("I", "K", "M", "N", "O", "P", "Q", "U", "V", "Y", "Z", "AB", "AC", "AL", "AM", "AP", "AQ") 'ZG 6/5/08
zRangeSum zColumns, zT, zF, zL 'ZG 6/10/08

Range("J" & zT) = "=IF(ISERROR(K" & zT & "/I" & zT & "),0,K" & zT & "/I" & zT & ")"
Range("R" & zT) = "=IF(ISERROR(-P" & zT & "/Q" & zT & "),0,-P" & zT & "/Q" & zT & ")"
Range("X" & zT) = "=IF(ISERROR(Y" & zT & "/K" & zT & "),0,Y" & zT & "/K" & zT & ")"
Range("T" & zT) = "=IF(ISERROR(U" & zT & "/K" & zT & "),0,U" & zT & "/K" & zT & ")"
Range("X" & zT) = "=IF(ISERROR(Y" & zT & "/K" & zT & "),0,Y" & zT & "/K" & zT & ")"
Range("AH" & zT) = "=IF(ISERROR(U" & zT & "/K" & zT & "),0,U" & zT & "/K" & zT & ")"
Range("AI" & zT) = "=IF(ISERROR(Y" & zT & "/K" & zT & "),0,Y" & zT & "/K" & zT & ")"

'added
Range("AK" & zT) = "=IF(ISERROR(AL" & zT & "/K" & zT & "),0,AL" & zT & "/K" & zT & ")"
Range("AO" & zT) = "=IF(ISERROR(AP" & zT & "/K" & zT & "),0,AP" & zT & "/K" & zT & ")"

With Range("B" & zT)
.Value = "Grand Total"
.Font.FontStyle = "Bold"
End With

Range("I" & zT & ":AQ" & zT).Font.Bold = True

End Sub

'________________________________________________________________________________________
Private Sub zPrint()
Dim zPrintArea As String

zPrintArea = Range("A1:AQ" & zReportLastDataRow + 3).Address

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$11"
.PrintTitleColumns = "$A:$G"
End With
ActiveSheet.PageSetup.PrintArea = zPrintArea
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&Z&F&A"
.CenterFooter = "&BThe Walt Disney Company Confidential&B"
.RightFooter = "&P of &N" & Chr(10) & "&D&T"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
' .PrintQuality = 90
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlOverThenDown
.BlackAndWhite = False
.Zoom = 35
.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub
'842, 826

Trebor76 · Sep 19, 2011

Holy toledo Batman!!!

You should always use code tags (as I have done) when posting scripts, especially that much code!!

I think the problem is to do with the use of SendKeys (generally not advised) trying to convert various formula ranges to arrays in the "zPopulateReport" macro.

As such, try this revised version of that macro:

Code:

Private Sub zPopulateReport()
Dim i As Long
Dim zF As Long
Dim zRowOffset As Integer

zRowOffset = zReportFirstDataRow - 1
zRecordset.MoveFirst

For i = 1 To zRecordset.RecordCount
Cells(i + zRowOffset, 1) = i
Cells(i + zRowOffset, 2) = zRecordset!zMPMIssue
Cells(i + zRowOffset, 3) = zRecordset!zMediaProduct
Cells(i + zRowOffset, 4) = zRecordset!zTitle
Cells(i + zRowOffset, 6) = zRecordset!zCBLCategory
Cells(i + zRowOffset, 7) = zRecordset!zformat
zRecordset.MoveNext
Next

zReportLastDataRow = i - 1 + zRowOffset
zF = zReportFirstDataRow

Range("E" & zF) = "=IF(ISERROR(VALUE(VLOOKUP(C" & zF & ",'Release Dates'!$A$" & _
zRDatesDataRow & ":$E$" & zRDatesLastRow & _
",5,FALSE))),"""",VALUE(VLOOKUP(C" & zF & ",'Release Dates'!$A$" & _
zRDatesDataRow & ":$E$" & zRDatesLastRow & ",5,FALSE)))"

Range("I" & zF).FormulaArray = zFormulaString1

'Range("I" & zF).Select
'SendKeys ("{F2}")
'SendKeys "^+{ENTER}", True

Range("J" & zF) = zFormulaString2
Range("K" & zF) = "=IF(I" & zF & "<0,0,Round(I" & zF & "*J" & zF & ",0))"

Range("M" & zF).FormulaArray = zFormulaString3
'Range("M" & zF).Select
'SendKeys ("{F2}")
'SendKeys "^+{ENTER}", True

Range("N" & zF) = "=ROUND(K" & zF & "-M" & zF & ",0)"

Range("O" & zF).FormulaArray = zFormulaString4("Actual Returns")
'Range("O" & zF).Select
'SendKeys ("{F2}")
'SendKeys "^+{ENTER}", True

Range("P" & zF) = "=O" & zF & "-N" & zF

Range("Q" & zF).FormulaArray = zFormulaString4("Gross Revenues")
'Range("Q" & zF).Select
'SendKeys ("{F2}")
'SendKeys "^+{ENTER}", True

Range("R" & zF) = "=IF(ISERROR(-P" & zF & "/Q" & zF & "),0,-P" & zF & "/Q" & zF & ")"

Application.ScreenUpdating = True

Range("AD" & zF).FormulaArray = zFormulaString5a
'Range("AD" & zF).Select
'SendKeys ("{F2}")
'SendKeys "^+{ENTER}", True

Range("T" & zF).FormulaArray = zFormulaString5
'Range("T" & zF).Select
'SendKeys ("{F2}")
'SendKeys "^+{ENTER}", True

Range("U" & zF) = "=ROUND(K" & zF & "*T" & zF & ",2)"
Range("V" & zF) = "=U" & zF & "*$V$4"

Range("AE" & zF).FormulaArray = zFormulaString6a
'Range("AE" & zF).Select
'SendKeys ("{F2}")
'SendKeys "^+{ENTER}", True

Range("AF" & zF).FormulaArray = zFormulaString6
'Range("AF" & zF).Select
'SendKeys ("{F2}")
'SendKeys "^+{ENTER}", True

Range("X" & zF) = "=IF(AF12<0,0,AF12)"

Range("Y" & zF) = "=ROUND(K" & zF & "*X" & zF & ",2)"
Range("Z" & zF) = "=Y" & zF & "*$V$4"
Range("AB" & zF) = "=P" & zF & "*T" & zF
Range("AC" & zF) = "=AB" & zF & "*$V$4"

Range("AH" & zF).FormulaArray = zFormulaString5
'Range("AH" & zF).Select
'SendKeys ("{F2}")
'SendKeys "^+{ENTER}", True

Range("AI" & zF) = "=IF(AF12<0,0,AF12)"

'added
Range("AL" & zF) = "=K" & zF & "* AK" & zF
Range("AM" & zF) = "=AL" & zF & "*$V$4"

Range("AP" & zF) = "=K" & zF & "* AO" & zF
Range("AQ" & zF) = "=AP" & zF & "*$V$4"

Application.ScreenUpdating = False
End Sub

HotNumbers · Sep 19, 2011

THANKS A MILLION. I had one developer who programmed like that, hence he is no longer with me.

I will try this and keep you posted.

Array Formula Issue Between XP and WINDOWS7

HotNumbers

Well-known Member

Excel Facts

Trebor76

Well-known Member

Yahya

Well-known Member

HotNumbers

Well-known Member

Trebor76

Well-known Member

HotNumbers

Well-known Member

Trebor76

Well-known Member

HotNumbers

Well-known Member

Trebor76

Well-known Member

HotNumbers

Well-known Member

Similar threads

Forum statistics

Share this page

Array Formula Issue Between XP and WINDOWS7

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock