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

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" <(E-Mail Removed)> wrote in message
news:531A5B0A-5E37-4BE3-8686-(E-Mail Removed)...
>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

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

Cliff Edwards

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" <(E-Mail Removed)> wrote in message
> news:531A5B0A-5E37-4BE3-8686-(E-Mail Removed)...
> >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
>
>

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

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" <(E-Mail Removed)> wrote in message
> news:531A5B0A-5E37-4BE3-8686-(E-Mail Removed)...
> >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
>
>

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

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" <(E-Mail Removed)> wrote in message
> > news:531A5B0A-5E37-4BE3-8686-(E-Mail Removed)...
> > >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
> >
> >