Use Range.Offset Method with cell value

[Ulisses_Carso]

Is there a way to use a cell Value with Range.Offset Method?

I Have this code done by a colleague ( @JLGWhiz ) from the forum, in parts it works as i need, but i would like to use "fn.Offset(, "B2, B3, B4, etc...") and .Resize(, "D2, D3, D4, etc...")
This code will search in both columns A and bring me the entire line, i need only a few, and random, cells from each line.

Dim sh1 As Worksheet, sh2 As Worksheet
Dim c As Range, fn As Range

Worksheets("Maintenance").Activate

Set sh1 = Sheets("BaseDin")
Set sh2 = Sheets("Maintenance")
    
    With sh2
        For Each c In .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
            Set fn = sh1.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                fn.Offset(, 1).Resize(, 31).Copy c.Offset(, 4)
            End If
        Next
    End With

 

[MARK858]

You just put them in as Range("??") or Range("??").Value i.e.

fn.Offset(, Range("B2").Value).Resize(, Range("D2").Value).Copy c.Offset(, 4)

If that is what you mean

 

[Ulisses_Carso]

it's almost it, is there a way to it undesrtand the entire column?

instead of fn.Offset(, Range("B2") use fn.Offset(, Range("B:B"), and same for Range("D2"), because this month I have 82 Lines and range for each line is diferent
it seems to be a simple thing but I can't see, it's so anoying when it happens, hahaha

 

[MARK858]

You can't offset from a single cell to an entire column. You can go left, right, up, and down to a single cell.
If you mean offset by B1 value, resize by D2 then make that range an entire column then that is

fn.Offset(, Range("B2").Value).Resize(, Range("D2").Value).EntireColumn.Copy

If you mean something else then you need to describe in detail exactly what you want.

 

[Norie]

Do you want the offset and resize to come from the same row as that in which the value being searched for is bound?

 

[Ulisses_Carso]

Hello Norie,

Yes that's what i need
I'm trying to post an image and the spreadsheet here to make it easier to understand, is there any way to post the Excel file? for easier understanding, is there any way to post the Excel file?

 

[Ulisses_Carso]

Sorry forgot this part:


The Red Rectangle is what I have (part of "BaseDin" Sheet)
The Yellow Rectangle is what I need based in Coluns B, C and D (final result, just for comparish only)
The Blue Rectangle is where I need the result (final result on "Maintenance" Sheet)

So, with the original Sub, the code will search for common data in Column A of both sheets and bring me the entire line,
the way i need is:
Search for common data in Column A of both sheets but the result must be: instead of entire line, bring back the value in column B, resize selection by Column D, paste the result in the Blue Rectangle with offset of column B+4(columns)

 

[Norie]

Try this.

Dim sh1 As Worksheet, sh2 As Worksheet
Dim c As Range, fn As Range

Worksheets("Maintenance").Activate

Set sh1 = Sheets("BaseDin")
Set sh2 = Sheets("Maintenance")
    
    With sh2
        For Each c In .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
            Set fn = sh1.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                fn.Offset(, 1).Resize(, .Range("D" & fn.Row).Copy c.Offset(, .Range("B" & fn.Row))
            End If
        Next
    End With

 

[Ulisses_Carso]

it's not working

error msg: "list separator or ) "

fn.Offset(, 1).Resize(, .Range("D" & fn.Row).Copy c.Offset(, .Range("B" & fn.Row)) "something is wrong in this line

 

[Norie]

Oops, missed a closing ).

            If Not fn Is Nothing Then
                fn.Offset(, 1).Resize(, .Range("D" & fn.Row)).Copy c.Offset(, .Range("B" & fn.Row))
            End If