hyphen in code

Can there be a hyphen in a line of VBA? The condition below ( is the only one that does not work. Thanks

Code:

---

  For iRow = 2 To rData.Rows.Count
        With rData.Rows(iRow)
            If .Cells(iGender).Value = "Male" And .Cells(iInheritance).Value = "x-linked dominant" And .Cells(iPopFreqMax).Value <= 0.01 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "" Then .Cells(iClassification).Value = "likely pathogenic"
            If .Cells(iGender).Value = "Female" And .Cells(iInheritance).Value = "x-linked recessive" And .Cells(iPopFreqMax).Value <= 0.1 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "" Then .Cells(iClassification).Value = "likely pathogenic"
            If .Cells(iGender).Value = "Male" And .Cells(iInheritance).Value = "x-linked dominant" And .Cells(iPopFreqMax).Value >= 0.01 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "" Then .Cells(iClassification).Value = "likely benign"
            If .Cells(iGender).Value = "Female" And .Cells(iInheritance).Value = "x-linked recessive" And .Cells(iPopFreqMax).Value >= 0.1 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "" Then .Cells(iClassification).Value = "likely benign"
            If .Cells(iGender).Value = "Male" And .Cells(iInheritance).Value = "x-linked dominant" And .Cells(iPopFreqMax).Value <= 0.01 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "common" Then .Cells(iClassification).Value = "???"
            If .Cells(iGender).Value = "Female" And .Cells(iInheritance).Value = "x-linked recessive" And .Cells(iPopFreqMax).Value <= 0.1 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "common" Then .Cells(iClassification).Value = "???"
           
            If .Cells(iClassification).Value = "likely pathogenic" Then .Cells(iClassification).EntireRow.Interior.ColorIndex = 7 'Magenta
            If .Cells(iClassification).Value = "likely benign" Then .Cells(iClassification).EntireRow.Interior.ColorIndex = 8 'Cyan
            If .Cells(iClassification).Value = "???" Then .Cells(iClassification).EntireRow.Interior.ColorIndex = 22 'Pink


        End With
    Next iRow

---

continuation from http://www.excelforum.com/excel-prog...er-change.html

1. I think the people in the other forum were wrong about the use of the With/End With. This seems to work fine

Code:

---

Sub test2()
    Dim iRow As Long
    Dim rData As Range
   
    ActiveSheet.Cells(1, 1).Value = 111
    ActiveSheet.Cells(2, 1).Value = 222
    ActiveSheet.Cells(3, 1).Value = 333
    ActiveSheet.Cells(4, 1).Value = 444
    ActiveSheet.Cells(1, 2).Value = 555
    ActiveSheet.Cells(2, 2).Value = 666
    ActiveSheet.Cells(3, 2).Value = 777
    ActiveSheet.Cells(4, 2).Value = 888
   
    Set rData = ActiveSheet.Cells(1, 1).CurrentRegion
   
    For iRow = 1 To rData.Rows.Count
        With rData.Rows(iRow)
            MsgBox .Cells(1).Value
            MsgBox .Cells(2).Value
        End With
    Next iRow
End Sub

---

2. Where is the hyphen? in the "x-link"? That should cause no issues

3. What do you mean by the condition below not working? Can you post a simple example?

BTW, there is always the possibility that the logic is performing correctly, but it is the wrong logic, OR the data is causing the unexpected results

You'd better work in memory than in sheets/ranges/cells

Code:

---

Sub test2()
  with ActiveSheet
    .Cells(1, 1).Value = 111
    .Cells(2, 1).Value = 222
    .Cells(3, 1).Value = 333
    .Cells(4, 1).Value = 444
    .Cells(1, 2).Value = 555
    .Cells(2, 2).Value = 666
    .Cells(3, 2).Value = 777
    .Cells(4, 2).Value = 888
  end with
 
      sn = ActiveSheet.Cells(1, 1).CurrentRegion
   
      For j=1 To ubound(sn)
    MsgBox sn(j,1) & vbtab & sn(j,2)
  Next
End Sub

---

Change each instance of:
If .Cells(iGender).Value =
to:
If Cells(2,iGender).Value =
Note the missing dot before Cells.
This is because the Male or Female value only occurs in one cell in the entire sheet, not on each row.

Other points, your:
For iRow = 2 To rData.Rows.Count
I think should be:
For iRow = 4 To rData.Rows.Count

You have at least 4 pairs of lines like:
If .Cells(iInheritance).Value = "autosomal dominant" And .Cells(iPopFreqMax).Value <= 0.01 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "" Then .Cells(iClassification).Value = "likely pathogenic"
If .Cells(iInheritance).Value = "autosomal dominant" And .Cells(iPopFreqMax).Value >= 0.01 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "" Then .Cells(iClassification).Value = "likely benign"
where, as it happens with your data where you have values of 0.01 exactly, both lines are true. Normally, one of those statements would not contain the = sign in the comparison. This prevents overlap of values where both conditions end up being true.

So you'd have either:
~(iPopFreqMax).Value < 0.01 And~
~(iPopFreqMax).Value >= 0.01 And~
or:
~(iPopFreqMax).Value <= 0.01 And~
~(iPopFreqMax).Value > 0.01 And~

depending on which category you want exact values of 0.01 to be placed in.
Remember, whichever of these you choose, to make the other comparisons the same, even where you don't have paired lines.

Quote:

---

This is because the Male or Female value only occurs in one cell in the entire sheet, not on each row.

---

Where did you see that? No attachment that I saw.

If true -- and I assume it is -- then that would make a difference to some of the code, but I still think IMHO the With / End With is better (or at least my preference)

Quote:

---

Originally Posted by Paul_Hossler

Where did you see that? No attachment that I saw.

---

In Msg#2 HaHoBe provides a link to a thread in another forum whose Msg#1has an attachment/link.

Quote:

---

Originally Posted by Paul_Hossler

but I still think IMHO the With / End With is better (or at least my preference)

---

I'm with you on that.