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

[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

 

[James]

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)

 

[Dianne]

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

 

[Tom Ogilvy]

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

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

creating financial statements

 

[Tom Ogilvy]

& 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.

--
Regards,
Tom Ogilvy

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

--
HTH,
Dianne

In

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


------------------------------------------------
[/url]
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

 

[Dianne]

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

& 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.

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

--
HTH,
Dianne

In

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


------------------------------------------------
[/url]
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

 

[Tom Ogilvy]

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"


--
Regards,
Tom Ogilvy

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

& 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.

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

--
HTH,
Dianne

In jjst34 <[email protected]> typed:
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


------------------------------------------------
[/url]
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

 

[Dianne]

Thanks for that explanation. I'll keep my eye out for situations where &
is combined with other operators.

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"

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

& 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.


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

--
HTH,
Dianne

In jjst34 <[email protected]> typed:
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


------------------------------------------------
[/url]
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements