General discussion

Locked

Excel VBA programming question

By matthew.m.mason2 ·
I hope I've come to the right place on this, I'm a java guy and am not familiar with the VBA syntax. I've been asked by an attorney at my firm to write an excel spreadsheet that can email deadlines for filings once they fall below a 15 day limit. Sounds simple eh? Well I've got the majority of the code written with help from a great website. The problem is the code only polls for one cell and I need to make it poll for multiple cells, basically do a for loop or increase the cell range. Below is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("A1"), rng) Is Nothing Then
If Range("A1").Value < 16 Then Mail_with_outlook
End If
End If
EndMacro:
End Sub

I've tried changing the cell range directly from A1 to A1:Z1 or "A1", "Z1" in the argument of the function. No go. Anybody have any ideas on how to make this thing poll for more than one row/cell? Your help is greatly appreciated.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Use a for-each loop in a macro

by bschaettle In reply to Excel VBA programming que ...

Just for starters, I see you've put this code in an event handler--why? Just inexperience? I would start by making this a macro (really just a Sub that's available to the user). Then create a button on the spreadsheet that will execute the macro. Within the macro, declare a range and loop through the cells collection in that range to do the processing you want. Sample code follows.

Sub send_emails()
'
' send_emails Macro
'
Dim MyRange As Range
On Error GoTo LeaveSub
Set MyRange = ActiveSheet.Range("$A$1:$Z$1")
For Each cCell In MyRange.Cells
....
Next
LeaveSub:
Set MyRange = Nothing
End Sub ' send_emails

Collapse -

by The Admiral In reply to Excel VBA programming que ...

Instead of:

If Not Intersect(Range("A1"), rng) Is Nothing Then
If Range("A1").Value < 16 Then Mail_with_outlook

Try:

If Not Intersect(Range("A:A"), rng) Is Nothing Then
If Range("A:A").Value < 16 Then Mail_with_outlook

Collapse -

Loop within Excel

Matthew,

If all the cells including ?A1? and below have the values in them, then you can do something like this
----------------------------------------------
a = 1

xvalue = cells(a,1).value

do while xvalue <> ??

if xvalue < 16 then
Mail_with_outlook
End if

a = a + 1
xvalue = cells(a,1).value
Loop
----------------------------------------------

Cheers

Collapse -

Cancelling out

by jmalbitz In reply to Excel VBA programming que ...

I believe that your line of code...
"If Target.Cells.Count > 1 Then Exit Sub"
immediately prevents you from doing anything with multiple cells.

Collapse -

by posselman In reply to Excel VBA programming que ...

get rid of the
If Target.Cells.Count > 1 Then Exit Su

Collapse -

Mind Sharing the Website Location?

by chaleepas In reply to Excel VBA programming que ...

I think it's great you found a resource to help you with this. Would you mind sharing the website with others so they can have access to the same resource? Thanks.

Related Discussions

Related Forums