Different Results from the Same Macro

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
0
Utf
12/28/2009 1:39:01 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
1027 Views

Similar Articles

[PageSpeed] 17

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
0
Utf
12/28/2009 2:14:01 PM
I rewrote the code below.  this should work the same as your original
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 or
xlno.  I changed it to yes.
2) Te number of rows may change.  You code used specific rows for the
last row.  I checked the number of rows and only used the exact number
of rows in the workbook.  You were copying the data down the entire
columns which would effect the sort.


Before running this code delete all the rows after your actual data on
sheet 1.  Cells that appear empty are not empty.  The code below uses
End(Xlup) which sometimes gets the wrong row if data was written to a
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 with


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=165411

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

0
joel
12/28/2009 2:30:55 PM

  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
<childofthe1980s@discussions.microsoft.com> 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
0
CellShocked
12/29/2009 2:15:30 AM
Reply:

Similar Artilces:

referencing a cell in a sheet where the sheet name is a result
My workbook has sheets named for each day of the month, (March 1, March 2...). On March 2, I'd like to reference a cell on March 1 (and March 3 would reference March 2, etc. so I need a formula that will reference "DAY - 1" so that I can copy it to subsequent pages and they will reference the cell in the prior day's spreadsheet). I can create the text "March 1" in a cell in March 2, but how do I use that in a formula to reference the correct cell. Thanks! Tim Try: =INDIRECT("'March "&RIGHT(CELL("filename",A1),2)-1&"&#...

Pulling data daily from a differently named report everyday
Hello, I have a spreadsheet where I use formulas to pull data from multiple reports on a shared server everyday. What I do is to save those reports under a certain name everyday since the formulas have to have the static report name in them to pull data. (For example, A1 may pull data from c:\reportfolder\reportA, B1 may pull from c: \reportfolder2\reportB, etc). In these report folders our IT group runs a new report in it everyday but of course they change the name of the report every day (to reflect the date) . . so in the reportfolder there will be "reportA-3-12-2009", and "...

Help w/ getting F2 into a macro
I have an Excel 2000 speadsheet with 9000 rows of data which someone imported from some other software. The one column should contain numeric data, but the import left it with some preceding spaces and a following asterisk. This needs to be converted to numerical format which can be referenced by formulae. I really don't want to plow through 9000 lines and am trying to set up a macro to do this. The following keystrokes have the right effect. F2 (function key to go to edit mode) Backspace (to delete the training asterisk character) Enter (which writes back to the cell a...

Macro to change cell text color
OBJECTIVE: I select a range of cells. I want a macro I can run tha changes the color of the text based on 3 criteria: (1) if a numeric value w/o an = sign or any mathematical operators then RED (2) if a forumla w/ an = sign, but no other operators, then GREEN (3) if a formula w/ an = sign and operators, then BLUE Can anyone post this? Thanks. - Ji -- James ----------------------------------------------------------------------- James C's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1211 View this thread: http://www.excelforum.com/showthread.php?threadid=3953...

Ribbon X macro not available
I have created a Ribbon and everything was working fine until the last button was added then I got the macro not available error. I have all my trust settings wide open. Is there a limit to the number of items that can be added to a ribbon. Here is my XML The error happens on anything below Character Count <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" > <ribbon > <tabs > <tab id="Tab2" label="RPExcel07"> <group id="Group1" > <menu id="Menu...

Different margins on different pages
I am setting up a template for letters at work. The first page of our letterhead has one ste of margins and all subsequent pages will have a different sent of margins. I know that section breaks can be used, but if staff copy and paste text from existing letters into this new template, the section break moves. Is there any way of locking the section break to the page? so the pasted text goes over the top of it? Hi Lilly80, You could setup your document with a 'different first page' layout. That allows the first page to have a different set of margins to the rest o...

Switchboard hangs when running a macro
Access 2003: I've recently discovered the Switchboard - a grand concept; wish it wasn't so hidden... When running a macro from a form, no problem. But when running the same macro from the Switchboard, Access hangs. It seems to run all of the steps in the macro, and finish, but then Access hangs. Any help would be appreciated. Thanks, Mike in Sugar Land, TX On Mon, 30 Nov 2009 21:28:03 -0800, Switchboard newbie <Switchboard newbie@discussions.microsoft.com> wrote: My crystal ball is broken. Can you post some code, stripped to the bare essentials? -Tom. ...

Universal Macros?
I have a file that I export to excel and I need to constantly reformat the cells etc. I recorded a Macro for the formatting but everytime I export it again the macro isnt there. How to I record a macro and make it available to any document in excel? Thanks Put the macro in PERSONAL.XLS. This hidden file gets opened every time you start Excel. Bernard "Andrew" <andrew@fredlewis.com> wrote in message news:OVDuxIxlDHA.2456@TK2MSFTNGP09.phx.gbl... > I have a file that I export to excel and I need to constantly reformat the > cells etc. I recorded a Macro for the formatti...

Different values for error bars in series
In Excel 2007, how do you put in different error bar values for each data point in a series? So far I am only able to put in one value for all in my bar chart. Hi, See Jon's blog on the subject. http://peltiertech.com/WordPress/error-bars-in-excel-2007/#comments Cheers Andy On 06/04/2010 15:52, wdwind1 wrote: > In Excel 2007, how do you put in different error bar values for each data > point in a series? So far I am only able to put in one value for all in my > bar chart. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Macro Warning, But No Macros!
Running Excel 2003 SP1 on a Windows 2000 Server machine. I have an old spreadsheet that when I open it, I get the macro warning: "Macros are disabled because the security level is set to High and a digitally signed Trusted Certificate is not attached to the macros. To run the macros, change the security level to a lower setting (not recommended), or request the macros be signed by the author using a certificate issued by a Certificate Authority." When I go to look for any macros in the workbook (ALT-F8), the list is blank. Where else could there be a macro that would trigger this...

Macros
I have Win Me Outlook 2000 How do I write a simple macro? (In Word, there is a little icon down at the bottom of the window. Double click it and the macro recorder starts. But I cannot find an equivalent in Outlook.) You say it correctrly; write a simple macro. Outlook doesn't support recording macro's. You'll need to write VBA code in the Visual Basic Editor -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tips of the month: -Setting Permissions on a Mailbox -Create an Office XP CD sl...

A Different CRM for GP
Hello, We are pondering the idea of implementing MS Dynamics CRM. However, I am curious whether anyone else has had success in integrating a different CRM product with GP 10.0. It's not that we don't like the MS product; I just like to ensure I have covered all bases. Our partner does not have any other recommendations as they have not worked with any other CRM applications. Any information would be greatly apprecaited. Thank you, -- Jessie GoldMine is a good product, very mature, and easier to maintain table structures. There are links to GP but I cannot remember the fir...

Macro Function
How do I create a new function in Excel? -- Tomas Hi Thomas, You can have a look at this link: http://www.exceltip.com/st/Writing_Your_First_VBA_Function_in_Excel/631.html Regards, Bondi THANKS!!!! Very good tip -- Tomas "Bondi" skrev: > Hi Thomas, > > You can have a look at this link: > > http://www.exceltip.com/st/Writing_Your_First_VBA_Function_in_Excel/631.html > > Regards, > Bondi > > ...

2 users access calendar online with different rights
hi user A wants to give user B permission to see, add and edit entries in calendar online http://www.exchangedomain.ch/usersname/kalender/ user B is able to see, but not to add or edit entries at the moment in outlook itself its working. user B can open the folder calendar of user A and make entries and change entries what have to be done that this is possible also with web access exchange ? thankx mike schwarz On Thu, 26 Oct 2006 14:09:29 +0200, "Mike Schwarz" <ctek@ctek.ch> wrote: >hi > >user A wants to give user B permission to see, add and edit entries in &...

Same column, different cell width at different row
I have tried inserting a break and split, didn't work. I just want to separate the top half of a page with the bottom half so I can apply different cell width on the same column. Or how would I be able to do this? Same column but different cell width. Thanks! Glenn You can't do that. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Glenn Chung" <GlennChung@discussions.microsoft.com> wrote in message news:20746997-9D99-4684-A352-120370D27036@microsoft.com... >I have tried inserting a break and split, di...

Macro to coppy cells to certain rows depending on value in cell
I want his macro to after it have inserted the colmns and added the formula to 1. copy range A1 to E1 to every row where the word "Header" is in colmn F. 2. Then copy paste the whole sheet as values. 3. Then the range now standing left of "header" must be copied to the empy cells beneath each heading. For example a b c d e f 1)12/12/2005 F001 SAO3 1 CCE Header 2) Detail ...

