I forgot to mention the variable which had zeroes in them is rs.Cells(J,
18).Value = .AMVar can u tel me the d/b

"TKM" wrote:

I have been debugging to see where the value are coming from (3 columns) that
has a zero.
Right now the issue is that in the excel sheet, one set of the rows has null
value/zero, and I dont know what database returns the zero value. Since
there is no documentation, It makes it tuff to locate. Need help quick so any
of you pros that are up to the challange I would really apprecaite it.

Type SortType
Ordered As Integer
SortedA As Integer
SortedD As Integer
End Type
Type ColType
ColNum As Integer
Header As String
MinWidth As Double
End Type
Type ProdType2
ProdName As String
Fiscal As Integer
Detail As Boolean
ProdDet() As String
End Type
Type RptMemberType
Rep() As String
Prods() As ProdType2
Div() As String
Location() As String
End Type
Type RptDataType
Skip As Boolean
Pos As Integer
Rep As String
Loc As String
Div As String
Prod As String
Detail As Boolean
Indent As String
FLY As Double
FTY As Double
FVar As Double
FVarPer As Double
LYM As Double
TYM As Double
MVar As Double
MVarPer As Double
SL As Double
SLVar As Double
SLVarPer As Double
LYNM As Double
AFLY As Integer
AFTY As Integer
AFVar As Integer
AFVP As Double
AMLY As Integer
AMTY As Integer
AMVar As Integer
AMVP As Double
End Type
Type FlashBackType
Mbrs As RptMemberType
Data() As RptDataType
ReportType As Integer
End Type
Public Const MINIMUMINTEGER = 6#
Public Const MINIMUMPERCENT = 5#
Public Const MINIMUMDOUBLE = 7.5
Const SPACES = " "
Dim lstMyProds() As String
Public intFlashLevel As Integer
Public ReportMembers As RptMemberType
Public ReportData() As RptDataType
Public Flashback() As FlashBackType
Public gstrMyMeasure As String
Public gstrMyScenario As String
Public gstrMyCompany As String
Public gstrMyManager As String
Public gintMyCompany As Integer
Public blnPMReport As Boolean
Public gintReportType As Integer
Public gintLastRow As Integer
Public gstrTYMonth As String
Public gstrLYMonth As String
Public gstrLYNextMonth As String
Public shtD As Worksheet
Public shtA As Worksheet
Public shtX As Worksheet
Public shtS As Worksheet
Dim lstReps() As SortType
Dim lstLocs() As SortType
Dim lstDivs() As SortType
Dim lstPrds() As SortType
Public arrMeasures() As Integer
Dim intLastSortRow As Integer
Dim intSortDat As Integer
Public gintOriginalReportType As Integer
Public blnDivisionLevel As Boolean
Public strDetailHeader As String
Sub GetSortMemberLists()
Dim i, J, k, L As Integer
Set shtS = ThisWorkbook.Worksheets("shtSort")
shtS.Activate
shtS.Cells.Clear
J = UBound(ReportMembers.Rep)
ReDim lstReps(J)
If J 1 Then
L = J - 1
For i = 1 To L
lstReps(i).Ordered = i
shtS.Cells(i, 1).Value = ReportMembers.Rep(i)
shtS.Cells(i, 2).Value = i
Next i
shtS.Range("A1:B" & CStr(L)).Select
selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For i = 1 To L
k = shtS.Cells(i, 2).Value
lstReps(k).SortedA = i
Next i
selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For i = 1 To L
k = shtS.Cells(i, 2).Value
lstReps(k).SortedD = i
Next i
lstReps(J).Ordered = J
lstReps(J).SortedA = J
lstReps(J).SortedD = J
Else
lstReps(1).Ordered = 1
lstReps(1).SortedA = 1
lstReps(1).SortedD = 1
End If
shtS.Cells.Clear
J = UBound(ReportMembers.Location)
ReDim lstLocs(J)
If J 1 Then
For i = 1 To J
lstLocs(i).Ordered = i
shtS.Cells(i, 1).Value = ReportMembers.Location(i)
shtS.Cells(i, 2).Value = i
Next i
shtS.Range("A1:B" & CStr(J)).Select
selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For i = 1 To J
k = shtS.Cells(i, 2).Value
lstLocs(k).SortedA = i
Next i
selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For i = 1 To J
k = shtS.Cells(i, 2).Value
lstLocs(k).SortedD = i
Next i
Else
lstLocs(1).Ordered = 1
lstLocs(1).SortedA = 1
lstLocs(1).SortedD = 1
End If
shtS.Cells.Clear
J = UBound(ReportMembers.Div)
ReDim lstDivs(J)
If J 1 Then
For i = 1 To J
lstDivs(i).Ordered = i
shtS.Cells(i, 1).Value = ReportMembers.Div(i)
shtS.Cells(i, 2).Value = i
Next i
shtS.Range("A1:B" & CStr(J)).Select
selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For i = 1 To J
k = shtS.Cells(i, 2).Value
lstDivs(k).SortedA = i
Next i
selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For i = 1 To J
k = shtS.Cells(i, 2).Value
lstDivs(k).SortedD = i
Next i
Else
lstDivs(1).Ordered = 1
lstDivs(1).SortedA = 1
lstDivs(1).SortedD = 1
End If
shtS.Cells.Clear
J = UBound(ReportMembers.Prods)
ReDim lstPrds(J)
If J 1 Then
For i = 1 To J
lstPrds(i).Ordered = i
shtS.Cells(i, 1).Value = ReportMembers.Prods(i).ProdName
shtS.Cells(i, 2).Value = i
Next i
shtS.Range("A1:B" & CStr(J)).Select
selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For i = 1 To J
k = shtS.Cells(i, 2).Value
lstPrds(k).SortedA = i
Next i
selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For i = 1 To J
k = shtS.Cells(i, 2).Value
lstPrds(k).SortedD = i
Next i
Else
lstPrds(1).Ordered = 1
lstPrds(1).SortedA = 1
lstPrds(1).SortedD = 1
End If

