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

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