Loop Iteration wont work as expected

Question

For some reason its not going to the next cell in the range to check the value.

Break down of what is meant to happen

Sub calls Modules1.Getdata

This Checks each row for a notification tag ("True/False"). If true it grabs CompanyNumber calls Module3.Check

Moduel3.Check takes the CompanyNumber checks another sheet/range for Samevalue (Go to next Iteration in Module1.Getdata) Next cell if blank, enter company number etc.

Hope that makes sense.

Sub

  Sub Workbook_open()

  Call Module1.GetData

  End Sub

Module1.GetData

  Public EmailAddress As String
  Public CompanyNumber As String
  Public Name As String
  Public Comp As String
  Public ID As Integer


  Function GetData()

  Dim LastRow As String
  Dim rng As Range


  Worksheets("DDregister").Activate
  Range("K2").Select


  LastRow = Cells(Rows.Count, "K").End(xlUp).Row

  For Each rng In Range("K2:K" + LastRow)

  If Not rng.Value = vbNullString Then
      Worksheets("DDregister").Activate
      Range("K2").Select

      Select Case rng.Value
        Case 1
            Case Is = "True"
            rng.Select

                Let EmailAddress = ActiveCell.Offset(0, -5).Value
                Let CompanyNumber = ActiveCell.Offset(0, -9).Value
                Let Name = ActiveCell.Offset(0, -8).Value
                Let Comp = ActiveCell.Offset(0, -7).Value
                ID = ActiveCell.Offset(0, -10).Value


                Call Module3.Check(EmailAddress, CompanyNumber, Name, Comp)
        Case 2
            Case Is = "False"
     End Select

ElseIf rng.Value = vbNullString Then
    ThisWorkbook.Save
    Application.DisplayAlerts = True
    'ThisWorkbook.Close
End If
Next

End Function

Module3.Check

Function Check(EmailAddress As String, CompanyNumber As String, Name As String, Comp As String)

Dim rngCheck As Range
Dim LastRowCheck As String
Dim NewRange As Range

Worksheets("Check").Activate
ActiveSheet.Range("B2").Select

LastRowCheck = Cells(Rows.Count, "B").End(xlDown).Row

For Each rngCheck In Range("B2:B" + LastRowCheck)

   Select Case rngCheck.Value
    Case 1
        Case Is = CompanyNumber
        'Go to next iteration
    Case 2
        Case Is = vbNullString
            ActiveCell.Value = CompanyNumber
            ActiveCell.Offset(0, 1).Value = "True"
            ActiveCell.Offset(0, -1).Value = ID
            Call Module2.Email(EmailAddress, CompanyNumber, Name, Comp)

Next

End Function

Module2.Email

Function Email(EmailAddress As String, CompanyNumber As String, Name As String, Comp As String)

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Subject " & (Comp)
objMessage.From = "[email protected]"
objMessage.Cc = "[email protected]"
objMessage.To = (EmailAddress)
'MsgBox (EmailAddress)
objMessage.TextBody = "Stuff"

objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "x.x.x.x"

objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

objMessage.Configuration.Fields.Update

objMessage.Send

End Function

Are you not getting a type mismatch error on 'For Each rng In Range("K2:K" + LastRow)' The '+' should be '&' — Automate This
Didnt get type mismatch but have changed the + to a & anyway. Thanks for pointing that out. — k3eper
As a starter, you aren't referencing ranges properly, your code only looks at the activesheet to get the last row. You need to be explicit as VBA will otherwise imply the sheet: Lastrow = Worksheets("DDregister").Cells(Rows.Count, "K").End(xlUp).Row — SWa
Your Case Statements seem off to me you are saying if rng.Value = 1 Do nothing if rng.Value = CompanyNumber do nothing if rng.Value = 2 Do nothing if rng.Value = vbNullString do something. Case Is is not assignment it is simply comparing the values of the case and the supplied value. Just because you have indented does not mean anything these re 4 separate Cases and will be evaluated in the same select. — engineersmnky

engineersmnky engineersmnky · Accepted Answer · 2013-09-30T15:25:43

Assuming "True" And "False" Are actually strings and not the boolean values I think GetData should look more like the following:

Sub GetData()
    Dim LastRow As String
    Dim rng As Range
    Dim EmailAddress As String
    Dim CompanyNumber As String
    Dim Name As String
    Dim Comp As String
    Dim ID As Integer

    Worksheets("DDregister").Activate
    Range("K2").Select


    Lastrow = Worksheets("DDregister").Cells(Rows.Count, "K").End(xlUp).Row

    For Each rng In Range("K2:K" & LastRow)
      Select Case rng.value
          Case "True"
              EmailAddress = Worksheets("DDregister").Cells(rng.Row,"F").Value
              CompanyNumber = Worksheets("DDregister").Cells(rng.Row,"B").Value
              Name = Worksheets("DDregister").Cells(rng.Row,"C").Value
              Comp = Worksheets("DDregister").Cells(rng.Row,"D").Value
              ID = Worksheets("DDregister").Cells(rng.Row,"A").Value
              Call Module3.Check(EmailAddress, CompanyNumber, Name, Comp)
          Case "False"
          Case vbNullString
              ThisWorkbook.Save
              Application.DisplayAlerts = True
              'ThisWorkbook.Close
     End Select
   Next rng
End Sub

Also this is a sub because it does not return anything and why have you put all of these routines in different modules? Since you are passing the values there is no reason to make them global by listing them outside the sub

P.S. I did not fix your other SELECT CASE Statement but it has similar issues. SELECT CASE syntax the way you are using it is as follows

 SELECT CASE [expression]
      CASE [condition]
      CASE [condition]
      CASE ELSE
 END SELECT

Loop Iteration wont work as expected

3 Answers