|Subject:||Some trouble assigning a string to a variable|
|Posted by:||Howard (lhkitt…@comcast.net)|
|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.
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)
lrTF = .Cells(.Rows.Count, "C").End(xlUp).Row
Set rngTF = .Range("C5:C" & lrTF)
'Application.Goto rngTF 'don't need to select the range
'/ 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"
hFill = c.Offset(, 4).Value ' hFill should be RT6
i = 0