Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Need to turn this in to VBA
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default Need to turn this in to VBA
Here is a recorded macro that i need to turn into VBA. WHat i need to change
is I need it to count the rows then place the totals at the bottom. On the
pivottable i need it to count the number of different investor numbers.

Sub PivotTable2()
'
' PivotTable2 Macro
' Macro recorded 10/24/2006 by J922703
'

'
Range("A1").Select
Selection.End(xlDown).Select
Range("B72").Select
ActiveCell.FormulaR1C1 = "=COUNT(R[-70]C:R[-1]C)"
Range("C72").Select
ActiveCell.FormulaR1C1 = "=COUNT(R[-70]C:R[-1]C)"
Range("D72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("E72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("F72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("H72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("I72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("K72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("L72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("M72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("N72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("O72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("P72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("Q72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("R72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("Y72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("Z72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"qry_PoExport!R1C1:R71C26").CreatePivotTable TableDestination:="",
TableName _
:="PivotTable3", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields ("Investor_Number")

.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("BegSchedBal"), "Sum of BegSchedBal",
xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("SchedPrin"), "Sum of SchedPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("LiqPrin"), "Sum of LiqPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("EndActBal"), "Sum of EndActBal", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("Ancillary Fees"), "Count of Ancillary
Fees", _
xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("B4").Select
ActiveSheet.PivotTables("PivotTable3").PivotSelect "", xlDataAndLabel,
True
ActiveSheet.PivotTables("PivotTable3").Format xlTable7
Range("F3").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields ("Count of Ancillary
Fees"). _
Function = xlSum
Range("G3").Select
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 210
Default Need to turn this in to VBA
You can get an row count on your sheet by using
activesheet.usedrange.rows.count
--
http://HelpExcel.com

516-984-0252


"jln via OfficeKB.com" wrote:

Here is a recorded macro that i need to turn into VBA. WHat i need to change
is I need it to count the rows then place the totals at the bottom. On the
pivottable i need it to count the number of different investor numbers.

Sub PivotTable2()
'
' PivotTable2 Macro
' Macro recorded 10/24/2006 by J922703
'

'
Range("A1").Select
Selection.End(xlDown).Select
Range("B72").Select
ActiveCell.FormulaR1C1 = "=COUNT(R[-70]C:R[-1]C)"
Range("C72").Select
ActiveCell.FormulaR1C1 = "=COUNT(R[-70]C:R[-1]C)"
Range("D72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("E72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("F72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("H72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("I72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("K72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("L72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("M72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("N72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("O72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("P72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("Q72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("R72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("Y72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("Z72").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"qry_PoExport!R1C1:R71C26").CreatePivotTable TableDestination:="",
TableName _
:="PivotTable3", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields ("Investor_Number")

.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("BegSchedBal"), "Sum of BegSchedBal",
xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("SchedPrin"), "Sum of SchedPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("LiqPrin"), "Sum of LiqPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("EndActBal"), "Sum of EndActBal", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("Ancillary Fees"), "Count of Ancillary
Fees", _
xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("B4").Select
ActiveSheet.PivotTables("PivotTable3").PivotSelect "", xlDataAndLabel,
True
ActiveSheet.PivotTables("PivotTable3").Format xlTable7
Range("F3").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields ("Count of Ancillary
Fees"). _
Function = xlSum
Range("G3").Select
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default Need to turn this in to VBA
Here is an updated work in progress. I have it doing the total now at the
right place but it not totaling all the columns that i need can some one see
what im doim wrong. It only totals columns C -F I think the problem is in my
resize.


Sub Forpayoffmismatch()
'
' PivotTable2 Macro
' Macro recorded 10/24/2006 by J922703
'

Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 As Range

Set rng4 = Worksheets(1) _
Cells(Rows.Count, 1).End(xlUp)(2)
Set rng5 = Worksheets(1).Cells(Rows.Count, 3).End(xlUp)(2)
rng5.Resize(1, 4).FormulaR1C1 = "=Sum(R5C:R[-1])"


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"qry_PoExport!R1C1:R71C26").CreatePivotTable TableDestination:="",
TableName _
:="PivotTable3", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields ("Investor_Number")

.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("BegSchedBal"), "Sum of BegSchedBal",
xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("SchedPrin"), "Sum of SchedPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("LiqPrin"), "Sum of LiqPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("EndActBal"), "Sum of EndActBal", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("Ancillary Fees"), "Count of Ancillary
Fees", _
xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("B4").Select
ActiveSheet.PivotTables("PivotTable3").PivotSelect "", xlDataAndLabel,
True
ActiveSheet.PivotTables("PivotTable3").Format xlTable7
Range("F3").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields ("Count of Ancillary
Fees"). _
Function = xlSum
Range("G3").Select
End Sub




galimi wrote:
You can get an row count on your sheet by using
activesheet.usedrange.rows.count
Here is a recorded macro that i need to turn into VBA. WHat i need to change
is I need it to count the rows then place the totals at the bottom. On the
[quoted text clipped - 86 lines]
Range("G3").Select
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default Need to turn this in to VBA
OK i got that working but the resize totals ever column and there only
certain ones that i need. Is there something I can use other then resize?

jln wrote:
Here is an updated work in progress. I have it doing the total now at the
right place but it not totaling all the columns that i need can some one see
what im doim wrong. It only totals columns C -F I think the problem is in my
resize.

Sub Forpayoffmismatch()
'
' PivotTable2 Macro
' Macro recorded 10/24/2006 by J922703
'

Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 As Range

Set rng4 = Worksheets(1) _
.Cells(Rows.Count, 1).End(xlUp)(2)
Set rng5 = Worksheets(1).Cells(Rows.Count, 3).End(xlUp)(2)
rng5.Resize(1, 4).FormulaR1C1 = "=Sum(R5C:R[-1])"


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"qry_PoExport!R1C1:R71C26").CreatePivotTable TableDestination:="",
TableName _
:="PivotTable3", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields ("Investor_Number")

.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("BegSchedBal"), "Sum of BegSchedBal",
xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("SchedPrin"), "Sum of SchedPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("LiqPrin"), "Sum of LiqPrin", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("EndActBal"), "Sum of EndActBal", xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.
PivotTables( _
"PivotTable3").PivotFields("Ancillary Fees"), "Count of Ancillary
Fees", _
xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("B4").Select
ActiveSheet.PivotTables("PivotTable3").PivotSelect "", xlDataAndLabel,
True
ActiveSheet.PivotTables("PivotTable3").Format xlTable7
Range("F3").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields ("Count of Ancillary
Fees"). _
Function = xlSum
Range("G3").Select
End Sub

You can get an row count on your sheet by using
activesheet.usedrange.rows.count
[quoted text clipped - 3 lines]
Range("G3").Select
End Sub

--
Message posted via http://www.officekb.com

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
WHY DOES 1E4 TURN INTO 1.00 E + 04? THANKS Lisa B. Excel Discussion (Misc queries) 1 July 2nd 05 12:48 AM
How do i turn it off ceemo Excel Discussion (Misc queries) 2 May 25th 05 12:25 PM
turn dll into xll alvaro Excel Programming 1 February 27th 05 06:38 PM
turn $12 to $12,000 john doep Excel Programming 0 February 5th 04 01:02 AM
Code to automatically turn on and turn off Track Changes John[_46_] Excel Programming 1 October 7th 03 02:22 AM


All times are GMT +1. The time now is 07:06 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement