| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro disables "undo" feature..help please
I have a spreadsheet that I introduced a macro to eliminate duplications
with ranking formulae in the sheet. All works ok until after I input data to the sheet, then run (update button) the macro. The macro does the job it was intended for but now the "undo" function in my Excel sheet is disabled. Without the macro the "undo" function works OK. Has anyone had similar problems and curred them without removing the macro. Dim myArray(500, 2) As String Dim myIndex As Long Sub getNames() Dim myArray(500, 2) As String Dim myIndex As Long Dim myRow As Long Dim lastRow As Long lastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row myRow = 1 myIndex = 1 For i = 4 To lastRow myArray(myIndex, 1) = Sheets(1).Cells(i, 1) myArray(myIndex, 2) = Sheets(1).Cells(i, 16) myIndex = myIndex + 1 Next 'MsgBox myArray(myIndex, 1).Rank myRow = myIndex - 1 myIndex = 1 For i = 4 To 13 For j = 1 To myRow If CStr(myArray(j, 2)) = CStr(Sheets(2).Cells(i, 4)) Then Sheets(2).Cells(i, 3) = myArray(j, 1) myArray(j, 1) = "" myArray(j, 2) = "" Exit For End If myIndex = myIndex + 1 Next myIndex = 1 Next End Sub TIA Terry |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro disables "undo" feature..help please
Hi Terry,
Macros always disable undos from before the macro was run. To get around that, you'd have program your own undo. Not an easy task, but it can be done. Consider saving your workbook before the macro is run (or even in the macro itself) -- Kind regards, Niek Otten Microsoft MVP - Excel "Terry" wrote in message ... |I have a spreadsheet that I introduced a macro to eliminate duplications | with ranking formulae in the sheet. | | All works ok until after I input data to the sheet, then run (update | button) the macro. | The macro does the job it was intended for but now the "undo" function in my | Excel sheet is disabled. | | Without the macro the "undo" function works OK. | | Has anyone had similar problems and curred them without removing the macro. | | Dim myArray(500, 2) As String | Dim myIndex As Long | | Sub getNames() | Dim myArray(500, 2) As String | Dim myIndex As Long | Dim myRow As Long | Dim lastRow As Long | | lastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row | myRow = 1 | myIndex = 1 | | For i = 4 To lastRow | myArray(myIndex, 1) = Sheets(1).Cells(i, 1) | myArray(myIndex, 2) = Sheets(1).Cells(i, 16) | myIndex = myIndex + 1 | Next | 'MsgBox myArray(myIndex, 1).Rank | myRow = myIndex - 1 | myIndex = 1 | For i = 4 To 13 | For j = 1 To myRow | If CStr(myArray(j, 2)) = CStr(Sheets(2).Cells(i, 4)) Then | Sheets(2).Cells(i, 3) = myArray(j, 1) | myArray(j, 1) = "" | myArray(j, 2) = "" | Exit For | End If | myIndex = myIndex + 1 | Next | myIndex = 1 | Next | | End Sub | | TIA | Terry | | |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro disables "undo" feature..help please
Niek Otten
Thanks for that.....at least I know it is a known fact now. I do not wish to have another programe for own undo function. Regards Terry "Niek Otten" wrote in message ... Hi Terry, Macros always disable undos from before the macro was run. To get around that, you'd have program your own undo. Not an easy task, but it can be done. Consider saving your workbook before the macro is run (or even in the macro itself) -- Kind regards, Niek Otten Microsoft MVP - Excel "Terry" wrote in message ... |I have a spreadsheet that I introduced a macro to eliminate duplications | with ranking formulae in the sheet. | | All works ok until after I input data to the sheet, then run (update | button) the macro. | The macro does the job it was intended for but now the "undo" function in my | Excel sheet is disabled. | | Without the macro the "undo" function works OK. | | Has anyone had similar problems and curred them without removing the macro. | | Dim myArray(500, 2) As String | Dim myIndex As Long | | Sub getNames() | Dim myArray(500, 2) As String | Dim myIndex As Long | Dim myRow As Long | Dim lastRow As Long | | lastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row | myRow = 1 | myIndex = 1 | | For i = 4 To lastRow | myArray(myIndex, 1) = Sheets(1).Cells(i, 1) | myArray(myIndex, 2) = Sheets(1).Cells(i, 16) | myIndex = myIndex + 1 | Next | 'MsgBox myArray(myIndex, 1).Rank | myRow = myIndex - 1 | myIndex = 1 | For i = 4 To 13 | For j = 1 To myRow | If CStr(myArray(j, 2)) = CStr(Sheets(2).Cells(i, 4)) Then | Sheets(2).Cells(i, 3) = myArray(j, 1) | myArray(j, 1) = "" | myArray(j, 2) = "" | Exit For | End If | myIndex = myIndex + 1 | Next | myIndex = 1 | Next | | End Sub | | TIA | Terry | | |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro disables "undo" feature..help please
Hi
More correctly, any VBA code always resets undo counter. When after running the macro you do some changes manually, you can use undo again - back to moment when the macro was run. You never can use undo to restore your workbook in state, it had before you run some VBA procedure - the only option is to close the file without saving and reopening it then. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Terry" wrote in message ... I have a spreadsheet that I introduced a macro to eliminate duplications with ranking formulae in the sheet. All works ok until after I input data to the sheet, then run (update button) the macro. The macro does the job it was intended for but now the "undo" function in my Excel sheet is disabled. Without the macro the "undo" function works OK. Has anyone had similar problems and curred them without removing the macro. Dim myArray(500, 2) As String Dim myIndex As Long Sub getNames() Dim myArray(500, 2) As String Dim myIndex As Long Dim myRow As Long Dim lastRow As Long lastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row myRow = 1 myIndex = 1 For i = 4 To lastRow myArray(myIndex, 1) = Sheets(1).Cells(i, 1) myArray(myIndex, 2) = Sheets(1).Cells(i, 16) myIndex = myIndex + 1 Next 'MsgBox myArray(myIndex, 1).Rank myRow = myIndex - 1 myIndex = 1 For i = 4 To 13 For j = 1 To myRow If CStr(myArray(j, 2)) = CStr(Sheets(2).Cells(i, 4)) Then Sheets(2).Cells(i, 3) = myArray(j, 1) myArray(j, 1) = "" myArray(j, 2) = "" Exit For End If myIndex = myIndex + 1 Next myIndex = 1 Next End Sub TIA Terry |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro disables "undo" feature..help please
Arvi..Thank you.
Terry "Arvi Laanemets" wrote in message ... Hi More correctly, any VBA code always resets undo counter. When after running the macro you do some changes manually, you can use undo again - back to moment when the macro was run. You never can use undo to restore your workbook in state, it had before you run some VBA procedure - the only option is to close the file without saving and reopening it then. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Terry" wrote in message ... I have a spreadsheet that I introduced a macro to eliminate duplications with ranking formulae in the sheet. All works ok until after I input data to the sheet, then run (update button) the macro. The macro does the job it was intended for but now the "undo" function in my Excel sheet is disabled. Without the macro the "undo" function works OK. Has anyone had similar problems and curred them without removing the macro. Dim myArray(500, 2) As String Dim myIndex As Long Sub getNames() Dim myArray(500, 2) As String Dim myIndex As Long Dim myRow As Long Dim lastRow As Long lastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row myRow = 1 myIndex = 1 For i = 4 To lastRow myArray(myIndex, 1) = Sheets(1).Cells(i, 1) myArray(myIndex, 2) = Sheets(1).Cells(i, 16) myIndex = myIndex + 1 Next 'MsgBox myArray(myIndex, 1).Rank myRow = myIndex - 1 myIndex = 1 For i = 4 To 13 For j = 1 To myRow If CStr(myArray(j, 2)) = CStr(Sheets(2).Cells(i, 4)) Then Sheets(2).Cells(i, 3) = myArray(j, 1) myArray(j, 1) = "" myArray(j, 2) = "" Exit For End If myIndex = myIndex + 1 Next myIndex = 1 Next End Sub TIA Terry |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro disables "undo" feature..help please
John Walkenbach shows how:
http://j-walk.com/ss/excel/tips/tip23.htm Terry wrote: I have a spreadsheet that I introduced a macro to eliminate duplications with ranking formulae in the sheet. All works ok until after I input data to the sheet, then run (update button) the macro. The macro does the job it was intended for but now the "undo" function in my Excel sheet is disabled. Without the macro the "undo" function works OK. Has anyone had similar problems and curred them without removing the macro. Dim myArray(500, 2) As String Dim myIndex As Long Sub getNames() Dim myArray(500, 2) As String Dim myIndex As Long Dim myRow As Long Dim lastRow As Long lastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row myRow = 1 myIndex = 1 For i = 4 To lastRow myArray(myIndex, 1) = Sheets(1).Cells(i, 1) myArray(myIndex, 2) = Sheets(1).Cells(i, 16) myIndex = myIndex + 1 Next 'MsgBox myArray(myIndex, 1).Rank myRow = myIndex - 1 myIndex = 1 For i = 4 To 13 For j = 1 To myRow If CStr(myArray(j, 2)) = CStr(Sheets(2).Cells(i, 4)) Then Sheets(2).Cells(i, 3) = myArray(j, 1) myArray(j, 1) = "" myArray(j, 2) = "" Exit For End If myIndex = myIndex + 1 Next myIndex = 1 Next End Sub TIA Terry -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro disables "undo" feature..help please
Thank you Dave
Terry "Dave Peterson" wrote in message ... John Walkenbach shows how: http://j-walk.com/ss/excel/tips/tip23.htm Terry wrote: I have a spreadsheet that I introduced a macro to eliminate duplications with ranking formulae in the sheet. All works ok until after I input data to the sheet, then run (update button) the macro. The macro does the job it was intended for but now the "undo" function in my Excel sheet is disabled. Without the macro the "undo" function works OK. Has anyone had similar problems and curred them without removing the macro. Dim myArray(500, 2) As String Dim myIndex As Long Sub getNames() Dim myArray(500, 2) As String Dim myIndex As Long Dim myRow As Long Dim lastRow As Long lastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row myRow = 1 myIndex = 1 For i = 4 To lastRow myArray(myIndex, 1) = Sheets(1).Cells(i, 1) myArray(myIndex, 2) = Sheets(1).Cells(i, 16) myIndex = myIndex + 1 Next 'MsgBox myArray(myIndex, 1).Rank myRow = myIndex - 1 myIndex = 1 For i = 4 To 13 For j = 1 To myRow If CStr(myArray(j, 2)) = CStr(Sheets(2).Cells(i, 4)) Then Sheets(2).Cells(i, 3) = myArray(j, 1) myArray(j, 1) = "" myArray(j, 2) = "" Exit For End If myIndex = myIndex + 1 Next myIndex = 1 Next End Sub TIA Terry -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| One should be able to "jump" over undo command in Excel | Excel Discussion (Misc queries) | |||
| Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A" | Excel Discussion (Misc queries) | |||
| "Undo/Redo" Feature NOT functioning | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| With autorecover on in Excel you lose your "undo" functionality. | Excel Discussion (Misc queries) | |||
Linear Mode
