Repeating VBA macro down a range of cells...

[Virge]

Hi,

I have written an excel macro, see below... it works fine for one single cell, but I need it to work down a column range of say 80 to 100 cells and can"t figure out how to make it go down the column. I have tried a loop, but all I was able to do was to compare M7 with the rest of the cells. What I want is to have this macro in each cell down the column M so when that particular item inventory reaches a certain value it brings up the msg box.

Any suggestions? THANKS!!!




Public Sub myMacro()




If Range("M7").Value <= Range("N7").Value And Range("M7").Value > 0 Then


MsgBox "INVENTORY HAS REACHED ORDERING POINT of " & Range("N7").Value & " You ONLY have" & " ( " & Range("M7").Value & " ) " & "Units left.", vbCritical, "WARNING!"

ElseIf Range("M7").Value = 0 Then

MsgBox "YOU ARE OUT OF INVENTORY!!" & " " & "YOU HAVE" & " ( " & Range("M7").Value & " ) " & " - NO UNITS LEFT!", vbCritical, "ALERT!!"

Else

If Range("M7").Value < 0 Then

MsgBox "YOU HAVE NEGATIVE INVENTORY!!!" & " " & "You are NEGATIVE BY" & " " & " (- " & 0 - Range("M7").Value & " ) " & "Units", vbCritical, "ALERT!!!"

End If

End If



End Sub

 

[PatOBrien198]

I often define a starting point (eg. Range("M7")) and use the Offset method to loop to the end of the list.....

dim rngStart as Range
set rngStart = Range("M7")

dim r as long
r = 0

Do While rngStart.Offset(r, 0) <> ""

If rngStart.Offset(r, 0).Value <= rngStart.Offset(r, 1).Value And rngStart.Offset(r,0).Value > 0 Then
.... rest of your code - 
changing Range("M7") to rngStart.Offset(r, 0)
changing Range("N7") to rngStart.Offset(r, 1)
end if


    r = r + 1
Loop

 

[Virge]

Hi Thanks... I am getting a Syntax error though in the last two statements. I have checked for proper syntax, but can not find anything on it. What am I missing?

Changing Range("M7") to rngStart.Offset(r, 0)Changing Range("N7") to rngStart.Offset(r, 1)

dim rngStart as Range
set rngStart = Range("M7")

dim r as long
r = 0

Do While rngStart.Offset(r, 0) <> ""

If rngStart.Offset(r, 0).Value <= rngStart.Offset(r, 1).Value And rngStart.Offset(r,0).Value > 0 Then
.... rest of your code - 
changing Range("M7") to rngStart.Offset(r, 0)
changing Range("N7") to rngStart.Offset(r, 1)
end if


    r = r + 1
Loop

[/QUOTE]

 

[PatOBrien198]

I meant that you should use the rest of your original code and make changes to it: replacing
all of your "Range("M7")" code to "rngStart.Offset(r,0)" (no quotes)

'as an example .... 
'your original code was:
If Range("M7").Value <= Range("N7").Value And Range("M7").Value > 0 Then

'should be changed to:
If rngStart.Offset(r, 0).Value <= rngStart.Offset(r, 1).Value And rngStart.Offset(r,0).Value > 0 Then