VB Code Is Not Working

[Guest]

Hi,

I'm trying to make a code that alerts me when a cell is equal to today's
date. The message spits out the value of a cell that is adjacent to the
active cell but the code isn't working. Could someone please loot at it and
tell me where I went wrong?

Here's the Code:::

Private Sub Workbook_Open()
Dim Ws As Worksheets
Dim sRange As Range
Dim nDate As Date
Dim RowId As Integer
Dim lItem As Integer


Ws = Worksheets("Slabber Reports")
sRange = Ws("N8:N5000")
nDate = Today()
RowId = ActiveCell.Row

For Each Item In sRange
If sRange = nDate Then
lItem = Range("B & RowId & ").FormulaR1C1
MsgBox "Line Item " & lItem & " Is Now Active. Please Perform The Appropiate
Actions To Reflect This Activity", vbOKOnly
Next
Wend
MsgBox "That's All For Today", vbOKOnly
End If
End Sub


Thanks Very Much In Advance!!!
Rob

 

[Dave O]

Hi, Rob-
I made some changes to your code- with all due respect to your effort
I saw a couple problems in your original version. For instance,
Today() is not a VBA function by itself; I changed that to Int(Now())
which does what you needed it to do. Some other things: you have a
WEND with no WHILE, and your FOR EACH and IF structures overlap: the
IF needs to be self-contained within the FOR EACH, but your IF's END
IF occurs after the NEXT statement.

These points should have become clear when you attempted to compile or
run your code. If I may offer a tip, when you declare variables
include a capital letter in each, and when you enter VBA code type it
in all lower letters. That way when the compiler recognizes a reserved
word or one of your variables, you'll see a letter change case to
upper, and you can then be sure you typed it properly.

Please note some text wrapping may occur in this usenet post: if you
get a compile error you may need to fix a line that was wrapped in
posting.

Hope you'll accept this as the constructive message it was intended to
be~
Dave O

Private Sub Workbook_Open()
Dim rCell As Range

Sheets("Slabber Reports").Select
Range("n8:n5000").Select

For Each rCell In Selection.Cells
If rCell.Value = Int(Now()) Then
MsgBox "Line Item " & rCell.Address & " is now active. Please
perform the appropiate actions to reflect this activity.", vbOKOnly
End If
Next rCell

MsgBox "That's all for today.", vbOKOnly

End Sub

 

[Guest]

It works great and you used so much less code to do it as well.

Thanks Much!!