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

C

cdavidson

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
 
B

Bob Phillips

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

--

HTH

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

Duke Carey

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
 
C

cdavidson

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)
 
D

Duke Carey

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
 
Top