CODEWITHSUNDEEP
vbaexcel
Slava32
Slava32

Reputation: 171

Set a new specific range in VBA

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.

enter image description here

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.

Upvotes: 1

Views: 2184

Answers (2)

CLR
CLR

Reputation: 12279

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))

Upvotes: 1

SJR
SJR

Reputation: 23081

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

Upvotes: 3

Related Questions