CODEWITHSUNDEEP
excelvbarangeuser-defined-functions
DBWeinstein
DBWeinstein

Reputation: 9489

How to set a Range using columns and rows in VBA?

Tried to get a range setup to use the worksheet function COUNTIF. Here's what I've got:

Function count_if(work_sheet As String, criteria As String, column_num As Integer)

    Dim rows As Integer
    rows = Get_Rows_Generic(work_sheet, 1) ' get the number of rows in another sheet

    Dim full_range As Range
    With work_sheet
        Set full_range = .Range(.Cells(0, rows), .Cells(0, column_num))
    End With

    count_result = WorksheetFunction.CountIf(full_range, criteria)

    count_if = range_size
End Function

Sub test_stuff()
    Dim n As Integer
    n = count_if("usersFullOutput.csv", "TRUE", 9)
    MsgBox n
End Sub

When I run the code, excel asks me to choose another macro. I'm guessing it's how i'm setting the range, but I have no idea.

Upvotes: 1

Views: 140

Answers (2)

ZygD
ZygD

Reputation: 24386

Point #1

IF "usersFullOutput.csv" is actually your worksheet's name (not a file name), with this name you cannot do this: 

With work_sheet
    Set full_range = .Range(...)
End With

Range is a property of a worksheet object, and not a worksheet name string. Try doing this:

With Worksheets(work_sheet)
    Set full_range = .Range(...)
End With

Point #2

Set full_range = .Range(.Cells(0, rows), .Cells(0, column_num))

The first argument of Cells() is row number. Row number can never be 0. The first row in Excel is always 1. A1 would be referenced by Cells(1, 1). Maybe you need something like

Set full_range = .Range(.Cells(1, 1), .Cells(rows, column_num))

Point #3

range_size is not defined (the line count_if = range_size).

I think you need

count_if = count_result

Upvotes: 2

basodre
basodre

Reputation: 5770

You're on the right track, but the syntax is somewhat wrong. You'd need to set the start cell and end cell, using row_index and col_index, such as:

Set full_range = .Range(.Cells(1,1), .Cells(rows, column_num)

Let me know if that helps

Upvotes: 0

Related Questions