Different Results from the Same Macro

C

childofthe1980s

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
 
C

childofthe1980s

disregard...I figured it out.

childofthe1980s said:
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
Click to expand...
 
C

CellShocked

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.


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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cleanup this macro please 3
variable number of rows. 2
Copying Data Down the Column But Only Based on What's in Column A 1
Macro should Save and Overwrite file 1
Complie Syntax Error 6
File Name changes with dates and Deleting Rows of Zeros 0
Help again!!! 1
Totals An Easier Way?? 5

Top