Some trouble assigning a string to a variable

Giganews Newsgroups
Subject: Some trouble assigning a string to a variable
Posted by:  Howard (lhkitt…
Date: Fri, 6 Dec 2013

The code checks a variable length column whose cells are a link to another column of check boxes.  A checked box returns a TRUE to this column which is rngTF, otherwise it is FALSE.

The For Each c In rngTF first checks if more than one TRUE is in the column and offers up a MsgBox if more than one TRUE.

If the column only has one TRUE I want to set the range variable  hFill to the value four columns offset from the TRUE in C column, which is column G.

The Msgbox to display the value of hFill displays blank.


Option Explicit

Sub FillColH()
Dim c As Range, i As Long
Dim rngTF As Range '/ Column C Studies TRUE/FALSE's
Dim lrTF As Long  '/ Last row in column C Studies
Dim Eitem As Variant '/ The drop down value in Studies E1
Dim hFill As String '/ the value four columns offset
                    '/ from TRUE value in rngTF (Column C Studies sheet)

With Sheets("Studies")
  lrTF = .Cells(.Rows.Count, "C").End(xlUp).Row
  Set rngTF = .Range("C5:C" & lrTF)
  'Application.Goto rngTF  'don't need to select the range
End With

'/ set drop down value to variable Eitem
Eitem = Range("E1").Value

'/ Test Studies column C for more than one TRUE
'/ More than one checked box in column D
i = 0
For Each c In rngTF
  If c = True Then
      i = i + 1
      If i > 1 Then
        MsgBox "There is more than ONE box checked in column D" & vbCr & _
              "  Review the boxes checked and check only one.", vbOKCancel, "Boxer Boxer"
        Exit Sub
      hFill = c.Offset(, 4).Value ' hFill should be RT6
      End If
  End If
MsgBox hFill
i = 0
End Sub