PDA

View Full Version : hyphen in code



cmccabe1
05-10-2014, 09:13 AM
Can there be a hyphen in a line of VBA? The condition below ( is the only one that does not work. Thanks



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

HaHoBe
05-10-2014, 09:30 AM
continuation from http://www.excelforum.com/excel-programming-vba-macros/1010016-multi-line-vba-not-working-after-column-order-change.html

Paul_Hossler
05-10-2014, 01:07 PM
1. I think the people in the other forum were wrong about the use of the With/End With. This seems to work fine



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

snb
05-10-2014, 01:37 PM
You'd better work in memory than in sheets/ranges/cells


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

p45cal
05-10-2014, 02:03 PM
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.

Paul_Hossler
05-10-2014, 04:06 PM
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)

p45cal
05-11-2014, 01:34 AM
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.


but I still think IMHO the With / End With is better (or at least my preference)I'm with you on that.