| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Range naming cells with blank cells through coding | Excel Discussion (Misc queries) | |||
| If certain cells not blank, and cells in range are, set values to | Excel Discussion (Misc queries) | |||
| Maximum Number of Blank Cells between Non Blank Cells in a Range | Excel Worksheet Functions | |||
| Determine if range has NO Blank Cells without looping through each cell in range | Excel Programming | |||
| How do I skip blank cells when copying over a range of cells? | Excel Discussion (Misc queries) | |||
Linear Mode
