Copying a value till the last populated column

[EE]

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

 

[Dave Peterson]

I think this was your question...

Range("B11:LastColumn").PasteSpecial _

becomes:

Range("B11:B" & LastColumn).PasteSpecial _

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

 

[JE McGimpsey]

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

 

[EE]

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

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