Help with a conditional macro
I have seen something like this here before but can't seem to searc correctly for it. Say I have a city name in cell A4. Then I input city into cell B4. If there entries match I would like nothing t happen. however if a different city is entered, I would like to run macro. I'm sure that I have seen a similar question asked here but ca not remember when. Any help would be appreciated. Jerry Egglesto -- Message posted from http://www.ExcelForum.com Hi Jerry, This may get you started. Private Sub Worksheet_Change(ByVal Target As Range) If Range("A4").Value = Ran...

Fixed Assets
When using the Fixed Asset module, I am finding some small differences when compared to the Depreciation calculated by the External Accountants for tax purposes. Client would like to match exactly to the amounts calculated by external accounts. What is the best way to fix this issue? thanks, -- Patti Need more info Patti. What are the depreciation methods/settings that are creating the differences? Does this occur on every asset or just some? Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com get your gptip42today at www.gp2themax.blogs...

modify macro
Hi everyone I'm VBA dumb so please bear with me. I have a macro that inserts a row at the selection and then I want it to copy the cell below the selection and paste into the new row's cell. Here's what's in the macro now Selection.EntireRow.Inser Range("D23").Selec Selection.AutoFill Destination:=Range("D22:D23"), Type:=xlFillCop Range("D22:D23").Selec When I run the macro, a row is inserted above the cursor's location (no matter where it's placed) but the AutoFill is always to D23. My question is how do I get the cod...

