Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
How to apply OFFSET as the range in a basic 'Copy' process...
Range("H12").Offset(7,6).Resize(3,2).Copy
-- HTH RP (remove nothere from the email address if mailing direct) "cdavidson" wrote in message ... 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 |
#3
|
|||
|
|||
How to apply OFFSET as the range in a basic 'Copy' process...
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 |
#4
|
|||
|
|||
How to apply OFFSET as the range in a basic 'Copy' process...
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+(IN DIRECT("_DATAROWS"))))),"<"&"GASHA")),3,(SUMPRODU CT(--((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" wrote in message ... 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 |
#5
|
|||
|
|||
How to apply OFFSET as the range in a basic 'Copy' process...
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+(IN DIRECT("_DATAROWS"))))),"<"&"GASHA")),3,(SUMPRODU CT(--((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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to copy last positive number in range of cells | Excel Worksheet Functions | |||
Basic: Looking up Data in Range | Excel Discussion (Misc queries) | |||
Copy a range | Excel Worksheet Functions | |||
Define Range with an offset | Excel Discussion (Misc queries) | |||
copy range of cells from one workbook to another | Excel Discussion (Misc queries) |