Shorten VBA loop code

  • I just have no idea how to shorten this sort of stuff. The msgbox is used in place of the email code I'll be using, which is working fine. I just need to know how to put this in a loop, all the way to column J. I mean I can do it the long way, but a programmer would laugh at the method I'm using to do this...






  • Re: Can someone please shorten this VBA code for me? Loop code - Should be simple


    Code
    For Each e In Sheets("Data").Range("A3:J3") 
            If e.Value < 5 Then 
                MsgBox ("Hi") & e.Offset(-1).Value 
            Next e 
        End If


    Putting code like that in the Calculate event is not a good idea, IMO. You should explain the logic, there will be a better way.

  • Re: Can someone please shorten this VBA code for me? Loop code - Should be simple


    Maybe this. :cool:


    Code
    Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
         'Workbook_SheetCalculate(ByVal Sh As Object)
        Dim c As Long
         With Worksheets("Data")
        For c = 1 To 10
            If .Cells(3, c).Value < 5 Then
                MsgBox ("Hi ") & .Cells(2, c).Value
            End If
        Next c
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!