Set a new specific range in VBA

Question

I'm trying to set a new range to VLookup required data.

Lets say I have the following table of data as a PivotTable. The order of Sets can change, some Sets can be absent.

With Find command (Match function doesn't work) I've found the row number with "Set1", name it X. Now I want to set a new range starting with Cells(X, 1) and ending with Cells(X +4, 2) in order to find the No. of different goods in a set.

How can I do it?

In the following code, second line doesn't work.

    X = Lookup_Range.Find("Set1", Range("A1"), xlValues, xlWhole, xlByColumns, xlNext).Row
    Set Lookup_Range = Worksheets("Sheet5").Range(Cells(X, 1), Cells(X + 4, 2))

Any help would be appreciated.

A few issues to come to, but how come you are using Lookup_Range before you define it? — SJR, Mar 23 '17 at 16:28
[Is the . in .Range necessary when defined by .Cells?](http://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) — , Mar 23 '17 at 17:17
@SJR, I've defined Lookup_Range before in the code. I just didn't want to post the whole code. I know that exactly this part doesn't work. Everything else without it worked just fine. — Slava32, Mar 24 '17 at 09:03

score 3 · Answer 1 · answered Mar 23 '17 at 16:30

3

Here is a slightly more rigorous way of doing things, which checks the value is found first. You error is due to incomplete sheet references - the Cells references were unqualified which will cause an error if a different sheet is active when the macro is run. My query above still applies though.

Dim r As Range, x As Long, Lookup_Range As Range

Set r = Lookup_Range.Find("Set1", Range("A1"), xlValues, xlWhole, xlByColumns, xlNext)
If Not r Is Nothing Then
    x = r.Row
    With Worksheets("Sheet5")
        Set Lookup_Range = .Range(.Cells(x, 1), .Cells(x + 4, 2))
    End With
End If

answered Mar 23 '17 at 16:30

SJR

22,986
6
18
26

I believe you nailed it for this circumstance but this still leaves `Range("A1")` in the `Lookup_Range.Find(...` unqualified and subject to a future error. `Set r = Lookup_Range.Find("Set1", Lookup_Range.Cells(1), xlValues, xlWhole, xlByColumns, xlNext)` would cover that. – Mar 23 '17 at 17:21
1

@Jeeped - yes, indeed. I was hoping the OP would clarify Lookup_Range and take it from there, but it is a loose end. – SJR Mar 23 '17 at 17:22
@SJR, thank you for your try. I'm not sure it works properly. When I look for a No. of good1 in a Set1 in this new Lookup_Range, instead of value 3 I get the sum of good1. – Slava32 Mar 24 '17 at 09:38
Okay, after some manipulations the following code worked: Dim g1 As Single, Lookup_Range As Range, x As Long Set Lookup_Range = Worksheets("Sheet5").range("A1:B17") x = Lookup_Range.Find("Set1", range("A1"), xlValues, xlWhole, xlByColumns, xlNext).Row With Worksheets("Sheet5") Set Lookup_Range = .range(.Cells(x, 1), .Cells(x + 4, 2)) g1 = Application.WorksheetFunction.VLookup("good1", Lookup_Range, 2, False) .Cells(30, 7) = g1 End With – Slava32 Mar 24 '17 at 09:55
Can you add that to your post and format it so we can read it - not sure what you had to do. Also, as per Jeeped's point it would be good practice to add a sheet reference to the find parameters. – SJR Mar 24 '17 at 10:35

score 1 · Answer 2 · answered Mar 23 '17 at 16:33

1

You're only looking in column A? Then this would work:

X = Range("A:A").Find("Set1", Range("A1"), xlValues, xlWhole, xlByColumns, xlNext).Row
Set lookup_range = Worksheets("Sheet5").Range(Cells(X, 1), Cells(X + 4, 2))

answered Mar 23 '17 at 16:33

CLR

11,284
1
11
29

1

This is dangerous (and a very common bug-that-ends-up-as-a-SO-dupe too); `Range.Find` returns `Nothing` if the search fails to find anything, which makes the chained `.Row` call fail with run-time error 91. ALWAYS verify if you have a valid object reference before calling into it. – Mathieu Guindon Mar 23 '17 at 17:08
You're right @Mat'sMug, I think I just corrected the syntax without looking at what it's doing. SJR's approach is much better. – CLR Mar 24 '17 at 09:05

Set a new specific range in VBA

2 Answers2