force text in a cell based on value of cell in a range

  • Thread starter Thread starter jjst34
  • Start date Start date
J

jjst34

Hello all.. new to this board, thx in advance for any help.. Here is
what I'm trying to do. I'd like a piece of code that says the
following. If any cell in a specific range is < 0 and the cell 7
columns before it, same row (the title of this cell is "description",
is blank, i'd like a message box to come up telling the user they need
to enter a description in that cell. Here is what I have so far.. I am
sorta new to this VBA stuff, so if I'm way off, sorry...

Private Sub worksheet_change(ByVal Target As Excel.Range)

Dim result As Range
For Each result In Range("FY04_reduction_totals")
If result < 0 & result.Offset(0, 7) = "" Then
MsgBox ("please enter a description")
End If
End Sub
 
Something like this should do the trick:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rngValidCells As Range
Dim Rng As Range
On Error Resume Next
Set rngValidCells = Intersect(Target, "FY04_reduction_totals")
If Not rngValidCells Is Nothing Then
For Each Rng In rngValidCells
If Rng.Value < 0 And Range.Offset(0, -7).Value = "" Then
MsgBox "Please enter a description"
End If
Next Rng
End If
End Sub

If the range "FY04_reduction_totals" is a column, it would be more robust to give a name to the column containing the descriptions, such as "FY04_descriptions" and replace

Range.Offset(0, -7).Value

with

Intersect(Rng.EntireRow, Range("FY04_descriptions")).Value

because the code doesn't rely on there being 6 columns between the two you're interested in (you can insert/delete columns without changing the code)
 
Try:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Not Intersect(Range("FY04_reduction_totals"), Target) Is Nothing Then
If Target.Value < 0 & IsEmpty(Target.Offset(0, -7).Value) Then
MsgBox ("please enter a description")
End If
End If

End Sub
 
Private Sub worksheet_change(ByVal Target As Excel.Range)
Dim rng as Range
Dim result As Range
For Each result In Range("FY04_reduction_totals")
If result < 0 And result.Offset(0, 7) = "" Then
if rng is nothing then
set rng = result
else
set rng = union(result,rng)
end if
End If
Next
if not rng is nothing then
rng.Select
rng.Msgbox "Please enter values in cells: " & rng.Address(0,0)
End if
End Sub

--
Regards,
Tom Ogilvy


jjst34 said:
Hello all.. new to this board, thx in advance for any help.. Here is
what I'm trying to do. I'd like a piece of code that says the
following. If any cell in a specific range is < 0 and the cell 7
columns before it, same row (the title of this cell is "description",
is blank, i'd like a message box to come up telling the user they need
to enter a description in that cell. Here is what I have so far.. I am
sorta new to this VBA stuff, so if I'm way off, sorry...

Private Sub worksheet_change(ByVal Target As Excel.Range)

Dim result As Range
For Each result In Range("FY04_reduction_totals")
If result < 0 & result.Offset(0, 7) = "" Then
MsgBox ("please enter a description")
End If
End Sub


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
& is a concatenation operator, it is not a boolean AND operator.

Also, the OP did say he/she wanted to check every cell in the range.
 
re ampersand: Quite right -- copied and pasted OP's code without
checking everything. Any idea why my suggestion still works despite the
ampersand? Does Excel "translate" the ampersand into an AND because it
doesn't make sense in that context? Because both "clauses" evaluate to
True, but the combined clauses still evaluate as True whereas I would
expect a concatenation to be "TrueTrue" or something to that effect.

re check every cell: Missed that.

Thanks,
Dianne

In
 
concatenation is an arithmetic operator and takes precedence over a
comparison operator so

If Target.Value < (0 & IsEmpty(Target.Offset(0, -7).Value)

if Target.value < "0True"

or

if Target.Value < "0False"
 
Thanks for that explanation. I'll keep my eye out for situations where &
is combined with other operators.

Dianne

In
 
Back
Top