Broken: Set Cells in Range to 0 if blank

[ktoth04]

I can't seem to get this script working properly... Any help would be
appreciated, I am a complete beginner at VBA
Cell Rnage is N13:T27, If the cell is blank, on a save, it should be set to 0.

Sub Workbook_BeforeSave(rwNumber, clNumber)
For rwNumber = 13 To 27
For clNumber = 14 To 20
Application.ScreenUpdating = False
If IsEmpty(Worksheets("Sheet1").Cells(nRow, nCol)) Then
Cells(rwNumber, clNumber).Value = 0
Else: End If
Next clNumber
Next rwNumber
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein \(MVP - VB\)]

Where did you get that Work_BeforeSave procedure header from? I ask, because
its argument list is not correct. Are you aware that the VBA environment
will provide event headers for you? Go into the VBA editor and double click
the ThisWorkbook entry in the Project Explorer window in the upper left part
of the screen. Doing this will open a code window for the workbook (as
opposed to one of the individual worksheets). Next, click the left-hand
combo box at the top of the code window that appeared and select Workbook
from the drop down list. After you do this, all the possible workbook events
will be available for selection from the right-hand combo box. Select the
BeforeSave entry from this combo box. Place your active code inside this
automatically generated event procedure and see if that works for you.

Rick

 

[ward376]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Range("n13:t27"). _
Replace What:="", _
Replacement:="0"
End Sub

Cliff Edwards

 

[ktoth04]

I copy and pasted it from some website somewhere >.>
Thanks! I'll try that and see if it works

 

[Mike H]

Hi,

You before save event looks a bit dodgy to me, try this approach

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Set myrange = Range("N13:T27")
For Each C In myrange
If IsEmpty(C) Then
C.Value = 0
End If
Next
Application.ScreenUpdating = True
End Sub

Mike

 

[ktoth04]

I got a runtime error 9, subscript out of range on the If IsEmpty line...

 

[ktoth04]

Works like a charm, thanks!

Hi,

You before save event looks a bit dodgy to me, try this approach

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Set myrange = Range("N13:T27")
For Each C In myrange
If IsEmpty(C) Then
C.Value = 0
End If
Next
Application.ScreenUpdating = True
End Sub

Mike

 

[ktoth04]

Haha, my sheet wasn't named Sheet1... nevermind...