Reporting with arrays

A

Alejandro

Hello all,

I am trying to break down a large sheet of data [jobs for a construction
company] down to a report per estimator in the list. I have a way of doing
it, but it seems a little bulky. Here is how I am doing it now:

Sub collect_est_data()

Dim est1(1 To 33), est2(1 To 33), est3(1 To 33), est4(1 To 33), est5(1 To
33), est6(1 To 33), est7(1 To 33), est8(1 To 33), est9(1 To 33), est10(1 To
33), est11(1 To 33), est12(1 To 33), est13(1 To 33), est14(1 To 33) As Range

For Each i In Worksheets("Bid Card").Range("P3:p1000")
For j = 18 To 46 Step 4
If Worksheets("Bid Card").Cells(i.Row, j).Value <> "" Then
Select Case Worksheets("Bid Card").Cells(i.Row, j).Value
Case "Estimator name1"
Select Case j
Case 18 ' Mechanical
est1(1) = est1(1) + 1
est1(2) = est1(2) + 1
est1(3) = est1(3) + Worksheets("Bid Card").Cells(i.Row,
j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) <> 0 Then
est1(4) = est1(4) + 1
est1(5) = est1(5) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 22 ' Electrical
est1(1) = est1(1) + 1
est1(6) = est1(6) + 1
est1(7) = est1(7) + Worksheets("Bid Card").Cells(i.Row,
j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) <> 0 Then
est1(8) = est1(8) + 1
est1(9) = est1(9) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 26 ' Piping
est1(1) = est1(1) + 1
est1(10) = est1(10) + 1
est1(11) = est1(11) + Worksheets("Bid
Card").Cells(i.Row, j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) <> 0 Then
est1(12) = est1(12) + 1
est1(13) = est1(13) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 30 ' Fab
est1(1) = est1(1) + 1
est1(14) = est1(14) + 1
est1(15) = est1(15) + Worksheets("Bid
Card").Cells(i.Row, j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) <> 0 Then
est1(16) = est1(16) + 1
est1(17) = est1(17) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 34 ' Rental
est1(1) = est1(1) + 1
est1(18) = est1(18) + 1
est1(19) = est1(19) + Worksheets("Bid
Card").Cells(i.Row, j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) <> 0 Then
est1(20) = est1(20) + 1
est1(21) = est1(21) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 38 ' Sub
est1(1) = est1(1) + 1
est1(22) = est1(22) + 1
est1(23) = est1(23) + Worksheets("Bid
Card").Cells(i.Row, j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) <> 0 Then
est1(24) = est1(24) + 1
est1(25) = est1(25) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 42 ' Engineering
est1(1) = est1(1) + 1
est1(26) = est1(26) + 1
est1(27) = est1(27) + Worksheets("Bid
Card").Cells(i.Row, j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) <> 0 Then
est1(28) = est1(28) + 1
est1(29) = est1(29) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 46 ' Civil
est1(1) = est1(1) + 1
est1(30) = est1(30) + 1
est1(31) = est1(31) + Worksheets("Bid
Card").Cells(i.Row, j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) <> 0 Then
est1(32) = est1(32) + 1
est1(33) = est1(33) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
End Select
Case "Estimator Name2"
[Inserts values into the array]
End Select
End Select
End If
Next j
Next

End Sub

And so on and so forth until all the estimators have been completed. This
just seems a little large in code, and I was thinking of writing a function,
but for some reason couldn't get it to dynamically pass the array I needed
to dump to. After all of this is collected, it will get put into a sheet in
the workbook, which is a block of data for each person.

Is my thought process even right here?

Thanks for any help,
-=Alejandro
 
F

Fredrik Wahlgren

Alejandro said:
Hello all,

I am trying to break down a large sheet of data [jobs for a construction
company] down to a report per estimator in the list. I have a way of doing
it, but it seems a little bulky. Here is how I am doing it now:

Sub collect_est_data()

Dim est1(1 To 33), est2(1 To 33), est3(1 To 33), est4(1 To 33), est5(1 To
33), est6(1 To 33), est7(1 To 33), est8(1 To 33), est9(1 To 33), est10(1 To
33), est11(1 To 33), est12(1 To 33), est13(1 To 33), est14(1 To 33) As Range

For Each i In Worksheets("Bid Card").Range("P3:p1000")
For j = 18 To 46 Step 4
If Worksheets("Bid Card").Cells(i.Row, j).Value <> "" Then
Select Case Worksheets("Bid Card").Cells(i.Row, j).Value
Case "Estimator name1"
Select Case j
Case 18 ' Mechanical
est1(1) = est1(1) + 1
est1(2) = est1(2) + 1
est1(3) = est1(3) + Worksheets("Bid Card").Cells(i.Row,
j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) <> 0 Then
est1(4) = est1(4) + 1
est1(5) = est1(5) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 22 ' Electrical
est1(1) = est1(1) + 1
est1(6) = est1(6) + 1
est1(7) = est1(7) + Worksheets("Bid Card").Cells(i.Row,
j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) <> 0 Then
est1(8) = est1(8) + 1
est1(9) = est1(9) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 26 ' Piping
est1(1) = est1(1) + 1
est1(10) = est1(10) + 1
est1(11) = est1(11) + Worksheets("Bid
Card").Cells(i.Row, j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) <> 0 Then
est1(12) = est1(12) + 1
est1(13) = est1(13) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 30 ' Fab
est1(1) = est1(1) + 1
est1(14) = est1(14) + 1
est1(15) = est1(15) + Worksheets("Bid
Card").Cells(i.Row, j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) <> 0 Then
est1(16) = est1(16) + 1
est1(17) = est1(17) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 34 ' Rental
est1(1) = est1(1) + 1
est1(18) = est1(18) + 1
est1(19) = est1(19) + Worksheets("Bid
Card").Cells(i.Row, j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) <> 0 Then
est1(20) = est1(20) + 1
est1(21) = est1(21) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 38 ' Sub
est1(1) = est1(1) + 1
est1(22) = est1(22) + 1
est1(23) = est1(23) + Worksheets("Bid
Card").Cells(i.Row, j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) <> 0 Then
est1(24) = est1(24) + 1
est1(25) = est1(25) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 42 ' Engineering
est1(1) = est1(1) + 1
est1(26) = est1(26) + 1
est1(27) = est1(27) + Worksheets("Bid
Card").Cells(i.Row, j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) <> 0 Then
est1(28) = est1(28) + 1
est1(29) = est1(29) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
Case 46 ' Civil
est1(1) = est1(1) + 1
est1(30) = est1(30) + 1
est1(31) = est1(31) + Worksheets("Bid
Card").Cells(i.Row, j + 1).Value
If Worksheets("Bid Card").Cells(i.Row, j + 2) <> 0 Then
est1(32) = est1(32) + 1
est1(33) = est1(33) + Worksheets("Bid
Card").Cells(i.Row, j + 2).Value
End If
End Select
Case "Estimator Name2"
[Inserts values into the array]
End Select
End Select
End If
Next j
Next

End Sub

And so on and so forth until all the estimators have been completed. This
just seems a little large in code, and I was thinking of writing a function,
but for some reason couldn't get it to dynamically pass the array I needed
to dump to. After all of this is collected, it will get put into a sheet in
the workbook, which is a block of data for each person.

Is my thought process even right here?

Thanks for any help,
-=Alejandro
Click to expand...

Here's a site that I found with a downloadable sheet with lots of array
functions. I think this is close to what you need.
http://home.pacbell.net/beban/

/Fredrik
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Variable error on second use??? 7
Jump to new line after the first record is displayed 1
Copy 18 Cells in one row 3
VBA code erroring in XL97... 1
Simple IF give me nothing? 2
InputBox for column letter problem 2
Combine three InputBoxes into one? 7
How do I make this string bold? 1

Top