Use Range.Offset Method with cell value

Ulisses_Carso

New Member
Joined
Sep 4, 2020
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,671
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Your image looks like it is
VBA Code:
fn.Offset(, .Cells(c.Row, 2).Value + 4) .Resize(,  .Cells(c.Row, 4).Value).Copy c.Offset(, .Cells(c.Row, 2).Value + 3)
 
Last edited:

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Ulisses_Carso

New Member
Joined
Sep 4, 2020
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,671
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
So what did you get with
VBA Code:
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

New Member
Joined
Sep 4, 2020
Messages
29
Office Version
  1. 365
Platform
  1. Windows
i did something wrong here, both lines are not working now, i'm trying to find what i did wrong, omg
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,671
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Mine should look like
VBA Code:
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
 
Solution

Ulisses_Carso

New Member
Joined
Sep 4, 2020
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,671
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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

Ulisses_Carso

New Member
Joined
Sep 4, 2020
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,671
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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 :ROFLMAO:
 

Ulisses_Carso

New Member
Joined
Sep 4, 2020
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,063
Messages
5,575,892
Members
412,689
Latest member
nhsmedic
Top