PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Different Results from the Same Macro

Reply
Thread Tools Rate Thread

Different Results from the Same Macro

 
 
childofthe1980s
Guest
Posts: n/a
 
      28th Dec 2009
Hello:

At the end of this posting, I have VBA code for a macro that I created.
This macro was created in Excel 2007 macro. What's puzzling me is that this
macro gives different results everytime that it is used. It is run against
the same set of data, so I do not understand why it is producing different
data in the spreadsheet.

The attached macro code "runs against data" in a spreadsheet in order to
generate another spreadsheet.

Different results are given every time the macro runs. That's not good.
The results need to be consistent. Is there anything in the attached code
that can be modified to allow for consistency?

In terms of what is being seen different each time the macro runs, I am
seeing different numbers of rows, different records in the columns, .....just
no consistency.

childofthe1980s

ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.SmallScroll ToRight:=-5
Range("L2:L" & Cells(Cells.Rows.Count, "J").End(xlUp).Row).FormulaR1C1 =
"=IF(RC[-2]<RC[-5], RC[-2]/RC[-5], 0)"
Columns("L:L").NumberFormat = "0%"
Range("L2:L" & Cells(Cells.Rows.Count, "J").End(xlUp).Row).Value =
Range("L2:L" & Cells(Cells.Rows.Count, "J").End(xlUp).Row).Value
Range("L1").Value = "% Below Min"
Application.CutCopyMode = False
Columns("L:L").EntireColumn.AutoFit
ActiveWindow.LargeScroll ToRight:=-1
Range("A1").Select
Selection.Subtotal GroupBy:=1, Function:=xlAverage, TotalList:=Array(4,
5, _
6, 7, 8, 9, 10, 11, 12), Replace:=True, PageBreaks:=False,
SummaryBelowData:= _
True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Cells.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet2").Select
Cells.Select
ActiveSheet.Paste
Selection.Columns.AutoFit
Range("A1").Select
Application.CutCopyMode = False
Range("A1:L620").Sort Key1:=Range("D2"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Range("H2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*2"
Range("H2").Select
Selection.Copy
Columns("H:H").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "0"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Order Point Qty"
Columns("B:G").Select
Selection.Delete Shift:=xlToLeft
Columns("C:G").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Application.WindowState = xlNormal
Range("B2").Select
ActiveCell.FormulaR1C1 = "'=LEFT(A2,LEN(A2)-8)"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-8)"
Range("B2").Select
Selection.Copy
Columns("B:B").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Selection.Cut
Range("B1").Select
ActiveSheet.Paste
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("B1").Select
ActiveCell.FormulaR1C1 = "CH"
Range("B1").Select
Selection.Copy
Columns("B:B").Select
ActiveSheet.Paste
Range("C1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("C1").Select
Selection.Copy
Columns("C:C").Select
ActiveSheet.Paste
Range("B1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Location Code"
Range("F1").Select
ActiveCell.FormulaR1C1 = "10"
Range("F1").Select
Selection.Copy
Columns("F:F").Select
ActiveSheet.Paste
Range("F1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Number of Days"
Range("F2").Select
Columns("F:F").ColumnWidth = 14.29
Range("E1:E261").Select
Selection.Copy
Range("C1:C261").Select
ActiveSheet.Paste
Columns("D:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Selection.AutoFilter
Range("A2").Select
Selection.AutoFilter Field:=1, Criteria1:="Grand"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
Selection.AutoFilter Field:=1, Criteria1:="#VALUE!"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
Selection.AutoFilter Field:=1
Selection.AutoFilter
Cells.Select
Selection.Copy
Sheets("Sheet3").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Cells.EntireColumn.AutoFit
Range("A1").Select
Application.DisplayAlerts = False
Sheets("Sheet1").Delete
Application.DisplayAlerts = True
Application.DisplayAlerts = False
Sheets("Sheet2").Delete
Application.DisplayAlerts = True
Range("A1").Select
 
Reply With Quote
 
 
 
 
childofthe1980s
Guest
Posts: n/a
 
      28th Dec 2009
disregard...I figured it out.

"childofthe1980s" wrote:

> Hello:
>
> At the end of this posting, I have VBA code for a macro that I created.
> This macro was created in Excel 2007 macro. What's puzzling me is that this
> macro gives different results everytime that it is used. It is run against
> the same set of data, so I do not understand why it is producing different
> data in the spreadsheet.
>
> The attached macro code "runs against data" in a spreadsheet in order to
> generate another spreadsheet.
>
> Different results are given every time the macro runs. That's not good.
> The results need to be consistent. Is there anything in the attached code
> that can be modified to allow for consistency?
>
> In terms of what is being seen different each time the macro runs, I am
> seeing different numbers of rows, different records in the columns, .....just
> no consistency.
>
> childofthe1980s
>
> ActiveWindow.LargeScroll ToRight:=1
> ActiveWindow.SmallScroll ToRight:=-5
> Range("L2:L" & Cells(Cells.Rows.Count, "J").End(xlUp).Row).FormulaR1C1 =
> "=IF(RC[-2]<RC[-5], RC[-2]/RC[-5], 0)"
> Columns("L:L").NumberFormat = "0%"
> Range("L2:L" & Cells(Cells.Rows.Count, "J").End(xlUp).Row).Value =
> Range("L2:L" & Cells(Cells.Rows.Count, "J").End(xlUp).Row).Value
> Range("L1").Value = "% Below Min"
> Application.CutCopyMode = False
> Columns("L:L").EntireColumn.AutoFit
> ActiveWindow.LargeScroll ToRight:=-1
> Range("A1").Select
> Selection.Subtotal GroupBy:=1, Function:=xlAverage, TotalList:=Array(4,
> 5, _
> 6, 7, 8, 9, 10, 11, 12), Replace:=True, PageBreaks:=False,
> SummaryBelowData:= _
> True
> ActiveSheet.Outline.ShowLevels RowLevels:=2
> Cells.Select
> Selection.SpecialCells(xlCellTypeVisible).Select
> Selection.Copy
> Sheets("Sheet2").Select
> Cells.Select
> ActiveSheet.Paste
> Selection.Columns.AutoFit
> Range("A1").Select
> Application.CutCopyMode = False
> Range("A1:L620").Sort Key1:=Range("D2"), Order1:=xlDescending, Header:= _
> xlGuess, OrderCustom:=1, MatchCase:=False,
> Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
> Columns("H:H").Select
> Selection.Insert Shift:=xlToRight
> Range("H2").Select
> ActiveCell.FormulaR1C1 = "=RC[-1]*2"
> Range("H2").Select
> Selection.Copy
> Columns("H:H").Select
> ActiveSheet.Paste
> Application.CutCopyMode = False
> Selection.Copy
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> Selection.NumberFormat = "0"
> Range("H1").Select
> ActiveCell.FormulaR1C1 = "Order Point Qty"
> Columns("B:G").Select
> Selection.Delete Shift:=xlToLeft
> Columns("C:G").Select
> Selection.Delete Shift:=xlToLeft
> Columns("B:B").Select
> Selection.Insert Shift:=xlToRight
> Selection.Insert Shift:=xlToRight
> Selection.Insert Shift:=xlToRight
> Selection.Insert Shift:=xlToRight
> Application.WindowState = xlNormal
> Range("B2").Select
> ActiveCell.FormulaR1C1 = "'=LEFT(A2,LEN(A2)-8)"
> Range("B2").Select
> ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-8)"
> Range("B2").Select
> Selection.Copy
> Columns("B:B").Select
> ActiveSheet.Paste
> Application.CutCopyMode = False
> Selection.Copy
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Range("A1").Select
> Application.CutCopyMode = False
> Selection.Cut
> Range("B1").Select
> ActiveSheet.Paste
> Columns("A:A").Select
> Selection.Delete Shift:=xlToLeft
> Range("B1").Select
> ActiveCell.FormulaR1C1 = "CH"
> Range("B1").Select
> Selection.Copy
> Columns("B:B").Select
> ActiveSheet.Paste
> Range("C1").Select
> Application.CutCopyMode = False
> ActiveCell.FormulaR1C1 = "0"
> Range("C1").Select
> Selection.Copy
> Columns("C:C").Select
> ActiveSheet.Paste
> Range("B1").Select
> Application.CutCopyMode = False
> ActiveCell.FormulaR1C1 = "Location Code"
> Range("F1").Select
> ActiveCell.FormulaR1C1 = "10"
> Range("F1").Select
> Selection.Copy
> Columns("F:F").Select
> ActiveSheet.Paste
> Range("F1").Select
> Application.CutCopyMode = False
> ActiveCell.FormulaR1C1 = "Number of Days"
> Range("F2").Select
> Columns("F:F").ColumnWidth = 14.29
> Range("E1:E261").Select
> Selection.Copy
> Range("C1:C261").Select
> ActiveSheet.Paste
> Columns("D:E").Select
> Application.CutCopyMode = False
> Selection.Delete Shift:=xlToLeft
> Range("A1").Select
> Selection.AutoFilter
> Range("A2").Select
> Selection.AutoFilter Field:=1, Criteria1:="Grand"
> Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> Selection.EntireRow.Delete
> Selection.AutoFilter Field:=1, Criteria1:="#VALUE!"
> Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> Selection.EntireRow.Delete
> Selection.AutoFilter Field:=1
> Selection.AutoFilter
> Cells.Select
> Selection.Copy
> Sheets("Sheet3").Select
> Cells.Select
> Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
> xlNone, SkipBlanks:=False, Transpose:=False
> Cells.EntireColumn.AutoFit
> Range("A1").Select
> Application.DisplayAlerts = False
> Sheets("Sheet1").Delete
> Application.DisplayAlerts = True
> Application.DisplayAlerts = False
> Sheets("Sheet2").Delete
> Application.DisplayAlerts = True
> Range("A1").Select
 
Reply With Quote
 
CellShocked
Guest
Posts: n/a
 
      29th Dec 2009


I would re-record it and use operations like "paste special", "Column
widths" or the like if you are seeing different cell formatting.

If you are always going to be operating at the same column of data
every time, you could use named ranges to declare sets of data, and then
making calls to the data, and shuffling around copies of data sets are
much easier than when you have to declare sheet names and range
declarations have to constantly be made.

If you know what a named range is, that is what I am talking about. If
you do not, you will find their use invaluable to spreadsheet work.

Your macro looks like a recording and it contains a lot of cursor moves
that can be resolved directly to actual cell calls or such.

You could trim a lot of that code away.


On Mon, 28 Dec 2009 05:39:01 -0800, childofthe1980s
<(E-Mail Removed)> wrote:

>Hello:
>
>At the end of this posting, I have VBA code for a macro that I created.
>This macro was created in Excel 2007 macro. What's puzzling me is that this
>macro gives different results everytime that it is used. It is run against
>the same set of data, so I do not understand why it is producing different
>data in the spreadsheet.
>
>The attached macro code "runs against data" in a spreadsheet in order to
>generate another spreadsheet.
>
>Different results are given every time the macro runs. That's not good.
>The results need to be consistent. Is there anything in the attached code
>that can be modified to allow for consistency?
>
>In terms of what is being seen different each time the macro runs, I am
>seeing different numbers of rows, different records in the columns, .....just
>no consistency.
>
>childofthe1980s
>
>ActiveWindow.LargeScroll ToRight:=1
> ActiveWindow.SmallScroll ToRight:=-5
> Range("L2:L" & Cells(Cells.Rows.Count, "J").End(xlUp).Row).FormulaR1C1 =
>"=IF(RC[-2]<RC[-5], RC[-2]/RC[-5], 0)"
> Columns("L:L").NumberFormat = "0%"
> Range("L2:L" & Cells(Cells.Rows.Count, "J").End(xlUp).Row).Value =
>Range("L2:L" & Cells(Cells.Rows.Count, "J").End(xlUp).Row).Value
> Range("L1").Value = "% Below Min"
> Application.CutCopyMode = False
> Columns("L:L").EntireColumn.AutoFit
> ActiveWindow.LargeScroll ToRight:=-1
> Range("A1").Select
> Selection.Subtotal GroupBy:=1, Function:=xlAverage, TotalList:=Array(4,
>5, _
> 6, 7, 8, 9, 10, 11, 12), Replace:=True, PageBreaks:=False,
>SummaryBelowData:= _
> True
>ActiveSheet.Outline.ShowLevels RowLevels:=2
> Cells.Select
> Selection.SpecialCells(xlCellTypeVisible).Select
> Selection.Copy
> Sheets("Sheet2").Select
> Cells.Select
> ActiveSheet.Paste
> Selection.Columns.AutoFit
> Range("A1").Select
> Application.CutCopyMode = False
> Range("A1:L620").Sort Key1:=Range("D2"), Order1:=xlDescending, Header:= _
> xlGuess, OrderCustom:=1, MatchCase:=False,
>Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
> Columns("H:H").Select
> Selection.Insert Shift:=xlToRight
> Range("H2").Select
> ActiveCell.FormulaR1C1 = "=RC[-1]*2"
> Range("H2").Select
> Selection.Copy
> Columns("H:H").Select
> ActiveSheet.Paste
> Application.CutCopyMode = False
> Selection.Copy
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> Selection.NumberFormat = "0"
> Range("H1").Select
> ActiveCell.FormulaR1C1 = "Order Point Qty"
> Columns("B:G").Select
> Selection.Delete Shift:=xlToLeft
> Columns("C:G").Select
> Selection.Delete Shift:=xlToLeft
> Columns("B:B").Select
> Selection.Insert Shift:=xlToRight
> Selection.Insert Shift:=xlToRight
> Selection.Insert Shift:=xlToRight
> Selection.Insert Shift:=xlToRight
> Application.WindowState = xlNormal
> Range("B2").Select
> ActiveCell.FormulaR1C1 = "'=LEFT(A2,LEN(A2)-8)"
> Range("B2").Select
> ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-8)"
> Range("B2").Select
> Selection.Copy
> Columns("B:B").Select
> ActiveSheet.Paste
> Application.CutCopyMode = False
> Selection.Copy
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>SkipBlanks _
> :=False, Transpose:=False
> Range("A1").Select
> Application.CutCopyMode = False
> Selection.Cut
> Range("B1").Select
> ActiveSheet.Paste
> Columns("A:A").Select
> Selection.Delete Shift:=xlToLeft
> Range("B1").Select
> ActiveCell.FormulaR1C1 = "CH"
> Range("B1").Select
> Selection.Copy
> Columns("B:B").Select
> ActiveSheet.Paste
> Range("C1").Select
> Application.CutCopyMode = False
> ActiveCell.FormulaR1C1 = "0"
> Range("C1").Select
> Selection.Copy
> Columns("C:C").Select
> ActiveSheet.Paste
> Range("B1").Select
> Application.CutCopyMode = False
> ActiveCell.FormulaR1C1 = "Location Code"
> Range("F1").Select
> ActiveCell.FormulaR1C1 = "10"
> Range("F1").Select
> Selection.Copy
> Columns("F:F").Select
> ActiveSheet.Paste
> Range("F1").Select
> Application.CutCopyMode = False
> ActiveCell.FormulaR1C1 = "Number of Days"
> Range("F2").Select
> Columns("F:F").ColumnWidth = 14.29
> Range("E1:E261").Select
> Selection.Copy
> Range("C1:C261").Select
> ActiveSheet.Paste
> Columns("D:E").Select
> Application.CutCopyMode = False
> Selection.Delete Shift:=xlToLeft
> Range("A1").Select
> Selection.AutoFilter
> Range("A2").Select
> Selection.AutoFilter Field:=1, Criteria1:="Grand"
> Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> Selection.EntireRow.Delete
> Selection.AutoFilter Field:=1, Criteria1:="#VALUE!"
> Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> Selection.EntireRow.Delete
> Selection.AutoFilter Field:=1
> Selection.AutoFilter
> Cells.Select
> Selection.Copy
> Sheets("Sheet3").Select
> Cells.Select
> Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
> xlNone, SkipBlanks:=False, Transpose:=False
> Cells.EntireColumn.AutoFit
> Range("A1").Select
> Application.DisplayAlerts = False
> Sheets("Sheet1").Delete
> Application.DisplayAlerts = True
> Application.DisplayAlerts = False
> Sheets("Sheet2").Delete
> Application.DisplayAlerts = True
> Range("A1").Select
 
Reply With Quote
 
 
 
Reply

« Re: MuliColumn Combo | Possible to lock a range from calculating formulas? »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Keystroke macro results . . . Rich Microsoft Excel Programming 7 1st Apr 2010 03:20 PM
A little help please on a macro to display results. Dave Microsoft Excel Programming 0 27th Jan 2010 05:21 PM
Storing Macro Results daphoenix Microsoft Excel Worksheet Functions 1 26th Jun 2008 05:48 PM
Different results when running macro vs stepping into macro Ken Steinhoff Microsoft Excel Programming 4 20th Sep 2007 05:49 PM
How can I list the results of my macro without overwritng previous results? =?Utf-8?B?bWF0dGlw?= Microsoft Excel Programming 3 28th Nov 2003 03:45 AM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 08:03 PM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top