Use Range.Offset Method with cell value

Ulisses_Carso

New Member
Joined
Sep 4, 2020
Messages
31
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,308
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You just put them in as Range("??") or Range("??").Value i.e.
VBA Code:
fn.Offset(, Range("B2").Value).Resize(, Range("D2").Value).Copy c.Offset(, 4)
If that is what you mean
 

Ulisses_Carso

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,308
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
VBA Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

Attachments

  • example.png
    example.png
    82.4 KB · Views: 6

Ulisses_Carso

New Member
Joined
Sep 4, 2020
Messages
31
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Try this.
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(, .Range("D" & fn.Row).Copy c.Offset(, .Range("B" & fn.Row))
            End If
        Next
    End With
 

Ulisses_Carso

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Oops, missed a closing ).
VBA Code:
            If Not fn Is Nothing Then
                fn.Offset(, 1).Resize(, .Range("D" & fn.Row)).Copy c.Offset(, .Range("B" & fn.Row))
            End If
 

Forum statistics

Threads
1,148,170
Messages
5,745,169
Members
423,930
Latest member
Simple77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top