如何使用vba从其他点参考选择特定范围? [英] how to select a specific range with reference from other points using vba?

问题描述

我正在尝试从excel工作表中的行标签"和其他2个总计"之类的参考点自动选择特定范围.但是，我在Set newlocationgrand1 = locationgrand1部分遇到类型不匹配"，如果我删除set，它会显示"set object required error".那我该怎么做才能解决这个问题呢?或者，是否有另一种方法可以从如下所示的点中选择参考范围?

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

更新的代码

我已经对代码进行了一些调整，即使我设法获得了范围(B5:J12)，这也是我想要的部分，但选择部分仍然出现此范围类的选择方法失败"错误选择但要根据变量表达

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

推荐答案

可能是:

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:=...

这篇关于如何使用vba从其他点参考选择特定范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！