title: Excel VBA - error handling and string searching
body: <p>Background:</p>
  
  <p>I have a spreadsheet which is acting as an entry form. There are numerous entry fields, with a string in one cell denoting the title of the quantity and a value of the quantity of the cell below it. </p>
  
  <p>Only some of these fields are mandatory. I am trying to write a macro to cycle through all mandatory fields and ensure there is a value present. </p>
  
  <p>Important Variables:</p>
  
  <p>MandatoryValues = array of strings containing the mandatory field titles</p>
  
  <p>MandatoryCount = the number of mandatory fields</p>
  
  <p>FindString = the field title as a string</p>
  
  <p>r = the row of the field title</p>
  
  <p>c = the column of the field title</p>
  
  <p>Check_Value = the value of the cell beneath the field title which contains the desired value</p>
  
  <p>Below is my code:</p>
  
  <pre><code>For i = 1 To MandatoryCount
      FindString = MandatoryValues(i - 1)
          With ActiveSheet.UsedRange
              Set Rng = .Find(What:=FindString, _
                              After:=.Cells(.Cells.Count), _
                              LookIn:=xlValues, _
                              LookAt:=xlWhole, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlNext, _
                              MatchCase:=False)
              If Not Rng Is Nothing Then
                  r = Rng.Row
                  c = Rng.Column
                  Check_Value = ActiveSheet.Cells(r + 1, c).Value
                  data_type = TypeName(Check_Value)
  
                  If data_type = "String" Then
                      If Check_Value = "" Then
                          If MsgBox(FindString &amp; " Not Specified", vbOKOnly, "ERROR") = vbOK Then
                              Exit Sub
                          End If
                      End If
                  End If
  
                  If data_type = "Double" Then
                      If Check_Value = "" Then
                          If MsgBox(FindString &amp; " Not Specified", vbOKOnly, "ERROR") = vbOK Then
                              Exit Sub
                          End If
                      End If
                  End If
  
                  If data_type = "Date" Then
                      If Check_Value = 0 Then
                          If MsgBox(FindString &amp; " Not Specified", vbOKOnly, "ERROR") = vbOK Then
                              Exit Sub
                          End If
                      End If
                  End If
  
                  Else
              End If
          End With
  Next i
  </code></pre>
  
  <p>For each loop, I search the UsedRange in the sheet for the title, and locate its position with .Find. This works fine. My problem is that as I am stepping through the code, I can see FindString, r and c changing with each iteration, but Check_Value is always empty. I feel like it's something dumb but I can't see it. Any suggestions? </p>
  
  <p>Edit: I have added the code snippet</p>
  
  <pre><code>If Not Rng Is Nothing Then
              Address = Rng.Address
              Address_Below = Rng.Offset(1, 0)
  </code></pre>
  
  <p>The Address value updates for every iteration, and it is correctly finding the string in the sheet and retrieving the range. However, the Address_Below variable is always empty. I have no idea why this would be. Any ideas?</p>