How can I use this VB script to reset my values on other sheets?

[trilogy]

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

[Die_Another_Day]

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