End Sub
Sub GetTopMembers()
Dim i, J, k As Integer
Dim S As Worksheet
Set S = ThisWorkbook.Worksheets("Connect")
If MyAccess.Prods(1).Access And MyAccess.Prods(2).Access Then
strTopProduct = gconALL_SUPPLIERS
ElseIf MyAccess.Prods(1).Access Then
strTopProduct = MyAccess.Prods(1).Name
Else
strTopProduct = MyAccess.Prods(2).Name
End If
S.Cells.Clear
i = 1
S.Cells(1, 2).Value = gstrMyCompany
S.Cells(1, 3).Value = conTIME
S.Cells(1, 4).Value = strTopProduct
S.Cells(1, 5).Value = conMEASURES
S.Cells(1, 6).Value = conSCENARIO
S.Cells(1, 7).Value = conALL_DIVISIONS
S.Cells(1, 8).Value = conLOCATION
Do While ms.Cells(i, 55).Value < ""
S.Cells(i + 1, 1).Value = ms.Cells(i, 55).Value
i = i + 1
Loop
gblnEssbaseOK = EssbaseSetSheetOptions("Connect", 2, False, "0", True,
False, NO_ACCESS, False)
gblnEssbaseOK = EssbaseRetrieve(gstrAppBusrev, gstrDBBusrev, "Connect", "",
1, False, "GetTopMembers", False)
If gblnEssbaseOK Then
ReDim strTopRep(0)
For J = 2 To i - 1
If InStr(S.Cells(J, 1).Value, " ") < 1 Then
k = k + 1
ReDim Preserve strTopRep(k)
strTopRep(k) = S.Cells(J, 1).Value
End If
Next J
End If
End Sub
Sub RestoreLastReport()
Dim i, J, k, L, M As Integer
With Flashback(intFlashLevel)
gintReportType = .ReportType
k = UBound(.Mbrs.Div)
ReDim ReportMembers.Div(k)
For i = 1 To k
ReportMembers.Div(i) = .Mbrs.Div(i)
Next i
k = UBound(.Mbrs.Location)
ReDim ReportMembers.Location(k)
For i = 1 To k
ReportMembers.Location(i) = .Mbrs.Location(i)
Next i
k = UBound(.Mbrs.Rep)
ReDim ReportMembers.Rep(k)
For i = 1 To k
ReportMembers.Rep(i) = .Mbrs.Rep(i)
Next i
k = UBound(.Mbrs.Prods)
ReDim ReportMembers.Prods(k)
For i = 1 To k
If .Mbrs.Prods(i).Detail Then
J = UBound(.Mbrs.Prods(i).ProdDet)
ReDim ReportMembers.Prods(i).ProdDet(J)
For L = 1 To J
ReportMembers.Prods(i).ProdDet(L) = .Mbrs.Prods(i).ProdDet(L)
Next L
End If
ReportMembers.Prods(i).Detail = .Mbrs.Prods(i).Detail
ReportMembers.Prods(i).Fiscal = .Mbrs.Prods(i).Fiscal
ReportMembers.Prods(i).ProdName = .Mbrs.Prods(i).ProdName
Next i
M = UBound(.Data)
ReDim ReportData(M)
For i = 1 To M
ReportData(i).AFLY = .Data(i).AFLY
ReportData(i).AFTY = .Data(i).AFTY
ReportData(i).AFVar = .Data(i).AFVar
ReportData(i).AFVP = .Data(i).AFVP
ReportData(i).AMLY = .Data(i).AMLY
ReportData(i).AMTY = .Data(i).AMTY
ReportData(i).AMVar = .Data(i).AMVar
ReportData(i).AMVP = .Data(i).AMVP
ReportData(i).Detail = .Data(i).Detail