On Aug 16, 1:22 pm, JE McGimpsey <jemcgimp...@mvps.org> wrote:
> One way:
>
> Dim rFormulas As Range
> With ActiveSheet
> .Range("A10").Value = "Margin %"
> Set rFormulas = .Range("B10").Resize(1, _
> .Cells(4, .Columns.Count).End(xlToLeft).Column - 1)
> With .Range("B10")
> .FormulaR1C1 = "=(R[-5]C/R[-4]C)-1"
> .NumberFormat = "0.00%"
> .AutoFill Destination:=rFormulas, Type:=xlFillCopy
> End With
> End With
>
> In article <1187292511.733637.253...@i38g2000prf.googlegroups.com>,
>
>
>
> EE <pras1...@gmail.com> wrote:
> > Hi
>
> > I am a VBA newbie. I have a table of values In a range "A4" AL9". The
> > data in this table is dynamic and sometimes AL can be AX.
>
> > In B10, I have a formula that should be pasted till the last populated
> > column. In the above example from B10 till "AL10".
>
> > I know the place where I am stuck. (marked between stars in the code
> > below).
>
> > Thanks in advance.
>
> > **************
> > ActiveSheet.Range("A10").Select
> > ActiveCell.FormulaR1C1 = "Margin %"
> > Range("B10").Select
> > ActiveCell.FormulaR1C1 = "=+(R[-5]C/R[-4]C)-1"
> > Range("B10").Select
> > Selection.Style = "Percent"
> > Selection.NumberFormat = "0.00%"
>
> > Application.ScreenUpdating = False
> > CalcStatus = Application.Calculation
> > Application.Calculation = xlCalculationManual
>
> > ***************************************
> > I think this refernce for the last column is where I am lost. How do I
> > refer to the last column.
>
> > LastColumn = ActiveSheet.Cells(4,
> > Columns.count).End(xlToLeft).Column
>
> > *************************************
> > Range("B10").Copy
> > Range("B11:LastColumn").PasteSpecial _
> > Paste:=xlPasteFormulas, _
> > Operation:=xlNone, _
> > SkipBlanks:=False, _
> > Transpose:=False
> > Application.Calculation = CalcStatus
> > Application.ScreenUpdating = True- Hide quoted text -
>
> - Show quoted text -
Hi all
Thanks for you inputs.
I figured a different way of doing it. I created a "Dynamic Named
Range" for the Target Range. Maybe a convoluted process but I was in a
hurry. I implemented the changes you suggested later. Posting what I
did for learning to the newbies like me who use the forum.
********************************
''''''For calculating Margin %
ActiveSheet.Range("A10").Select
ActiveCell.FormulaR1C1 = "Margin %"
Range("B10").Select
ActiveCell.FormulaR1C1 = "=+(R[-5]C/R[-4]C)-1"
Range("B10").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.00%"
'''''''''''For Getting PASTE RANGE
Dim Lcol As Long
Dim lRow As Long
Dim rStart As Range
Dim rCtyShtFltRng As Range
Dim wActSheet As Worksheet
Dim LastcolRng As Range
Set wActSheet = Worksheets("Output")
With wActSheet
Set rStart = .Range("C10")
Set LastcolRng = Range("A4") (This is the first row (header)
of my table that determines the last column)
lRow = rStart.Row
Lcol = LastcolRng.End(xlToRight).Column
Set rCtyShtFltRng = .Range(rStart, .Cells(lRow, Lcol))
.Names.Add Name:="PasteRange", RefersTo:=rCtyShtFltRng
Worksheets("Output").Select
.Range("PasteRange").Select
End With
''''''''''''''''' End Paste Range
Application.ScreenUpdating = False
CalcStatus = Application.Calculation
Application.Calculation = xlCalculationManual
' LastColumn = ActiveSheet.Cells(4,
Columns.Address).End(xlToLeft).Column
Range("B10").Copy
Range("PasteRange").PasteSpecial _
Paste:=xlPasteFormulas, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.Calculation = CalcStatus
Application.ScreenUpdating = True
Thanks again. I do not post that often but I have been using
suggestion from here for over an year now.
Best
Prasad
|
Similar Threads
