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...
 
J

joel

I rewrote the code below. this should work the same as your origina
code. there are two reasons you may get differences


1) The sort was using xlguess to determine if there is a header row.
Sometimes Excel guesses wrong. It is better to use either xlyes o
xlno. I changed it to yes.
2) Te number of rows may change. You code used specific rows for th
last row. I checked the number of rows and only used the exact numbe
of rows in the workbook. You were copying the data down the entir
columns which would effect the sort.


Before running this code delete all the rows after your actual data o
sheet 1. Cells that appear empty are not empty. The code below use
End(Xlup) which sometimes gets the wrong row if data was written to
cell and then later deleted.





with Sheets("sheet1")
LastRow = .Cells(Rows.Count, "J").End(xlUp).Row
set FormulaRange = .Range("L2:L" & LastRow)
FormulaRange.FormulaR1C1 = "=IF(RC[-2]<RC[-5], RC[-2]/RC[-5], 0)"

.Columns("L:L").NumberFormat = "0%"
.Range("L1").Value = "% Below Min"
.Columns("L:L").EntireColumn.AutoFit

.Range("A1").Subtotal GroupBy:=1, _
Function:=xlAverage, _
TotalList:=Array(4,5,6, 7, 8, 9, 10, 11, 12), _
Replace:=True, _
PageBreaks:=False,
SummaryBelowData:=True

.Outline.ShowLevels RowLevels:=2

Set visibleCells = .cells.SpecialCells(xlCellTypeVisible)
end with

with Sheets("sheet2")
.cells.Paste
.cells.Columns.AutoFit


LastRow = .Cells(Rows.Count, "J").End(xlUp).Row
.Rows("1:" & LastRow).Sort _
Key1:=.Range("D2"), _
Order1:=xlDescending, _
Header:=xlyes, _
OrderCustom:=1, _
MatchCase:=False,

.Columns("H:H").Insert

.Range("H2").FormulaR1C1 = "=RC[-1]*2"
.Range("H2").copy _
Destination:=.Range("H2:H" & LastRow)

.Range("H2:H" & LastRow).copy
.Range("H2:H" & LastRow).pastespecial _
paste:=xlpastevalues
.Range("H2:H" & LastRow).NumberFormat = "0"


.Range("H1") = "Order Point Qty"

.Columns("B:G").Delete
.Columns("C:G").Delete
.Columns("B:E").Insert

.Range("B2").FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-8)"

.Range("B2").copy _
Destination:=.Range("H2:H" & LastRow)

.Range("B2:H" & LastRow).copy
.Range("B2:H" & LastRow).pastespecial _
paste:=xlpastevalues

.Range("A1").cut
.Range("B1").Paste
.Range("B1") = "Location Code"
.Range("C1:C" & LastRow) = "0"

.Range("F1:F" & LastRow) = "10"
.Range("F1) = "Number of Days"
.Columns("F:F").ColumnWidth = 14.29

.Range("E1:E" & Lastrow).Copy _
destination:=.Range("C1:C" & Lastrow)

.Columns("D:E").Delete

.Range("A1").AutoFilter
.Range("A1").AutoFilter Field:=1, Criteria1:="Grand"

.columns.SpecialCells(xlCellTypeVisible).entirerow.delete


.Range("A1").AutoFilter Field:=1, Criteria1:="#VALUE!"

.columns.SpecialCells(xlCellTypeVisible).entirerow.delete

'remove autofilter
.Range("A1").AutoFilter

.cells.copy
end with

with Sheets("sheet2")
.cells.pastespecial _
paste:=xlpastevalues
.Range("A1").Select
end wit
 
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
Copying Data Down the Column But Only Based on What's in Column A 3
variable number of rows. 3
Why is Excel 2007 macro creating extra blank rows? 1
Complie Syntax Error 6
Help again!!! 1
works in 2007 but not in 2010 0
Help merging two VBA codes 2

Top