| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset & Select
I am trying to select a dynamic range and fill the formatting right four
columns. The code that I have come up with is shown below: Selection.AutoFill Destination:=Range("T6:T" & lastRow).ActiveCell.Offset(0, 4).Select, Type:=xlFillFormats This is part of a larger loop, which works fine when this line removed. It is probably just a period or something; just can't figure it out. Thanks in advance, Ryan---- -- RyGuy |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Offset & Select
Try this... Not sure if you want to format all four columns or just the 4th
column over. Your code is 4th column over... Selection.AutoFill Destination:=Range("T6:T" & lastRow).Offset(0, 4), Type:=xlFillFormats -- HTH... Jim Thomlinson "ryguy7272" wrote: I am trying to select a dynamic range and fill the formatting right four columns. The code that I have come up with is shown below: Selection.AutoFill Destination:=Range("T6:T" & lastRow).ActiveCell.Offset(0, 4).Select, Type:=xlFillFormats This is part of a larger loop, which works fine when this line removed. It is probably just a period or something; just can't figure it out. Thanks in advance, Ryan---- -- RyGuy |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Offset & Select
It just changed to 7 columns. :)
That's trivial though. Thanks for the look Jim, but that didn't work. I thought it may be something like this: Selection.AutoFill Destination:=Range(("T6:" & lastRow), Cells(Lrow, 7)).Select However, this doesn't work wither. I'm sure it is possible, I'm just not seeing it right now... Any other ideas? Thanks, Ryan--- -- RyGuy "Jim Thomlinson" wrote: Try this... Not sure if you want to format all four columns or just the 4th column over. Your code is 4th column over... Selection.AutoFill Destination:=Range("T6:T" & lastRow).Offset(0, 4), Type:=xlFillFormats -- HTH... Jim Thomlinson "ryguy7272" wrote: I am trying to select a dynamic range and fill the formatting right four columns. The code that I have come up with is shown below: Selection.AutoFill Destination:=Range("T6:T" & lastRow).ActiveCell.Offset(0, 4).Select, Type:=xlFillFormats This is part of a larger loop, which works fine when this line removed. It is probably just a period or something; just can't figure it out. Thanks in advance, Ryan---- -- RyGuy |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Offset & Select
Autofill does not work over multiple columns unless your selection is over
multiple columns... Since I don't know what your selection is it is a bit tough for me to give you the correct code. That being said here is something that should be close... Range("T6:Z6").Autofill Destination:=range(range("T6"), cells(lastrow, "Z")) Note taht on my ranges I do not select as you have in the code you posted... -- HTH... Jim Thomlinson "ryguy7272" wrote: It just changed to 7 columns. :) That's trivial though. Thanks for the look Jim, but that didn't work. I thought it may be something like this: Selection.AutoFill Destination:=Range(("T6:" & lastRow), Cells(Lrow, 7)).Select However, this doesn't work wither. I'm sure it is possible, I'm just not seeing it right now... Any other ideas? Thanks, Ryan--- -- RyGuy "Jim Thomlinson" wrote: Try this... Not sure if you want to format all four columns or just the 4th column over. Your code is 4th column over... Selection.AutoFill Destination:=Range("T6:T" & lastRow).Offset(0, 4), Type:=xlFillFormats -- HTH... Jim Thomlinson "ryguy7272" wrote: I am trying to select a dynamic range and fill the formatting right four columns. The code that I have come up with is shown below: Selection.AutoFill Destination:=Range("T6:T" & lastRow).ActiveCell.Offset(0, 4).Select, Type:=xlFillFormats This is part of a larger loop, which works fine when this line removed. It is probably just a period or something; just can't figure it out. Thanks in advance, Ryan---- -- RyGuy |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Offset & Select
Try this. This will copy you selection to the adjacent 4 columns.
Selection.AutoFill Destination:=Range("T6:AB" & LastRow), Type:=xlFillDefault Hope this helps! -- Cheers, Ryan "ryguy7272" wrote: It just changed to 7 columns. :) That's trivial though. Thanks for the look Jim, but that didn't work. I thought it may be something like this: Selection.AutoFill Destination:=Range(("T6:" & lastRow), Cells(Lrow, 7)).Select However, this doesn't work wither. I'm sure it is possible, I'm just not seeing it right now... Any other ideas? Thanks, Ryan--- -- RyGuy "Jim Thomlinson" wrote: Try this... Not sure if you want to format all four columns or just the 4th column over. Your code is 4th column over... Selection.AutoFill Destination:=Range("T6:T" & lastRow).Offset(0, 4), Type:=xlFillFormats -- HTH... Jim Thomlinson "ryguy7272" wrote: I am trying to select a dynamic range and fill the formatting right four columns. The code that I have come up with is shown below: Selection.AutoFill Destination:=Range("T6:T" & lastRow).ActiveCell.Offset(0, 4).Select, Type:=xlFillFormats This is part of a larger loop, which works fine when this line removed. It is probably just a period or something; just can't figure it out. Thanks in advance, Ryan---- -- RyGuy |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Offset & Select
I' still not sure about what you are asking, by your code alone. Please
describe what you are trying to accomplish. -- Cheers, Ryan "RyanH" wrote: Try this. This will copy you selection to the adjacent 4 columns. Selection.AutoFill Destination:=Range("T6:AB" & LastRow), Type:=xlFillDefault Hope this helps! -- Cheers, Ryan "ryguy7272" wrote: It just changed to 7 columns. :) That's trivial though. Thanks for the look Jim, but that didn't work. I thought it may be something like this: Selection.AutoFill Destination:=Range(("T6:" & lastRow), Cells(Lrow, 7)).Select However, this doesn't work wither. I'm sure it is possible, I'm just not seeing it right now... Any other ideas? Thanks, Ryan--- -- RyGuy "Jim Thomlinson" wrote: Try this... Not sure if you want to format all four columns or just the 4th column over. Your code is 4th column over... Selection.AutoFill Destination:=Range("T6:T" & lastRow).Offset(0, 4), Type:=xlFillFormats -- HTH... Jim Thomlinson "ryguy7272" wrote: I am trying to select a dynamic range and fill the formatting right four columns. The code that I have come up with is shown below: Selection.AutoFill Destination:=Range("T6:T" & lastRow).ActiveCell.Offset(0, 4).Select, Type:=xlFillFormats This is part of a larger loop, which works fine when this line removed. It is probably just a period or something; just can't figure it out. Thanks in advance, Ryan---- -- RyGuy |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Offset & Select
Thanks for your persistence Jim, and welcome to the party Ryan. Here is the
code, in it's entirety (maybe this will help or just make things more confusing). Sub LoopMac() Dim lastRow As Long Dim c As Variant Dim sh As Worksheet For Each sh In Worksheets If (sh.Name) < "C2_UnionQuery" And (sh.Name) < "Summary-Sheet" Then sh.Activate With sh Chng = Range("T6").Value lastRow = .Cells(.Rows.Count, "T").End(xlUp).Row For Each c In .Range("T6:T" & lastRow) If c.Value < "" Then c.Offset(, 1).Formula = "=IF(ISNUMBER(RC[-1]/RC[-9]),RC[-1]/RC[-9],""N/A"")" End If Next c With sh Chng = Range("U6").Value lastRow = .Cells(.Rows.Count, "T").End(xlUp).Row For Each c In .Range("T6:T" & lastRow) If c.Value < "" Then c.Offset(, 2).Formula = "=IF(ISNUMBER(RC[-1]/R2C21*R3C21),IF(RC[-1]/R2C21*R3C211,1,RC[-1]/R2C21*R3C21),""N/A"")" End If Next c With sh Chng = Range("X6").Value lastRow = .Cells(.Rows.Count, "T").End(xlUp).Row For Each c In .Range("T6:T" & lastRow) If c.Value < "" Then c.Offset(, 4).Formula = "=IF(ISNUMBER(RC[-2]),SUM(RC[-1],RC[-9])*RC[-2],0)" End If Next c With sh Chng = Range("Z6").Value lastRow = .Cells(.Rows.Count, "T").End(xlUp).Row For Each c In .Range("T6:T" & lastRow) If c.Value < "" Then c.Offset(, 6).Formula = "=IF(ISNUMBER(RC[-4]),RC[-2]+RC[-1]*RC[-4],0)" End If Next c Range("W6:W" & Cells(Rows.Count, "T").End(xlUp).Row).Interior.ColorIndex = 36 Range("Y6:Y" & Cells(Rows.Count, "T").End(xlUp).Row).Interior.ColorIndex = 36 lastRow = .Cells(.Rows.Count, "T").End(xlUp).Row .Cells(lastRow, "T").Activate ActiveCell = .Cells(.Rows.Count, "W").End(xlUp).Row .Cells(lastRow, "W").FormulaR1C1 _ = "=sum(r2c:r[-1]c)" ActiveCell = .Cells(.Rows.Count, "X").End(xlUp).Row .Cells(lastRow, "X").FormulaR1C1 _ = "=sum(r2c:r[-1]c)" ActiveCell = .Cells(.Rows.Count, "Y").End(xlUp).Row .Cells(lastRow, "Y").FormulaR1C1 _ = "=sum(r2c:r[-1]c)" ActiveCell = .Cells(.Rows.Count, "Z").End(xlUp).Row .Cells(lastRow, "Z").FormulaR1C1 _ = "=sum(r2c:r[-1]c)" Range("T6:Z6").AutoFill Destination:=Range(Range("T6"), Cells(lastRow, "Z")) Columns("U:V").Select Selection.NumberFormat = "0%" End With End With End With End With End If Next sh End Sub Just to clarify things, I am trying to select all cells in Column T that contain data, and fill to the right, including Column Z, with the formatting in Column T. I can't use any kind of ctrl+end, or used range, because several tables that I am doing these operation on are coming from a large Pivot Table which is filtered for certain elements, and then copied/pasted to several sheets that I create on the fly. All of this is working except for getting the formatting in Column T to fill-right. Appreciate all your help up to this point and any help forthcoming. Regards, Ryan--- -- RyGuy "RyanH" wrote: Try this. This will copy you selection to the adjacent 4 columns. Selection.AutoFill Destination:=Range("T6:AB" & LastRow), Type:=xlFillDefault Hope this helps! -- Cheers, Ryan "ryguy7272" wrote: It just changed to 7 columns. :) That's trivial though. Thanks for the look Jim, but that didn't work. I thought it may be something like this: Selection.AutoFill Destination:=Range(("T6:" & lastRow), Cells(Lrow, 7)).Select However, this doesn't work wither. I'm sure it is possible, I'm just not seeing it right now... Any other ideas? Thanks, Ryan--- -- RyGuy "Jim Thomlinson" wrote: Try this... Not sure if you want to format all four columns or just the 4th column over. Your code is 4th column over... Selection.AutoFill Destination:=Range("T6:T" & lastRow).Offset(0, 4), Type:=xlFillFormats -- HTH... Jim Thomlinson "ryguy7272" wrote: I am trying to select a dynamic range and fill the formatting right four columns. The code that I have come up with is shown below: Selection.AutoFill Destination:=Range("T6:T" & lastRow).ActiveCell.Offset(0, 4).Select, Type:=xlFillFormats This is part of a larger loop, which works fine when this line removed. It is probably just a period or something; just can't figure it out. Thanks in advance, Ryan---- -- RyGuy |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Offset & Select
This is what finally worked for me:
Range("T6").Select Range(Selection, Selection.End(xlDown)).Select Selection.AutoFill Destination:=Range("T6:Z" & lastRow), Type:=xlFillFormats Both suggestions were helpful to get me 'back on track' with this!! Thanks guys!!! Regards, Ryan--- -- RyGuy "ryguy7272" wrote: Thanks for your persistence Jim, and welcome to the party Ryan. Here is the code, in it's entirety (maybe this will help or just make things more confusing). Sub LoopMac() Dim lastRow As Long Dim c As Variant Dim sh As Worksheet For Each sh In Worksheets If (sh.Name) < "C2_UnionQuery" And (sh.Name) < "Summary-Sheet" Then sh.Activate With sh Chng = Range("T6").Value lastRow = .Cells(.Rows.Count, "T").End(xlUp).Row For Each c In .Range("T6:T" & lastRow) If c.Value < "" Then c.Offset(, 1).Formula = "=IF(ISNUMBER(RC[-1]/RC[-9]),RC[-1]/RC[-9],""N/A"")" End If Next c With sh Chng = Range("U6").Value lastRow = .Cells(.Rows.Count, "T").End(xlUp).Row For Each c In .Range("T6:T" & lastRow) If c.Value < "" Then c.Offset(, 2).Formula = "=IF(ISNUMBER(RC[-1]/R2C21*R3C21),IF(RC[-1]/R2C21*R3C211,1,RC[-1]/R2C21*R3C21),""N/A"")" End If Next c With sh Chng = Range("X6").Value lastRow = .Cells(.Rows.Count, "T").End(xlUp).Row For Each c In .Range("T6:T" & lastRow) If c.Value < "" Then c.Offset(, 4).Formula = "=IF(ISNUMBER(RC[-2]),SUM(RC[-1],RC[-9])*RC[-2],0)" End If Next c With sh Chng = Range("Z6").Value lastRow = .Cells(.Rows.Count, "T").End(xlUp).Row For Each c In .Range("T6:T" & lastRow) If c.Value < "" Then c.Offset(, 6).Formula = "=IF(ISNUMBER(RC[-4]),RC[-2]+RC[-1]*RC[-4],0)" End If Next c Range("W6:W" & Cells(Rows.Count, "T").End(xlUp).Row).Interior.ColorIndex = 36 Range("Y6:Y" & Cells(Rows.Count, "T").End(xlUp).Row).Interior.ColorIndex = 36 lastRow = .Cells(.Rows.Count, "T").End(xlUp).Row .Cells(lastRow, "T").Activate ActiveCell = .Cells(.Rows.Count, "W").End(xlUp).Row .Cells(lastRow, "W").FormulaR1C1 _ = "=sum(r2c:r[-1]c)" ActiveCell = .Cells(.Rows.Count, "X").End(xlUp).Row .Cells(lastRow, "X").FormulaR1C1 _ = "=sum(r2c:r[-1]c)" ActiveCell = .Cells(.Rows.Count, "Y").End(xlUp).Row .Cells(lastRow, "Y").FormulaR1C1 _ = "=sum(r2c:r[-1]c)" ActiveCell = .Cells(.Rows.Count, "Z").End(xlUp).Row .Cells(lastRow, "Z").FormulaR1C1 _ = "=sum(r2c:r[-1]c)" Range("T6:Z6").AutoFill Destination:=Range(Range("T6"), Cells(lastRow, "Z")) Columns("U:V").Select Selection.NumberFormat = "0%" End With End With End With End With End If Next sh End Sub Just to clarify things, I am trying to select all cells in Column T that contain data, and fill to the right, including Column Z, with the formatting in Column T. I can't use any kind of ctrl+end, or used range, because several tables that I am doing these operation on are coming from a large Pivot Table which is filtered for certain elements, and then copied/pasted to several sheets that I create on the fly. All of this is working except for getting the formatting in Column T to fill-right. Appreciate all your help up to this point and any help forthcoming. Regards, Ryan--- -- RyGuy "RyanH" wrote: Try this. This will copy you selection to the adjacent 4 columns. Selection.AutoFill Destination:=Range("T6:AB" & LastRow), Type:=xlFillDefault Hope this helps! -- Cheers, Ryan "ryguy7272" wrote: It just changed to 7 columns. :) That's trivial though. Thanks for the look Jim, but that didn't work. I thought it may be something like this: Selection.AutoFill Destination:=Range(("T6:" & lastRow), Cells(Lrow, 7)).Select However, this doesn't work wither. I'm sure it is possible, I'm just not seeing it right now... Any other ideas? Thanks, Ryan--- -- RyGuy "Jim Thomlinson" wrote: Try this... Not sure if you want to format all four columns or just the 4th column over. Your code is 4th column over... Selection.AutoFill Destination:=Range("T6:T" & lastRow).Offset(0, 4), Type:=xlFillFormats -- HTH... Jim Thomlinson "ryguy7272" wrote: I am trying to select a dynamic range and fill the formatting right four columns. The code that I have come up with is shown below: Selection.AutoFill Destination:=Range("T6:T" & lastRow).ActiveCell.Offset(0, 4).Select, Type:=xlFillFormats This is part of a larger loop, which works fine when this line removed. It is probably just a period or something; just can't figure it out. Thanks in advance, Ryan---- -- RyGuy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using offset to select a range | Excel Programming | |||
| copy an offset value when select | Excel Programming | |||
| what does target.offset(0,-1).select mean here? | Excel Programming | |||
| Help in using an Offset in a .Range().Select | Excel Programming | |||
| Select and Copy Range using Offset | Excel Programming | |||
Linear Mode
