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]

Your image looks like it is

fn.Offset(, .Cells(c.Row, 2).Value + 4) .Resize(,  .Cells(c.Row, 4).Value).Copy c.Offset(, .Cells(c.Row, 2).Value + 3)

 

[Ulisses_Carso]

Yeah, i found it now too @Norie

It's working, the code is getting the range acoording to column B and resizing by column D and pasting it exactly as copied, so far so good.
Now, the data copied is wrong, the result of first line is: 135, 5, 0, 112, 105, 89, and must be: 9, 4, 0, 9, 0, 0. Before pasting the data it must offset 3 columns to the right first.
I really don't understand all this offsets, I am increasingly confused by them hahahaha >.<

@MARK858
your line didn't work
it was giving an erro "end of instruction expected" that i fix, but still not working with the entire code.

 

[MARK858]

So what did you get with

fn.Offset(, .Cells(c.Row, 2).Value + 4).Resize(, .Cells(c.Row, 4).Value).Copy c.Offset(, .Cells(c.Row, 2).Value + 3)

When you say it was not working with the rest of the code?

 

[Ulisses_Carso]

i did something wrong here, both lines are not working now, i'm trying to find what i did wrong, omg

 

[MARK858]

Mine should look like

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(, .Cells(c.Row, 2).Value + 4).Resize(, .Cells(c.Row, 4).Value).Copy c.Offset(, .Cells(c.Row, 2).Value + 3)
            End If
        Next
    End With

 

[Ulisses_Carso]

HOLY F*** (sorry for language)
it worked, PERFECTLY, with a little modification, served as a glove , OMG, in first offset i removed that ( + 4) and got exactly what i need, so good.

Can't say how gratefull I am to both of you guys, my most sincere thanks to both !!!

Below is the entire code as a result:

Private Sub Search()

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(, .Cells(c.Row, 2).Value).Resize(, .Cells(c.Row, 4).Value).Copy c.Offset(, .Cells(c.Row, 2).Value + 3)
            End If
        Next
    End With
End Sub

 

[MARK858]

Not sure why you needed to remove the +4 if those 3 blank columns are there but happy you have it working

 

[Ulisses_Carso]

In the first line i have value (26) to offset, with + 4, so instead of bring: 9, 4, 0, 9, 0, 0, it was offseting 30 columns at the begining. But that line was a straight shot anyway!

 

[MARK858]

Happy you worked it out, I was just puzzled why my counting was so far out as I thought you needed it with the 3 blank columns in the red bordered image.

Not to worry I just need some new fingers to count on

 

[Ulisses_Carso]

oh, now i understand, my mistake, i didn't explain that, i just added those 3 columns to arrange the othe columns, originally i don't have them, sorry about that.