how to select a specific range with reference from other points using vba?

Question

Im trying to automate the selection of a specific range with reference from points like "row labels" and the other 2 "Grand Total" in my excel worksheet. However i face the "type mismatch" at the part Set newlocationgrand1 = locationgrand1 and if i remove set, it shows "set object required error". So what can i do to solve this issue? Alternatively, is there another way to select the range with reference from the points as shown below?

sub test

Dim locationrow as range

Dim locationgrand1 as range

Dim locationgrand2 As Range

Dim newlocationrow as string

Dim newlocationgrand1 As String

Dim locationgrand2no as long

Dim extractedno As Long

Dim lastcolletter As String


Set locationrow = Range("A1:L6").Find(What:="Row Labels", LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
 'row labels address is 'A4'


newlocationrow = locationrow.Offset(1, 1).Address 'newlocation row is B5

Set locationgrand1 = Range("A1:L6").Find(What:="Grand Total", LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

Set newlocationgrand1 = locationgrand1.Offset(0, -1).Address 'set object error if i put set, it shows type mismatch error

'newlocationgrand1 is J5

lastcolletter = colLetter(newlocationgrand1.Column) 'lastcolletter = J

Set locationgrand2 = Range("A1:A15").Find(What:="Grand Total", LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

    locationgrand2no = locationgrand2.Row 'locationgrand2 is 13
     extractedno = locationgrand2no - 1 'extractedno =12

With Selection
    Range("newlocationrow" & ":" & "lastcolletter" & "extractedno").Select

updated code

i have made some adjustments to the code and now there is this "select method of range class failed " error at with selection part even though i managed to get the range(B5:J12) which is the part i want it to select but express in terms of variables

sub test

Dim locationrow As Range

Dim locationgrand1 As Range

Dim newlocationrow As Range

Dim newlocationgrand1 As Range

Dim locationgrand2 As Range

Dim locationgrand2no As Long

Dim extractedno As Long

Dim lastcolletter As String

Dim locationcolletter As String

Dim x As String

Dim y As String

Dim extractednorow As Long

Dim locationrowno As Long

Set locationrow = Range("A1:L6").Find(What:="Row Labels", LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
 'row labels address is 'A4'

 Set newlocationrow = locationrow.Offset(1, 1) 'newlocationrow is 'B5'

locationcolletter = colLetter(newlocationrow.Column) 'locationcolletter = B

locationrowno = locationrow.Row + 1 'locationrow=5

Set locationgrand1 = Range("A1:L6").Find(What:="Grand Total", LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

Set newlocationgrand1 = locationgrand1.Offset(0, -1)

x = newlocationgrand1.Address 'set object error if i put set, it shows type mismatch error

'newlocationgrand1 is J5

lastcolletter = colLetter(newlocationgrand1.Column) 'lastcolletter = J

Set locationgrand2 = Range("A1:A15").Find(What:="Grand Total", LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

    locationgrand2no = locationgrand2.Row 'locationgrand2 is 13
     extractedno = locationgrand2no - 1 'extractedno =12

With Selection
    Sheets(1).Range(locationcolletter & locationrowno & ":" & lastcolletter & extractedno).Select

    '^select method range of class fail

Function colLetter(col As Long) As String
    colLetter = Split(Columns(col).Address(, 0), ":")(0)
End Function

Did you update it here? I'd do Dim locationgrand1 as Range. — BruceWayne
@BruceWayne now i got the invalid qualifer error at the part lastcolletter =... — john
Oh! I was looking at the wrong bit, try this: Set newlocationgrand1 = locationgrand1.Offset(0, -1). Then if you want the address, newlocationgrand1.Address. And you're getting that error, because what is colLetter() function? — BruceWayne
@BruceWayne my bad, i didn't name variables that are more easier to differentiate. Anyways, why do i get method range of object_global failed at the part Range(newlocationrow & ...)? I went to debug and seems like i have extracted the required range? — john
i have made some adjustments to the code and now there is this "select method of range class failed error" at with selection part even though i managed to get the range(B5:J12) which is the part i want it to select but express in terms of variables — john

HTH HTH · Accepted Answer · 2020-04-03T12:48:11

that could be:

With Range("A4").CurrentRegion
    With Range(.Columns(1).Find(what:="Grand Total", LookIn:=xlValues, lookat:=xlWhole), .Columns(.Columns.Count).Find(what:="Grand Total", LookIn:=xlValues, lookat:=xlWhole))
        .Offset(1, 1).Resize(.Rows.Count - 2, .Columns.Count - 2).Select
    End With
End With

but you hardly need to really Select anything

most probably you want store that range in a variable and use for subsequent operations

Dim myRange As Range
With Range("A4").CurrentRegion
    With Range(.Columns(1).Find(what:="Grand Total", LookIn:=xlValues, lookat:=xlWhole), .Columns(.Columns.Count).Find(what:="Grand Total", LookIn:=xlValues, lookat:=xlWhole))
        Set myRange = .Offset(1, 1).Resize(.Rows.Count - 2, .Columns.Count - 2)
    End With
End With

myRange.Copy destination:=...

how to select a specific range with reference from other points using vba?

2 Answers