• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 45
  • Last Modified:

part 2 - array

o array does not equal 8,18,54 for each sheet ...instead x,yx for each sheet are based on the following

x = where in col A this value exisits, "Total Income"
y = where in col A this value exisits, "Gross Profit"
z = where in col A this value exisits, "Net Income"
  • 3
  • 2
1 Solution
NorieVBA ExpertCommented:
Which column(s) will 'Total Income', 'Gross Profit' and 'Net Income' appear in?
finnstoneAuthor Commented:
col A
NorieVBA ExpertCommented:
That's the thing, based on the example file you uploaded earlier they aren't all in the same column.

You might see that if you run this updated code.
Sub GetTotals()
Dim wsTotals As Worksheet
Dim strShName As String
Dim I As Long
Dim J As Long
Dim K As Long
Dim col As Long
Dim arrCols As Variant
Dim arrRows As Variant
Dim Res As Variant

    Set wsTotals = Sheets.Add

    wsTotals.Range("B1:D1").Value = Array("ROW8", "ROW18", "ROW54")

    arrCols = Array("HX:SX", "TX:AEX", "AFX:AQX", "ARX:BCX", "BDX:BOX", "BPX:CAX")
    arrRows = Array("Total Income", "Gross Profit", "Net Income")

    For I = 1 To 50
        strShName = "CA" & Format(I, "000")
        col = 2
        With wsTotals
            .Cells(I + 2, 1).Value = strShName
            If Evaluate("ISREF('" & strShName & "'!A1)") Then
                For J = LBound(arrCols) To UBound(arrCols)
                    For K = LBound(arrRows) To UBound(arrRows)
                        Res = Application.Match(arrRows(K), Sheets(strShName).Columns(1), 0)
                        If Not IsError(Res) Then
                            .Cells(I + 2, col).Formula = "=SUM(" & strShName & "!" & Replace(arrCols(J), "X", Res) & ")"
                        End If
                        col = col + 1
                    Next K
                Next J
                .Cells(I + 2, 2).Resize(, 3).Value = "#N/A"
                End If
        End With
    Next I

End Sub

Open in new window

finnstoneAuthor Commented:
i though i only gave you one tab. somehow you knew anyways!
finnstoneAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now