Dates Difference
Hi, I need to get the difference between 2 dates (in number of days), how do i get that in vc++? I have used the DateDiff function in asp but couldn't find anything like that. Also, how can i convert string to a date? thanks in advance karan >I need to get the difference between 2 dates (in number of days), how >do i get that in vc++? Karan, Convert (if the dates aren't already in this format) the dates/times to FILETIME and subtract the values. >Also, how can i convert string to a date? Try COleDateTime::ParseDateTime or VarDateFromStr. Dave -- MVP VC++ FAQ: http://ww...

Sorting results Query
Hi I would like to know how to sort some results of a couple of tables. Help would be most apreciated as my skill level is growing fast but but it seems not fast enogh at the moment and Im stuck. I have two tables one called tblCategory and another called tblResults I am using the Query Wizard ( fantastic tool ) I need to create a query that will put the top 5 results ( Points ) from each Catagory in a list to display on a results form. Sounds easy but well it seems I can get a assending list of categories and have desending on points but I cant seem to make it only show the top 5 points ...

macro to convert multiple worksheets into one
Is there a macro that I can take a work book that has 8 seperate worksheets of data, and put all the data on one worksheet?. I need to sort all the data on one sheet. Currently I cut and paste the data to the one sheet. Any help would be appreciated. Thanks, AP Ron de Bruin has lots of sample code at: http://www.rondebruin.nl/copy2.htm "a.pontes" wrote: > > Is there a macro that I can take a work book that has 8 seperate worksheets > of data, and put all the data on one worksheet?. I need to sort all the data > on one sheet. Currently I cut and paste the data to...

Want to display value and difference on one chart
I am trying to create a chart that displays x and 10-x on the same chart. I want the chart to show a meaured value and the difference of that value to 10 in a different color. On Wed, 6 Aug 2008, in microsoft.public.excel.charting, JMH <JMH@discussions.microsoft.com> said: >I am trying to create a chart that displays x and 10-x on the same chart. I >want the chart to show a meaured value and the difference of that value to 10 >in a different color. Use your spreadsheet to calculate the values, then use the chart to graph them. Never try to use a chart for calculation. ...

Sending from 2 different computers
I have Outlook 2003 on my desktop at home and 2002 on my laptop while traveling. My server is Comcast. I would strongly prefer to use Outlook (rather than Comcast's webmail) on both computers. I have configured Outlook on both computers to receive email. I can also leave a copy on the server so it can be downloaded again later on the other computer, assuring that I always have copies of all emails no matter which computer I'm using. Problem is I can't send from my laptop. I've tried Comcast's instructions for configuring Outlook 2002 for use while traveling, but it doesn&...

Macros in Excel XP
Hi, I have recently started using office xp and have some macros stored in the personal.xls file. They used to open automatically in '97 but now I have to open the file every time I start Excel. The files are in xlstart folder so what's different? Any ideas? regards Phil Phil In XP the xlStart folder moved from C:\Program Files\Microsoft Office to C:\Application Data\Microsoft\Excel. I suspect this is your problem. (It actually moved in XL2000 I think) -- HTH Nick Hodge Southampton, England nick_hodge@btinternet.com "Phil Boffin" <philboffin@tiscali.co.uk>...