| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making sure that cells within a row in a database aren't blank
Good afternoon, weekend Excelers!
I currently have a database in cells B14:L19 I'm trying to prevent users from having blank rows in the database by using the Worksheet_Change event to check that if a change is made to any cells, that this doesn't result in all eleven cells in the row being blank. As you can see from the code below, I've tried lots of different ways to (for example if I'm in row 16) to check that B16:L16 aren't blank, but without much success. I get lots of "invalid qualifier" messages. Can anyone advise me of the best way to go about this? Thanks in advance Pete Rooney With Target '--------------------------------------------------------------------------------- If .Column = 2 Then If .Column <= 12 Then MsgBox (.Column & " " & .Row) If Application.WorksheetFunction.isblank(Cells(Target .Row, 2)) Then ' If "C" & Target.Row.Value = 0 Then ' If "D" & Target.Row.Value = 0 Then ' If "E" & Target.Row.Value = 0 Then ' If "F" & Target.Row.Value = 0 Then ' If "G" & Target.Row.Value = 0 Then ' If "H" & Target.Row.Value = 0 Then ' If "I" & Target.Row.Value = 0 Then ' If "J" & Target.Row.Value = 0 Then ' If "K" & Target.Row.Value = 0 Then ' If "L" & Target.Row.Value = 0 Then MsgBox ("You CAN'T have blank rows in the database!") ' Range("F" & Target.Row).Formula = "Blank Eliminator" ' End If ' End If ' End If ' End If ' End If ' End If ' End If ' End If ' End If ' End If End If End If End If '--------------------------------------------------------------------------------- If .Column = 11 Then DefineDatabase If .Row = FirstDBRow Then If .Row <= FinalDBRow Then '--------------------------------------------------------------------------- If .Formula = "Core Infrastructure" Then |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Making sure that cells within a row in a database aren't blank
Norman,
Thanks for this. A couple of issues - I need the range to be dynamic as the database will extend or reduce, depending on user action. However, I have a routine that determines the first and last cells of the range, so it's not too much of a problem. However, when I tried your code, I get the message even though there's something in one of the columns. I never have any luck with intersect! Any thoughts? Pete "Norman Jones" wrote: Hi Pete, Perhaps, something like this will do what you want: '=========================== Private Sub Worksheet_Change(ByVal Target As Range) Dim Rng As Range Dim rng2 As Range Dim rw As Range Set Rng = Range("A2:L20") '<<===== CHANGE Set rng2 = Intersect(Rng, Target) If Not rng2 Is Nothing Then For Each rw In rng2.Rows If Application.CountA(rw.Cells) = 0 Then MsgBox ("Database row " & rw.Row _ & " is empty. You CAN'T have " _ & "blank rows in the database!") End If Next End If End Sub '<<=========================== Change the range address to accord with the database range. --- Regards, Norman "Pete Rooney" <Pete wrote in message ... Good afternoon, weekend Excelers! I currently have a database in cells B14:L19 I'm trying to prevent users from having blank rows in the database by using the Worksheet_Change event to check that if a change is made to any cells, that this doesn't result in all eleven cells in the row being blank. As you can see from the code below, I've tried lots of different ways to (for example if I'm in row 16) to check that B16:L16 aren't blank, but without much success. I get lots of "invalid qualifier" messages. Can anyone advise me of the best way to go about this? Thanks in advance Pete Rooney With Target '--------------------------------------------------------------------------------- If .Column = 2 Then If .Column <= 12 Then MsgBox (.Column & " " & .Row) If Application.WorksheetFunction.isblank(Cells(Target .Row, 2)) Then ' If "C" & Target.Row.Value = 0 Then ' If "D" & Target.Row.Value = 0 Then ' If "E" & Target.Row.Value = 0 Then ' If "F" & Target.Row.Value = 0 Then ' If "G" & Target.Row.Value = 0 Then ' If "H" & Target.Row.Value = 0 Then ' If "I" & Target.Row.Value = 0 Then ' If "J" & Target.Row.Value = 0 Then ' If "K" & Target.Row.Value = 0 Then ' If "L" & Target.Row.Value = 0 Then MsgBox ("You CAN'T have blank rows in the database!") ' Range("F" & Target.Row).Formula = "Blank Eliminator" ' End If ' End If ' End If ' End If ' End If ' End If ' End If ' End If ' End If ' End If End If End If End If '--------------------------------------------------------------------------------- If .Column = 11 Then DefineDatabase If .Row = FirstDBRow Then If .Row <= FinalDBRow Then '--------------------------------------------------------------------------- If .Formula = "Core Infrastructure" Then |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Making sure that cells within a row in a database aren't blank
Hi Pete,
A couple of issues - I need the range to be dynamic as the database will extend or reduce, depending on user action. However, I have a routine that determines the first and last cells of the range, so it's not too much of a problem. Fine. As you intimate, instead of defining a static range, establish and define the boundaries. Alternatively, perhaps you can use a dynamic range (nammed, say "MyDatabase") and set the rng variable to MyDatabase. If you are not familiar with dynamic ranges, see debra Dalgleish at: http://www.contextures.com/xlNames01.html#Dynamic However, when I tried your code, I get the message even though there's something in one of the columns. I never have any luck with intersect! My fault! Try changing: Set rng2 = Intersect(Rng, Target) to Set rng2 = Intersect(Rng, Target.EntireRow) --- Regards, Norman "Pete Rooney" wrote in message ... Norman, Thanks for this. A couple of issues - I need the range to be dynamic as the database will extend or reduce, depending on user action. However, I have a routine that determines the first and last cells of the range, so it's not too much of a problem. However, when I tried your code, I get the message even though there's something in one of the columns. I never have any luck with intersect! Any thoughts? Pete "Norman Jones" wrote: Hi Pete, Perhaps, something like this will do what you want: '=========================== Private Sub Worksheet_Change(ByVal Target As Range) Dim Rng As Range Dim rng2 As Range Dim rw As Range Set Rng = Range("A2:L20") '<<===== CHANGE Set rng2 = Intersect(Rng, Target) If Not rng2 Is Nothing Then For Each rw In rng2.Rows If Application.CountA(rw.Cells) = 0 Then MsgBox ("Database row " & rw.Row _ & " is empty. You CAN'T have " _ & "blank rows in the database!") End If Next End If End Sub '<<=========================== Change the range address to accord with the database range. --- Regards, Norman "Pete Rooney" <Pete wrote in message ... Good afternoon, weekend Excelers! I currently have a database in cells B14:L19 I'm trying to prevent users from having blank rows in the database by using the Worksheet_Change event to check that if a change is made to any cells, that this doesn't result in all eleven cells in the row being blank. As you can see from the code below, I've tried lots of different ways to (for example if I'm in row 16) to check that B16:L16 aren't blank, but without much success. I get lots of "invalid qualifier" messages. Can anyone advise me of the best way to go about this? Thanks in advance Pete Rooney With Target '--------------------------------------------------------------------------------- If .Column = 2 Then If .Column <= 12 Then MsgBox (.Column & " " & .Row) If Application.WorksheetFunction.isblank(Cells(Target .Row, 2)) Then ' If "C" & Target.Row.Value = 0 Then ' If "D" & Target.Row.Value = 0 Then ' If "E" & Target.Row.Value = 0 Then ' If "F" & Target.Row.Value = 0 Then ' If "G" & Target.Row.Value = 0 Then ' If "H" & Target.Row.Value = 0 Then ' If "I" & Target.Row.Value = 0 Then ' If "J" & Target.Row.Value = 0 Then ' If "K" & Target.Row.Value = 0 Then ' If "L" & Target.Row.Value = 0 Then MsgBox ("You CAN'T have blank rows in the database!") ' Range("F" & Target.Row).Formula = "Blank Eliminator" ' End If ' End If ' End If ' End If ' End If ' End If ' End If ' End If ' End If ' End If End If End If End If '--------------------------------------------------------------------------------- If .Column = 11 Then DefineDatabase If .Row = FirstDBRow Then If .Row <= FinalDBRow Then '--------------------------------------------------------------------------- If .Formula = "Core Infrastructure" Then |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Making Blank Cells | Excel Worksheet Functions | |||
| Making a Database | Excel Discussion (Misc queries) | |||
| Blank cells in named range- how to ignore them when making my graph? Help plz! | Excel Discussion (Misc queries) | |||
| Making Blank Cells Really Blank (Zen Koan) | Excel Worksheet Functions | |||
| making graphs ignore "blank " cells | Excel Discussion (Misc queries) | |||
![Pete Rooney[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
