Broken: Set Cells in Range to 0 if blank
 

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

Broken: Set Cells in Range to 0 if blank
 

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


"ktoth04" wrote in message
...
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

Broken: Set Cells in Range to 0 if blank
 

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

Cliff Edwards

Broken: Set Cells in Range to 0 if blank
 

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

"Rick Rothstein (MVP - VB)" wrote:

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


"ktoth04" wrote in message
...
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

Broken: Set Cells in Range to 0 if blank
 

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" wrote:

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

Broken: Set Cells in Range to 0 if blank
 

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

"Rick Rothstein (MVP - VB)" wrote:

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


"ktoth04" wrote in message
...
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

Broken: Set Cells in Range to 0 if blank
 

Works like a charm, thanks!

"Mike H" wrote:

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" wrote:

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

Broken: Set Cells in Range to 0 if blank
 

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

"ktoth04" wrote:

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

"Rick Rothstein (MVP - VB)" wrote:

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


"ktoth04" wrote in message
...
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