VBA get range from cell offset

  • Thread starter Thread starter stainless
  • Start date Start date
S

stainless

I have a macro that allows pictures to be added to a cell defined as a
range and, as such, takes the range as an input i.e.

Sub InsertPicture(PictureFileName As String, TargetCell As Range, _
CenterH As Boolean, CenterV As Boolean)

However, my main code is using cell offsets in all of the processing
and I am unsure as to how to convert my offset cell to the TargetCell
value required above i.e.

Set fdcCell = FDCs.Cells(fdcrownumber, 1) -- the initial cell
definition

The cell I need to find the range value for is fdcCell.Offset(0, 18)

I guess I have to create a range variable such as "Dim pictRange As
Range"

What is the command to set this pictRange value to that of the cell
defined by offset fdcCell.Offset(0, 18) ??

Cannot get this right!!

Cheers
 
Sub InsertPicture(PictureFileName As String, _
TargetCell As Range, _
    CenterH As Boolean, CenterV As Boolean)

However, my main code is using cell offsets in all
of the processing and I am unsure as to how to convert
my offset cell to the TargetCell value required above i.e.

Set fdcCell = FDCs.Cells(fdcrownumber, 1) -- the initial
cell definition

The cell I need to find the range value for is
fdcCell.Offset(0, 18)
Click to expand...

I'm a bit confused. Why can't you simply pass fdcCell.Offset(0,18)
directly as the 2nd parameter to InsertPicture?

fdcCell.Offset(0,18) returns a Range of a single cell, given how you
set fdcCell in the first place. Look at fdcCell.Offset(0,18).Address.

Are you looking for a multicell range? Is so, what defines the limits
of the range? Look at something like
Range(fdcCell.Offset(0,18),fcdCell.Offset(5,22)).Address.

(Just making things up to demonstrate concepts.)

Note: Although I use .Address to display the ranges, you would pass
the object without .Address to the TargetCell parameter.
 
I'm a bit confused.  Why can't you simply pass fdcCell.Offset(0,18)
directly as the 2nd parameter to InsertPicture?
Click to expand...

It is me that is confused. I did not know that simply passing in the
Offset cell would satisfy the Range variable.

I will give it a go and get back to you with the results.

Thanks for your help.
 
It is me that is confused. I did not know that simply passing in the
Offset cell would satisfy the Range variable.

I will give it a go and get back to you with the results.

Thanks for your help.
Click to expand...

I tried fdcCell.Offset(0,18) and fdcCell.Offset(0,18).Address, and in
both cases, the TargetCell range value on entry to the sub
InsertPicture was empty.Have I misunderstood again?
 
Back
Top