Searching for number of values in one table, resizing number of rows in another table

Question

I'm trying to search for the number of times "Commodity" appears in the table column: Securities[Strategy]. I then want to take that number and resize a table (named: Commodity) on another worksheet accordingly. If it appears 6 times in column Securities[Strategy], the Commodity table should resize to 6 rows, and so on for any number.

I'm very new to VBA. When I run the following code nothing happens.

Sub AdjRow()

    Dim Count1 As Integer
    Count1 = Application.WorksheetFunction.CountIf(Range("Securities[Strategy]"), "Commodity")
    Count1 = Count1 + 12

    ActiveSheet.ListObjects("Commodity").Resize Range("$A$12:$J$" & Count1)

End Sub

I checked when debugging. Count1 was pulling in the right number. I'm too new to understand it, but I think using ActiveSheet instead of referring to to the correct sheet, was preventing the macro from doing anything. I seem to remember trying to use WorkSheets("SheetName").ListObjects...(etc) - but that didn't work either. I ended up assigning the sheet and table to a variable (Var), as Eirik noted below, and then using Var.Resize Range... - as the command. — twils0, Mar 03 '16 at 15:03

score 1 · Accepted Answer · answered Mar 03 '16 at 08:34

To help with debugging you can either print key values to the immediate window using Debug.Print or to a messagebox using MsgBox. In this case though, I am curious if you get any error messages when you attempt to run the macro. Editing your code and attempting to run it, it runs fine when the result I get from the CountIf is larger than one, but aborts with an error when it is one or less. If the table you attempt to resize don't have headers, I assume the macro will run fine if CountIf is greater than zero.

Here is the the code, sample data, and output I got when attempting to debug your code. I ran the code 3 times, and had return values of 8, 1, and 2 from the CountIf-function. Note how I didn't get the third address for the listobject on the second run-through, this was because the code aborted when it tried to set the ListObject to only its headers (A2:J2).

Code

Option Explicit
Sub AdjRow()

    Dim r As Range
    Dim i As Long
    Dim lo As ListObject

    Set r = Sheet2.Range("Securities[Strategy]")
    i = Application.WorksheetFunction.CountIf(r, "Test1")
    i = i + 1
    Set lo = Sheet1.ListObjects("Commodity")

    Debug.Print i
    Debug.Print r.Address
    Debug.Print lo.Range.Address

    lo.Resize Range("$A$2:$J$" & i)

    Debug.Print lo.Range.Address

End Sub

Sheet1

Sheet2

Output to immediate window

9
$A$2:$A$10
$A$2:$J$9
$A$2:$J$9
2
$A$2:$A$10
$A$2:$J$9
3
$A$2:$A$11
$A$2:$J$9
$A$2:$J$3

Thanks Eirik - very helpful. I'm very new to macros, but I think the problem may have been my use of "ActiveSheet" above. I don't think this command was doing anything. I got to point where I could click run; and, nothing would happen. I would debug in VB, and I could tell the range countif was working. It just wouldn't resize the table. — twils0, Mar 03 '16 at 14:56
Now I just need to figure out how to get the new row's formatting to match the rest of the table.The new rows show up with different formatting. — twils0, Mar 03 '16 at 14:58
I think it may also have been the use of "Integer" as my counting variable("i" above), instead of "Long" — twils0, Mar 03 '16 at 15:10
@Tyler Using integer instead of long shouldn't affect anything, except that [using long is marginally more effficient](http://stackoverflow.com/a/26409520/4497791). — eirikdaude, Mar 03 '16 at 20:50

Searching for number of values in one table, resizing number of rows in another table

1 Answers1