How to apply OFFSET as the range in a basic 'Copy' process...

Simply looking to understand how to properly apply the offset command to
identify a range in VBA coding. The respective portion of my 'improper' code
is shown below. What should the 'Range("...' line of code be? Thanks!


Sub PivotConcept()

Sheets("Pivots").Select
Range("(OFFSET(R12C8,7,6,3,2)").Select
Selection.Copy

..
..
..

End Sub

Range("H12").Offset(7,6).Resize(3,2).Copy

--

HTH

RP
(remove nothere from the email address if mailing direct)


"cdavidson" <[email protected]> wrote in message
news:[email protected]...
> Simply looking to understand how to properly apply the offset command to
> identify a range in VBA coding. The respective portion of my 'improper'
code
> is shown below. What should the 'Range("...' line of code be? Thanks!
>
>
> Sub PivotConcept()
>
> Sheets("Pivots").Select
> Range("(OFFSET(R12C8,7,6,3,2)").Select
> Selection.Copy
>
> .
> .
> .
>
> End Sub

As you are learning VBA, shed yourself of the notion you need to SELECT a
range to act on it. Truth is you RARELY need to select ranges in VBA.

Now, to use OFFSET in VBA, it is of the syntax

RANGE("A10").OFFSET(#rows,#cols)
or
CELLS(10,1).OFFSET(#rows,#cols)

In this example you end up with a 1 by 1 range. It's identical in size to
your original reference, A10 being a single cell. If you need a different
sized range you use

RANGE("A10").OFFSET(#rows,#cols).RESIZE(how many rows tall, how many columns
wide)

To rewrite your code

Cells(12,8).OFFSET(7,6).resize(3,2).COPY


"cdavidson" wrote:

> Simply looking to understand how to properly apply the offset command to
> identify a range in VBA coding. The respective portion of my 'improper' code
> is shown below. What should the 'Range("...' line of code be? Thanks!
>
>
> Sub PivotConcept()
>
> Sheets("Pivots").Select
> Range("(OFFSET(R12C8,7,6,3,2)").Select
> Selection.Copy
>
> .
> .
> .
>
> End Sub

My apologies Bob, but I tried to over simplify my actual situation for the
sake of simply getting the proper coding structure from you. My actual
OFFSET formula is far more complicated (pasted below in case it helps). Is
there a way we can stick with my original format of
Range("(OFFSET(...)").Select ??

Thanks

--------------

Actual OFFSET formula...


=OFFSET($H$12,(COUNTIF((INDIRECT("$H$12:H"&(11+(INDIRECT("_DATAROWS"))))),"<>"&"GASHA")),3,(SUMPRODUCT(--((INDIRECT("$H$12:H"&(11+(INDIRECT("_DATAROWS")))))="GASHA"),--((INDIRECT("$J$12:J"&(11+(INDIRECT("_DATAROWS")))))=(OFFSET($H$12,(COUNTIF((INDIRECT("$H$12:H"&(11+(INDIRECT("_DATAROWS"))))),"<>"&"GASHA")),2,1,1))))),4)


"Bob Phillips" wrote:

> Range("H12").Offset(7,6).Resize(3,2).Copy
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "cdavidson" <[email protected]> wrote in message
> news:[email protected]...
> > Simply looking to understand how to properly apply the offset command to
> > identify a range in VBA coding. The respective portion of my 'improper'
> code
> > is shown below. What should the 'Range("...' line of code be? Thanks!
> >
> >
> > Sub PivotConcept()
> >
> > Sheets("Pivots").Select
> > Range("(OFFSET(R12C8,7,6,3,2)").Select
> > Selection.Copy
> >
> > .
> > .
> > .
> >
> > End Sub
>
>
>

So long as you're going to put this into VBA, you'll have to abide by VBA's
syntax.

Bob's reply pretty much lays out what VBA allows and what you need. You'll
have to be responsible for converting your formula into the values you need
for the 2 arguments to the OFFSET property and the 2 arguments to the RESIZE
property

"cdavidson" wrote:

> My apologies Bob, but I tried to over simplify my actual situation for the
> sake of simply getting the proper coding structure from you. My actual
> OFFSET formula is far more complicated (pasted below in case it helps). Is
> there a way we can stick with my original format of
> Range("(OFFSET(...)").Select ??
>
> Thanks
>
> --------------
>
> Actual OFFSET formula...
>
>
> =OFFSET($H$12,(COUNTIF((INDIRECT("$H$12:H"&(11+(INDIRECT("_DATAROWS"))))),"<>"&"GASHA")),3,(SUMPRODUCT(--((INDIRECT("$H$12:H"&(11+(INDIRECT("_DATAROWS")))))="GASHA"),--((INDIRECT("$J$12:J"&(11+(INDIRECT("_DATAROWS")))))=(OFFSET($H$12,(COUNTIF((INDIRECT("$H$12:H"&(11+(INDIRECT("_DATAROWS"))))),"<>"&"GASHA")),2,1,1))))),4)
>
>
> "Bob Phillips" wrote:
>
> > Range("H12").Offset(7,6).Resize(3,2).Copy
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "cdavidson" <[email protected]> wrote in message
> > news:[email protected]...
> > > Simply looking to understand how to properly apply the offset command to
> > > identify a range in VBA coding. The respective portion of my 'improper'
> > code
> > > is shown below. What should the 'Range("...' line of code be? Thanks!
> > >
> > >
> > > Sub PivotConcept()
> > >
> > > Sheets("Pivots").Select
> > > Range("(OFFSET(R12C8,7,6,3,2)").Select
> > > Selection.Copy
> > >
> > > .
> > > .
> > > .
> > >
> > > End Sub
> >
> >
> >