Combine numeric values in excel cells based on a loop 1

[tech84]

I've got a spreadsheet with data that's imported from an Access database. The data is comprised of eight numbers, filling in columns A through H, through row 422. There's a number in the H column for each row which indicates what type of data it is.

Here's what I'd like to do: There are 13 different types of data that I'm tracking, indicated by the numbers 10 - 22. What I need to do is create a When/Wend loop that will go down through column H, find a cell that contains the number 10, takes the value from the F column in this same row and stores it. And I want it to keep doing that until it reaches row 422. The result is to be inserted into another cell.

Here's the code I have so far:

Sub Array_count()
Dim Cell As Integer
Dim Index As Integer
Index = 1

While (Index <= 422)
If Sheet1.Range(&quot;Hindex&quot = &quot;10&quot; Then Cell = (Cell + Sheet1.Range(&quot;Findex&quot)

Index = Index + 1
Wend

Sheet1.Range(&quot;K4&quot.Value = Cell

End Sub

This works if I put actual numbers where index is in the code, but I can't figure out how to put the incrementing value, index, in place of the cell number. Can someone please throw me a bone here?

Thanks!

Mike

 

[Zathras]

You could use
[blue]

Sub test()
Dim c As Range
Dim nTotal As Long
  With Worksheets("Sheet1")
    For Each c In Intersect(.UsedRange, .Range("H:H"))
      If c.Value = 10 Then
        nTotal = nTotal + c.Offset(0, -2)
      End If
    Next c
    .Range("K4") = nTotal
  End With
End Sub

[/color]

But, why not just use
[blue]

  [K4] = WorksheetFunction.SumIf(Range("H:H"), 10, Range("F:F"))

[/color]

 

[tech84]

Zathras,

That code at the top does exactly what I was looking for!! I'm not sure how to use the part at the bottom (the [K4] part). I apologize, but I know practically nothing about VBA. I just got the bit of bad code here from reading the help files. How can I make the [K4] part work? Where is that inserted, in the K4 cell, or in the Visual Basic editor? Sorry for asking dumb questions!

Thanks!

Mike

 

[Zathras]

It would go in place of everything between the Sub and End Sub (in the VBA editor):
[blue]

Sub test()
  [K4] = WorksheetFunction.SumIf(Range("H:H"), 10, Range("F:F"))
End Sub

[/color]
But be aware, I have heard of some versions of Excel that don't support the [Xn] syntax that way. So if that doesn't work, try it this way:
[blue]

Sub test1()
  Range("K4") = WorksheetFunction.SumIf(Range("H:H"), 10, Range("F:F"))
End Sub

[/color]
At the risk of confusing you, here is a third possibility: You could put a formula into cell K4 so that if data are changed, the sum would be automatically adjusted:
[blue]

Sub test2()
  Range("K6").Formula = "=SUMIF(H:H,10,F:F)"
End Sub

[/color]

 

[tech84]

Awesome! Thank you so much, Zathras! Now for one last bit... I have to execute this code for all 13 types of imported data. In other words, cells K4 - K16, using data-type numbers 10 - 22. Is there some way to execute all of these &quot;macros&quot; with the push of one button control?

Thanks again!!

Mike

 

[Zathras]

You can just do something like this:
[blue]

Sub test4()
Dim i As Integer
  For i = 10 To 22
    Range("K" & i - 6) = WorksheetFunction.SumIf(Range("H:H"), i, Range("F:F"))
  Next i
End Sub

[/color]
or if you want worksheet functions in place of hard-coded sums:
[blue]

Sub test5()
Dim i As Integer
  For i = 10 To 22
    Range("K" & i - 6).Formula = "=SUMIF(H:H," & i & ",F:F)"
  Next i
End Sub

[/color]
Have you considered using a Pivot Table?

 

[tech84]

Zathras,

Thanks again for all your help! The other Tech and I at work don't know much of anything about pivot tables, but I'm sure we'll start learning about them soon.

We went with the sub test() in your second post, and it works beautifully! The sub test 4() would have been perfect if we were using 10 - 22 for our data-types, but we decided to use text instead. For example, our data is seperated into types like DASC, DANO, SASC, SANO, etc. It doesn't make any sense here, but it does in our spreadsheet.

Anyway, we learned a lot about VBA while working on this spreadsheet this week. Thanks again!



Mike

 

[tech84]

Zathras,

I changed my mind again. I figured out how to get the code to work in fewer lines, as in your last example. Here's the actual code from our spreadsheet:

Sub FridayRefreshLayout()
Dim i As Integer
For i = 3 To 16
Range(&quot;L&quot; & i) = WorksheetFunction.SumIf(Range(&quot;H:H&quot, Range(&quot;J&quot; & i), Range(&quot;F:F&quot)
Next i
End Sub

I knew there had to be a simpler way! Before, I had a separate line of code for each data type, as represented in cells j3 - j16 ( the DASC, DANO, etc. from above). I guess I just needed to get away from it for a couple of days and start fresh before it would sink into my thick skull.

Thanks yet again!

Mike