VBA MAcro Code Question..Is it possible??

[Jack_58]

How would it be possible to write code to have my macro take the current figure that is in the sum of the column and continue with the macro? The current code looks like this

ActiveCell.FormulaR1C1 = "=RC[-1]/3223894.484"
ActiveCell.Select
Selection.Copy

However the Number will be a different figure, based on the rest of the figures in the column.

Is it possible to have Excel automatically detect and copy the figure calculated in that cell, which is Dynamic?


Thanks in advance.



Jack

[Juan Pablo González]

ActiveCell.Value

will give you the result of the active cell.

[bjwillingham]

Can you rephrase your question? I do not understand what you are asking, nor do I understand the scenario you are describing.

[Jack_58]

When I replace ActiveCell.FormulaR1C1 = "=RC[-1]/3223894.484" with ActiveCell.FormulaR1C1 = "=RC[-1]/Activecell.value", I am Receiving a NAME? Error.

Please let me know if I am entering this formula correctly.


Thanks



Jack

[Juan Pablo González]

Ok, that's one strange formula. You want in say, B2, to get the result of diving A2 with the value that is in B2 ?

If so then..

ActiveCell.FormulaR1C1 = "=RC[-1]/" Activecell.value

[Jack_58]

Here is my entire code for this project,
Selection.QueryTable.Refresh BackgroundQuery:=False
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(15), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Application.Goto Reference:="Range2"
Selection.Copy
Sheets("AAS Summary").Select
ActiveCell.Offset(0, 3).Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(3, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveWindow.SmallScroll ToRight:=-2
Selection.AutoFilter Field:=4, Criteria1:="<>"
ActiveCell.Offset(-2, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Selection.AutoFilter
ActiveCell.Offset(-1, -3).Range("A1:AA1").Select
Selection.ClearContents
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveCell.Range("A1:D1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("A7").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "0.00"
Selection.Cut
ActiveCell.Offset(-1, -16).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = " "
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = "1572018.34"
ActiveCell.Offset(-58, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/" Activecell.value
ActiveCell.Value.Cell
ActiveCell.Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.NumberFormat = "0.00%"
ActiveCell.Offset(0, -2).Range("A1").Select
Sheets("AAS Omits").Select
Selection.RemoveSubtotal
Range("A7").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:O130").Select
ActiveSheet.PageSetup.PrintArea = "$A$7:$O$136"
ActiveCell.Offset(129, 13).Range("A1").Select
ActiveCell.FormulaR1C1 = "Total"
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaR1C1 = "=SUM(R[-129]C:R[-1]C)"
ActiveCell.Offset(-129, -14).Range("A1").Select
Sheets("BBC Omits").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(14), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Application.Goto Reference:="RangeBBC"
Selection.Copy
Sheets("BBC Summary").Select
ActiveCell.Offset(1, 3).Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(2, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="<>"
ActiveCell.Offset(-1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Selection.AutoFilter
ActiveCell.Offset(-1, -3).Range("A1:AA1").Select
Selection.ClearContents
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.Range("A1:E1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("A7").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "0.00"
Selection.Cut
ActiveCell.Offset(-1, -15).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = " "
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = "3223894.484"
ActiveCell.Offset(-142, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/"
ActiveCell.Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.NumberFormat = "0.00%"
ActiveCell.Offset(0, -2).Range("A1").Select
Sheets("BBC Omits").Select
Selection.RemoveSubtotal
Range("A7").Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveCell.Range("A1:N1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:N329").Select
ActiveSheet.PageSetup.PrintArea = "$A$7:$N$335"
ActiveCell.Offset(328, 12).Range("A1").Select
ActiveCell.FormulaR1C1 = "Total"
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormulaR1C1 = "=SUM(R[-328]C:R[-1]C)"
ActiveCell.Offset(-328, -13).Range("A1").Select
Sheets("AAS Omits").Select
End Sub

I hope this will better able that people that can and are helping me with this problem.

Thanks Again



Jack

[Juan Pablo González]

By PM:

Originally Posted by Jack_58

What I need the formula to do is,
Divide the Contents of B2, by the Value in the last row of Column B
So normally I would use,

=B7/Whatever the value in the last row would be

I want to be able to place this into a macro, currently when I write the macro is remembers the last value and places an incorrect figure.


Thanks for any help!

Jack