选择范围对象的VBA语法 [英] VBA Syntax for selecting range objects

问题描述

我得到:

需要运行时错误'424'对象

Run Time Error '424' Object required

这是我用来将一个范围的值复制到另一个范围的代码.请让我知道与最后一行相同的问题.我认为当我尝试创建一个单元格区域和一个偏移量的单元格时出现问题

This is the code I am using to copy values of one range into another. Please let me know the issue with the same in the last line. I think the problem is arising when I try to create a range of a cell and the a cell which is offset

num_lines = 4
Set ws = Sheets("Working BoM")
Set ws_ref = Sheets("BoM")

For i = 1 To num_lines

    match_value = Sheets("Line Info").Range("C" & Trim(Str(i))).Value
    match_range = ws_ref.Range("A2:Y2")
    bom_pos = Application.WorksheetFunction.Match(match_value, match_range, 0)
    bom_cell = ws_ref.Range(ws_ref.Cells(2, bom_pos).Address)
    ref_cell = ws.Range(ws.Cells(1, 4 * (i - 1) + 1).Address)
    num_rows = ws_ref.Range("A2").Offset(0, bom_pos - 1).End(xlDown).Row - 1

    ws_ref.Range(bom_cell, bom_cell.Offset(num_rows, 2)).Copy _
     Destination:=ws.Range(ref_cell, ref_cell.Offset(num_rows, 2))


Next i

推荐答案

不幸的是，我没有数据可以测试您的代码或我的代码.因此，我无法理解您要做什么.但是，我检查了您的代码，结果如下.它应该没有您抱怨的错误.

Unfortunately, I don't have data to test your code or mine. Therefore I can't develop an idea of what you are trying to do. However, I reviewed your code and below is the result. It shouldn't have the error that you complained about.

Sub ReviewedCode()

    Dim Ws As Worksheet
    Dim WsBom As Worksheet
    Dim WsInfo As Worksheet
    Dim MatchRng As Range
    Dim MatchVal As Variant
    Dim MatchPos As Long
    Dim BomCell As Range
    Dim RefCell As Range
    Dim NumRows As Integer
    Dim R As Long
    Dim Rt As Long                          ' Target row

    Set Ws = Worksheets("Working BoM")
    Set WsInfo = Worksheets("Line Info")
    Set WsBom = Worksheets("BoM")
    MatchRng = WsBom.Range("A2:Y2")
    NumRows = 4

    For R = 1 To NumRows
        MatchVal = WsInfo.Cells(R, "C").Value
        On Error Resume Next                ' in case no match is found
        MatchPos = Application.WorksheetFunction.Match(MatchVal, MatchRng, 0)
        If Err.Number Then
            ' enter code here what to do if there was no match
            ' for now: do nothing & skip to next row
        Else
            Set BomCell = WsBom.Cells(2, MatchPos)
            Set RefCell = Ws.Cells(1, 4 * (R - 1) + 1)
            Rt = WsBom.Range("A2").Offset(0, MatchPos - 1).End(xlDown).Row - 1

            WsBom.Range(BomCell, BomCell.Offset(Rt, 2)).Copy _
                        Destination:=Ws.Range(RefCell, RefCell.Offset(Rt, 2))
        End If
    Next R
End Sub

如您所见，在我最明显的更改中，列出了您使用的所有变量.这将帮助您避免不知道 bom_cell 是范围，值还是地址而陷入的陷阱.请原谅我删除了所有漂亮的snake_names.我看不懂.对于其余的内容，我基本上保留了您的代码，但将其修改为正确使用您定义的变量.

As you see, of the changes I made the most visible is a listing of all variables you used. This will help you avoid the trap into which you fell of not knowing if bom_cell is a range, a value or an address. Excuse me for removing all your beautiful snake_names. I can't read them. For the rest of it I basically kept your code as it was but adapted it to the correct use of the variables you defined.

这篇关于选择范围对象的VBA语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！