Consulting

Results 1 to 7 of 7

Thread: hyphen in code

  1. #1
    VBAX Regular
    Joined
    May 2014
    Posts
    71
    Location

    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

      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

  2. #2
    VBAX Regular HaHoBe's Avatar
    Joined
    Aug 2004
    Location
    Hamburg
    Posts
    89
    Location

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.
    Last edited by p45cal; 05-10-2014 at 02:15 PM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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)
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by Paul_Hossler View Post
    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 View Post
    but I still think IMHO the With / End With is better (or at least my preference)
    I'm with you on that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •