Offset & Select

[ryguy7272]

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

 

[Jim Thomlinson]

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

 

[ryguy7272]

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

 

[Jim Thomlinson]

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...

 

[RyanH]

Try this. This will copy you selection to the adjacent 4 columns.

Selection.AutoFill Destination:=Range("T6:AB" & LastRow),
Type:=xlFillDefault

Hope this helps!

 

[RyanH]

I' still not sure about what you are asking, by your code alone. Please
describe what you are trying to accomplish.

 

[ryguy7272]

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*R3C21>1,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---

 

[ryguy7272]

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

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*R3C21>1,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

Try this. This will copy you selection to the adjacent 4 columns.

Selection.AutoFill Destination:=Range("T6:AB" & LastRow),
Type:=xlFillDefault

Hope this helps!