Intersect multiple rows and columns then SUM them

Question

so I'm working on an excel macro that simply prints values from external data to another sheet. The column and row headers can change position based on the external data so I used find then intersect to get the values I needed. It works well enough until I needed to print the SUM of multiple values. I'm also using option buttons to change what rows the values print to based on which is checked. An example of the working code:

Sub XXXXTest()
Application.ScreenUpdating = False
Dim ToutControl As Integer

'XXXX
'Selects XXXX total by finding string headers then intersecting
Dim xPath As String
Dim xTot As String
Dim xCol As Range
Dim xRow As Range

ActiveWorkbook.Sheets("Rodeo1").Activate

xPath = "Rowheader"
xTot = "ColumnHeader"

Set xRow = Columns(1).Find(xPath)
If xRow Is Nothing Then GoTo NextB
Set xCol = Rows(1).Find(xTot)
If xCol Is Nothing Then msg = msg & vbLf & xTot
If msg = "" Then
    Intersect(xRow.EntireRow, xCol.EntireColumn).Activate
Else

End If


'Looks at option button control value and depending on said value, will write Trans value from above in different rows
ToutControl = Sheets("Trans Out").Range("$A$3").Value

If ToutControl = 1 Then
    Sheets("Trans Out").Range("$I$4").Value = ActiveCell.Value
ElseIf ToutControl = 2 Then
    Sheets("Trans Out").Range("$I$5").Value = ActiveCell.Value
ElseIf ToutControl = 3 Then
    Sheets("Trans Out").Range("$I$6").Value = ActiveCell.Value
ElseIf ToutControl = 4 Then
    Sheets("Trans Out").Range("$I$7").Value = ActiveCell.Value
ElseIf ToutControl = 5 Then
    Sheets("Trans Out").Range("$I$8").Value = ActiveCell.Value
ElseIf ToutControl = 6 Then
    Sheets("Trans Out").Range("$I$9").Value = ActiveCell.Value
ElseIf ToutControl = 7 Then
    Sheets("Trans Out").Range("$I$10").Value = ActiveCell.Value
ElseIf ToutControl = 8 Then
    Sheets("Trans Out").Range("$I$11").Value = ActiveCell.Value
ElseIf ToutControl = 9 Then
    Sheets("Trans Out").Range("$I$12").Value = ActiveCell.Value
ElseIf ToutControl = 10 Then
    Sheets("Trans Out").Range("$I$13").Value = ActiveCell.Value
ElseIf ToutControl = 11 Then
    Sheets("Trans Out").Range("$I$14").Value = ActiveCell.Value
Else: MsgBox "Please Select A Time"
    End If

How would I go about doing the same thing but with multiple rows intersecting with the same column and then printing the sum of the returned values into cells like I did above?

Note when using the [Range.Find method](https://learn.microsoft.com/en-us/office/vba/api/excel.range.find) **always** specify the `LookAt` parameter als `xlWhole` or `xlPart`. If you don't do that Excel will use whatever was used last by either VBA or the user interface. That means the result will be unpredictable! • Using `GoTo` is a very bad practice! And `GoTo NextB` is not defined so nothing to jump to! • Also don't use `.Select` and `Activate` see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Nov 13 '19 at 09:29
The Goto reference is not included as this this isn't the full code, so it's further down. Good call on LookAt parameter, just added it to the rest of the body. — ShroomBear, Nov 13 '19 at 09:42
Well, still don't use `GoTo` use a `If … Then … Else … End If` structure instead. — Pᴇʜ, Nov 13 '19 at 09:44
Also, you can eliminate the If...ElseIf...ElseIf block with `Sheets("Trans Out").Range("$I$4").Offset(ToutControl-1,0).Value = ActiveCell.Value`. — Excel Developers, Nov 13 '19 at 09:44
So in the example above I listed 1 value being pulled from the intersection. What I'm trying to do is do that 3 times from different intersection locations, then print the sum of the 3 values to the same cell. The column in the intersection will always be there, however the external data will exclude entire rows if the value I need from it is 0. So if the row is missing the statement needs to be able to exclude skip and move on or list the intersected value as 0. Hence the goto's which I realize is bad, however I don't know a workaround with if-then — ShroomBear, Nov 13 '19 at 10:15
Not really getting what you want. Obviously `xPath` will only get the first cell containing "Rowheader", but you might have three values like that? Is that why you try to loop? — JvdV, Nov 13 '19 at 10:17
Yes, I'll get a value when I intersect rowheader with columnheader, but I'll have between 0 to 3 values from different rows with different headers that would need to get added together and printed into a cell onto a different sheet — ShroomBear, Nov 13 '19 at 10:42

score 0 · Answer 1 · answered Nov 13 '19 at 12:15

0

Try:

'Use Long to avoid error in case of a number bigger than 32,767
Dim ToutControl As Long

'9 is the number of column I
Sheets("Trans Out").Cells(ToutControl + 3, 9).Value = ActiveCell.Value

answered Nov 13 '19 at 12:15

Error 1004

7,877
3
23
46

Intersect multiple rows and columns then SUM them

1 Answers1