Z
Zaf
I have recently taken over an excel spreadsheet which has numerous macro, the
problem i am having is that i am not that familiar with VBA. there is a macro
in form of a command button that will dalete the first record and ther is
another command button that allocates a specfic points to a new record and
also slots the new entry in the spreadsheet according how many points have
been accumalted according to the information you have just entered and lastly
there is a re-order button which will re-order the records according to the
points with the highest being first. The re-order button seems to work fine
but when you try to put a new entry in or delete the first record you get
run-time error '1004': 'Waiting Lists2.xls' could not be found. check
spelling of the file name and varify that the file location is correct. When
i go to debug it always goes to the Application.Run " 'Waiting Lists2.xls' !
REORDER" in the command button for the delete option or the new entry
depending on what i am trying to do
I have tried making sure all the file names are the same across all the
macros, but do not know where to varfiy the location of the file if that is
the problem
Please any help will be much appreciated
BELOW IS WHAT YOU SEE WHEN YOU GO TO TOOLS AND THEN MACROS
AND EDIT
Sub REORDER()
'
' REORDER Macro
' Macro recorded 07/12/2004 by Kinrade
'
'
Rows("5:200").Select
Selection.Sort Key1:=Range("B5"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A5").Select
ActiveCell.FormulaR1C1 = "1st"
Range("A6").Select
ActiveCell.FormulaR1C1 = "2nd"
Range("A5:A6").Select
Selection.AutoFill Destination:=Range("A5:A200"), Type:=xlFillDefault
Range("A5:A200").Select
Range("B4").Select
ActiveCell.FormulaR1C1 = "=RC[21]+RC[22]+RC[23]+RC[24]"
Range("C4").Select
End Sub
Sub ENTERNEW()
'
' ENTERNEW Macro
' Macro recorded 07/12/2004 by Kinrade
'
'
Rows("4:4").Select
Selection.Copy
Rows("200:200").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Application.Run "'Waiting Lists2.xls'!REORDER"
Range("C4:U4").Select
Selection.ClearContents
Range("B4").Select
ActiveCell.FormulaR1C1 = "=RC[21]+RC[22]+RC[23]+RC[24]"
Range("C4").Select
End Sub
Sub DELETE1ST()
'
' DELETE1ST Macro
' Macro recorded 07/12/2004 by Kinrade
'
'
Rows("5:5").Select
Selection.ClearContents
Application.Run "'Waiting Lists2.xls'!REORDER"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=RC[21]+RC[22]+RC[23]+RC[24]"
Range("C4").Select
End Sub
Sub BUTTONS()
'
' BUTTONS Macro
' Macro recorded 07/12/2004 by Kinrade
'
'
Range("H4").Select
ActiveCell.FormulaR1C1 = "High"
Range("C4").Select
End Sub
Sub MWGFORMULA()
'
' MWGFORMULA Macro
' Macro recorded 07/12/2004 by Kinrade
'
'
Range("B4").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[15],RC[16],RC[17],RC[18],RC[19],RC[20],RC[21],RC[22],RC[23],RC[24])"
Range("I4").Select
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-1],TODAY())/360"
Range("C4").Select
End Sub
Sub Copy()
'
' Copy Macro
' Macro recorded 10/05/2007 by Administrator
'
'
Selection.Copy
Sheets("Council DFG (Stage2)").Select
Range("B200").Select
ActiveSheet.Paste
Application.Run "'Waiting Lists2.xls'!REORDER"
End Sub
BELOW IS WHAT YOU SEE WHEN YOU GO TO THE PARTICULAR SHEET IN THE WORKBOOK
THAT I AM WORKING ON BY GOING TO MICROSOFT EXCEL OBJECTS AND SELECTING SHEET
1 AND THE CHANGING GENERAL TO THE COMMAND BUTTONS 1, 2, 3
Private Sub CommandButton1_Click()
Rows("4:4").Select
Selection.Copy
Rows("200:200").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Application.Run "'Waiting Lists2.xls'!REORDER"
Range("C4:H4").Select
Selection.ClearContents
Range("J4:N4").Select
Selection.ClearContents
Range("B4").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[15],RC[16],RC[17],RC[18],RC[19],RC[20],RC[21],RC[22],RC[23],RC[24])"
Range("I4").Select
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-1],TODAY())/360"
Range("C4").Select
End Sub
Private Sub CommandButton2_Click()
Rows("5:200").Select
Selection.Sort Key1:=Range("B5"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A5").Select
ActiveCell.FormulaR1C1 = "1st"
Range("A6").Select
ActiveCell.FormulaR1C1 = "2nd"
Range("A5:A6").Select
Selection.AutoFill Destination:=Range("A5:A200"), Type:=xlFillDefault
Range("A5:A200").Select
Range("B4").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[15],RC[16],RC[17],RC[18],RC[19],RC[20],RC[21],RC[22],RC[23],RC[24])"
Range("I4").Select
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-1],TODAY())/360"
Range("C4").Select
End Sub
Private Sub CommandButton3_Click()
Rows("5:5").Select
Selection.ClearContents
Application.Run "'Waiting Lists2.xls'!REORDER"
Range("B4").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[15],RC[16],RC[17],RC[18],RC[19],RC[20],RC[21],RC[22],RC[23],RC[24])"
Range("I4").Select
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-1],TODAY())/360"
Range("C4").Select
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
PLEASE HELP
problem i am having is that i am not that familiar with VBA. there is a macro
in form of a command button that will dalete the first record and ther is
another command button that allocates a specfic points to a new record and
also slots the new entry in the spreadsheet according how many points have
been accumalted according to the information you have just entered and lastly
there is a re-order button which will re-order the records according to the
points with the highest being first. The re-order button seems to work fine
but when you try to put a new entry in or delete the first record you get
run-time error '1004': 'Waiting Lists2.xls' could not be found. check
spelling of the file name and varify that the file location is correct. When
i go to debug it always goes to the Application.Run " 'Waiting Lists2.xls' !
REORDER" in the command button for the delete option or the new entry
depending on what i am trying to do
I have tried making sure all the file names are the same across all the
macros, but do not know where to varfiy the location of the file if that is
the problem
Please any help will be much appreciated
BELOW IS WHAT YOU SEE WHEN YOU GO TO TOOLS AND THEN MACROS
AND EDIT
Sub REORDER()
'
' REORDER Macro
' Macro recorded 07/12/2004 by Kinrade
'
'
Rows("5:200").Select
Selection.Sort Key1:=Range("B5"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A5").Select
ActiveCell.FormulaR1C1 = "1st"
Range("A6").Select
ActiveCell.FormulaR1C1 = "2nd"
Range("A5:A6").Select
Selection.AutoFill Destination:=Range("A5:A200"), Type:=xlFillDefault
Range("A5:A200").Select
Range("B4").Select
ActiveCell.FormulaR1C1 = "=RC[21]+RC[22]+RC[23]+RC[24]"
Range("C4").Select
End Sub
Sub ENTERNEW()
'
' ENTERNEW Macro
' Macro recorded 07/12/2004 by Kinrade
'
'
Rows("4:4").Select
Selection.Copy
Rows("200:200").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Application.Run "'Waiting Lists2.xls'!REORDER"
Range("C4:U4").Select
Selection.ClearContents
Range("B4").Select
ActiveCell.FormulaR1C1 = "=RC[21]+RC[22]+RC[23]+RC[24]"
Range("C4").Select
End Sub
Sub DELETE1ST()
'
' DELETE1ST Macro
' Macro recorded 07/12/2004 by Kinrade
'
'
Rows("5:5").Select
Selection.ClearContents
Application.Run "'Waiting Lists2.xls'!REORDER"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=RC[21]+RC[22]+RC[23]+RC[24]"
Range("C4").Select
End Sub
Sub BUTTONS()
'
' BUTTONS Macro
' Macro recorded 07/12/2004 by Kinrade
'
'
Range("H4").Select
ActiveCell.FormulaR1C1 = "High"
Range("C4").Select
End Sub
Sub MWGFORMULA()
'
' MWGFORMULA Macro
' Macro recorded 07/12/2004 by Kinrade
'
'
Range("B4").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[15],RC[16],RC[17],RC[18],RC[19],RC[20],RC[21],RC[22],RC[23],RC[24])"
Range("I4").Select
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-1],TODAY())/360"
Range("C4").Select
End Sub
Sub Copy()
'
' Copy Macro
' Macro recorded 10/05/2007 by Administrator
'
'
Selection.Copy
Sheets("Council DFG (Stage2)").Select
Range("B200").Select
ActiveSheet.Paste
Application.Run "'Waiting Lists2.xls'!REORDER"
End Sub
BELOW IS WHAT YOU SEE WHEN YOU GO TO THE PARTICULAR SHEET IN THE WORKBOOK
THAT I AM WORKING ON BY GOING TO MICROSOFT EXCEL OBJECTS AND SELECTING SHEET
1 AND THE CHANGING GENERAL TO THE COMMAND BUTTONS 1, 2, 3
Private Sub CommandButton1_Click()
Rows("4:4").Select
Selection.Copy
Rows("200:200").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Application.Run "'Waiting Lists2.xls'!REORDER"
Range("C4:H4").Select
Selection.ClearContents
Range("J4:N4").Select
Selection.ClearContents
Range("B4").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[15],RC[16],RC[17],RC[18],RC[19],RC[20],RC[21],RC[22],RC[23],RC[24])"
Range("I4").Select
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-1],TODAY())/360"
Range("C4").Select
End Sub
Private Sub CommandButton2_Click()
Rows("5:200").Select
Selection.Sort Key1:=Range("B5"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A5").Select
ActiveCell.FormulaR1C1 = "1st"
Range("A6").Select
ActiveCell.FormulaR1C1 = "2nd"
Range("A5:A6").Select
Selection.AutoFill Destination:=Range("A5:A200"), Type:=xlFillDefault
Range("A5:A200").Select
Range("B4").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[15],RC[16],RC[17],RC[18],RC[19],RC[20],RC[21],RC[22],RC[23],RC[24])"
Range("I4").Select
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-1],TODAY())/360"
Range("C4").Select
End Sub
Private Sub CommandButton3_Click()
Rows("5:5").Select
Selection.ClearContents
Application.Run "'Waiting Lists2.xls'!REORDER"
Range("B4").Select
ActiveCell.FormulaR1C1 = _
"=SUM(RC[15],RC[16],RC[17],RC[18],RC[19],RC[20],RC[21],RC[22],RC[23],RC[24])"
Range("I4").Select
ActiveCell.FormulaR1C1 = "=DAYS360(RC[-1],TODAY())/360"
Range("C4").Select
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
PLEASE HELP