Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I use this VB script to reset my values on other sheets?
Hello I have this VB script that resets all my values on the sheet that m Reset Button resides (shown below) However, this spreadsheet has 3 different sheets, and I would like t be able to reset the variable values on the other sheets as well wit the one button. I am using the script below. Any help on how to make it reset th others sheets as well would be very much appreciated. Thanking you in advance Trilogy Dim CellArray Dim ValueArray Dim ErrorCheck CellArray = Array("B1:C1", "B2:C2",..... etc) ValueArray = Array("", "",... etc) ErrorCheck = False If UBound(CellArray) < UBound(ValueArray) Then MessageString = "Data Mismatch in Cell Arrays - Please ensur there is a matching value for each cell listed in 'CellArray'" & vbCrL & "There are currently " & UBound(CellArray) & " CELL assignments and & UBound(ValueArray) & " VALUE assignments" MsgBox (MessageString) ErrorCheck = True End If If Not ErrorCheck Then For i = o To UBound(CellArray) Range(CellArray(i)).Select ActiveCell.FormulaR1C1 = ValueArray(i) If InStr(CellArray(i), ":") 0 Then CellRange = Split(CellArray(i), ":") StartRow = Right(CellRange(0), Len(CellRange(0)) - 1) EndRow = Right(CellRange(1), Len(CellRange(1)) - 1) If StartRow < EndRow Then Selection.FillDown End If End If Next End If Range("B1:C1").Select End Su -- trilog ----------------------------------------------------------------------- trilogy's Profile: http://www.excelforum.com/member.php...fo&userid=3683 View this thread: http://www.excelforum.com/showthread.php?threadid=56820 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I use this VB script to reset my values on other sheets?
Trilogy, I cleaned the sub up a little. I believe it still does as was
intended, just must faster. I add the ability to clear multiple sheets. Let me know if it does not work. Sub ClearData() 'Modified 8/3/2006 By Charles Chickering Dim CellArray 'Dim ValueArray 'Not Needed Dim Sh As Worksheet CellArray = Array("B1:C1", "B2:C2") 'ValueArray = Array("", "") 'Not Needed ErrorCheck = False If UBound(CellArray) < UBound(ValueArray) Then MessageString = "Data Mismatch in Cell Arrays - Please ensure " & _ "there is a matching value for each cell listed in 'CellArray'" & vbCrLf _ & "There are currently " & UBound(CellArray) & " CELL assignments and " _ & UBound(ValueArray) & " VALUE assignments" MsgBox (MessageString) Exit Sub End If For i = 0 To UBound(CellArray) For Sh = Sheets(1) To Sheets(3) Sh.Range(CellArray(i)).ClearContents Range("B1:C1").Select Next Next End Sub Charles Chickering xl Geek trilogy wrote: Hello I have this VB script that resets all my values on the sheet that my Reset Button resides (shown below) However, this spreadsheet has 3 different sheets, and I would like to be able to reset the variable values on the other sheets as well with the one button. I am using the script below. Any help on how to make it reset the others sheets as well would be very much appreciated. Thanking you in advance Trilogy Dim CellArray Dim ValueArray Dim ErrorCheck CellArray = Array("B1:C1", "B2:C2",..... etc) ValueArray = Array("", "",... etc) ErrorCheck = False If UBound(CellArray) < UBound(ValueArray) Then MessageString = "Data Mismatch in Cell Arrays - Please ensure there is a matching value for each cell listed in 'CellArray'" & vbCrLf & "There are currently " & UBound(CellArray) & " CELL assignments and " & UBound(ValueArray) & " VALUE assignments" MsgBox (MessageString) ErrorCheck = True End If If Not ErrorCheck Then For i = o To UBound(CellArray) Range(CellArray(i)).Select ActiveCell.FormulaR1C1 = ValueArray(i) If InStr(CellArray(i), ":") 0 Then CellRange = Split(CellArray(i), ":") StartRow = Right(CellRange(0), Len(CellRange(0)) - 1) EndRow = Right(CellRange(1), Len(CellRange(1)) - 1) If StartRow < EndRow Then Selection.FillDown End If End If Next End If Range("B1:C1").Select End Sub -- trilogy ------------------------------------------------------------------------ trilogy's Profile: http://www.excelforum.com/member.php...o&userid=36833 View this thread: http://www.excelforum.com/showthread...hreadid=568201 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i reset excel to its default values? | Excel Worksheet Functions | |||
How do I reset options to default values | Excel Discussion (Misc queries) | |||
set or reset the print range for several sheets at a time? | Excel Discussion (Misc queries) | |||
how do you set or reset the print range for several sheets at a ti | Excel Programming | |||
Using For Next to reset Pge Brk Preview on all sheets | Excel Programming |