How to set range to variable?

Question

I'm trying to save a range from another sheet to a variable and then compare it to a cell. I have this code:

Function collectUtfall(A1 As String, Ax As String)
Dim rng As Variant
Dim sum as Integer

sum = 0

Set rng = Sheets("Utfall").Range("N2").Value <------- This line

If rng = Ax Then
        sum = sum + 10
        Else: sum = 33

End If

collectUtfall = sum
End Function

The problem is that it's not acting the way I hoped. I get #Value error, and I have narrowed it down to the line marked in the code. If I remove that line I don't get the error but of course the result is only 0.

I have tried to dim rng As Range also, doesn't work.

What can be the problem?

EDIT: Original problem solved, but encountered another one. If I insted use this code (small changes) I get the same error again. Am I using offset wrong or where is the problem now?

Function collectUtfall(A1 As String, Ax As String)
Dim rng As Variant
Dim sum As Integer

sum = 0

rng = Sheets("Utfall").Range("M2:O272").Value

If rng.Offset(0, 1) = Ax Then
    sum = sum + 10
    Else: sum = 33

End If


collectUtfall = sum
End Function

You should honor answer to previous question, marking it as correct and making a new question. `Offset` is a `Range` method. Remove `.Value` and everything should run fine. — LS_ᴅᴇᴠ, Aug 29 '13 at 14:09
Ok, sorry. Wasn't sure about if I should start a new question or not. Will do next time! — andysando, Aug 29 '13 at 14:11

score 3 · Answer 1 · answered Aug 29 '13 at 13:08

3

"Set" is unneeded in Set rng =; remove it.

rng = Sheets("sheet1").Range("N2").Value

answered Aug 29 '13 at 13:08

Joe

6,767
1
16
29

Wow, that was simple. Thanks! For future reference, when is Set needed? – andysando Aug 29 '13 at 13:12
3

You use *Set* when assigning a reference to an *object*. For example: `Dim c as Range : Set c = Range("A1")`. In your code, you're assigning rng to a *value*. – Joe Aug 29 '13 at 13:14
3

You can find out more on the web. Here's a start: http://stackoverflow.com/questions/3872339/what-is-the-difference-between-dim-and-set-in-vba. – Doug Glancy Aug 29 '13 at 13:15

score 3 · Accepted Answer · edited May 23 '17 at 12:06

There are several problems with your code.

I recommend you start learning what Set is used for and when.

Difference between Dim and Set - provided by Doug Glancy

Also, check out what datatypes to chose for your variables and why. See their limitations and example of usage.

Find out how to work with Ranges. When to use .Value and when not. When you pointing a variable to a single cell and when to multiple cells.

Learn how to loop over a Range collection.

Read a bit about Code Indentation.

and after that you will be able to write your own:

Function collectUtfall(A1 As String, Ax As String)

    Dim rng As Range
    Dim sum As Long: sum = 0

    Set rng = Sheets("Utfall").Range("M2:O272")

    Dim cell As Range
    For Each cell In rng
        If StrComp(cell.Offset(0, 1).Text, Ax, vbTextCompare) = 0 Then
            sum = sum + 10
        Else
            sum = 33
        End If
    Next

    collectUtfall = sum
End Function

score 0 · Answer 3 · answered Aug 29 '13 at 13:12

0

You may have conflicting datatypes. If Ax is type string, you should be comparing a string to it. You could define rng as string (dim rng as string), or do cstr:

If CStr(Rng) = Ax Then

answered Aug 29 '13 at 13:12

Aaron Thomas

5,054
8
43
89

How to set range to variable?

3 Answers3