VBA Syntax for selecting range objects

Question

I'm getting:

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

`bom_cell` is a string, the address of a range. It isn't a range and it isn't a cell. Therefore `bom_cell.Offset(num_rows, 2)` fails. — Variatus, Apr 03 '20 at 04:05
can you post your sample data then explain what you want to do? That will make it easier to find solutions for you — ManhND, Apr 03 '20 at 04:10

score 1 · Answer 1 · answered Apr 03 '20 at 04:49

When assigning to objects, you must use Set. In this case, you want to store the range object of the cells. Otherwise, your variable will store only the value of these cells.

So, change these lines:

bom_cell = ws_ref.Range(ws_ref.Cells(2, bom_pos).Address)
ref_cell = ws.Range(ws.Cells(1, 4 * (i - 1) + 1).Address)

to these

Set bom_cell = ws_ref.Range(ws_ref.Cells(2, bom_pos).Address)
Set ref_cell = ws.Range(ws.Cells(1, 4 * (i - 1) + 1).Address)

score 0 · Accepted Answer · answered Apr 03 '20 at 05:15

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

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.

Thanks a lot for the effort. This worked. I had use set to make it a range and not just the value. I am very new to VBA , hence this issue. — Murali Manohar Vedula, Apr 04 '20 at 09:09
Great! Does my response need any modification before you can endorse it as "Selected" for other people to learn from it? — Variatus, Apr 04 '20 at 09:20

VBA Syntax for selecting range objects

2 Answers2

Linked