+ Reply to Thread
Results 1 to 4 of 4

part of VB not working after query moved to different sheet

Hybrid View

  1. 01-31-2015, 04:00 PM #1
    cmccabe
    cmccabe is offline
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    part of VB not working after query moved to different sheet

    Now that the query has been moved to sheet panel I can not get the calculations to work, so far I have tried: The section in bold works but the rest doesn't. Thank you .

     ' Add Additional Selection Information
With Sheets("panel")
Dim LastRowNo As Long
LastRowNo = .Cells(Rows.Count, "J").End(xlUp).Row
formula = "=panel!$J$1:$J$" & lastrow
End With

Worksheets("annovar").Range("B2").formula = "=INDEX(panel!$J:$O,MATCH($A$2,panel!$J:$J,0),2)"
Worksheets("annovar").Range("C2").formula = "=INDEX(panel!$J:$O,MATCH($A$2,panel!$J:$J,0),3)"
Worksheets("annovar").Range("D2").formula = "=INDEX(panel!$J:$O,MATCH($A$2,panel!$J:$J,0),4)"
Worksheets("annovar").Range("E2").formula = "=INDEX(panel!$J:$O,MATCH($A$2,panel!$J:$J,0),5)"
Worksheets("annovar").Range("F2").formula = "=INDEX(panel!$J:$O,MATCH($A$2,panel!$J:$J,0),6)"
          ' Add Inheritance
Set wsLookUp = Sheets("panel")
 With Sheets("annovar")
   For Each rngCell In .Range("C5", .Range("C" & Rows.Count).End(xlUp))
     If WorksheetFunction.CountIf(wsLookUp.Range("G:G"), rngCell.Value) > 0 Then
      rngCell.Offset(0, 1).Value = WorksheetFunction.VLookup(rngCell.Value, wsLookUp.Range("G:H"), 2, 0)
     Else
      rngCell.Offset(0, 1).Value = "Item not found"
     End If
  Next rngCell
End With
Set wsLookUp = Sheets("panel")
    Res = Application.VLookup(Range("A2").Value, Range("panel!J:O"), 6, 0) - do I need to reference the INDEX/MATCH here (not sure how)

    Const EpilepsyH = "=IF(COUNTIFS(panel!B2:B6712,Q5,panel!C2:C6712,""<=""&R5,panel!D2:D6712, "">=""&R5),VLOOKUP(R5,panel!C2:E6712,3,1),""No"")"
    Const EpilepsyS = "=IF(OR(V5={""intronic"",""splicing""}),(COUNTIFS(panel!B6714:B7731,Q5,panel!C6714:C7731,"">=""&R5,panel!E6714:E7731, ""<=""&R5)+COUNTIFS(panel!B6714:B7731,Q5,panel!D6714:D7731,""<=""&R5,panel!F6714:F7731,"">=""&R5))>0)"
    Const EpilepsyP = "=IF(COUNTIFS(panel!B7732:B25608,Q5,panel!C7732:C25608,""<=""&R5,panel!D7732:D25608,"">=""&R5),VLOOKUP(R5,panel!C7732:E25608,3,1),""No"")"
    
    Const MarfanH = "=IF(COUNTIFS(panel!B25609:B29333,Q5,panel!C25609:C29333,""<=""&R5,panel!D25609:D29333, "">=""&R5),VLOOKUP(R5,panel!C25609:E29333,3,1),""No"")"
    Const MarfanS = "=IF(OR(V5={""intronic"",""splicing""}),(COUNTIFS(panel!B29334:B29916,Q5,Panel!C29334:C29916,"">=""&R5,panel!E29334:E29916, ""<="" & R5)+COUNTIFS(panel!B29334:B29916,Q5,panel!D29334:D29916,""<="" &R5,panel!F29334:F29916,"">=""&R5))>0)"
    Const MarfanP = "=IF(COUNTIFS(panel!B29917:B47793,Q5,panel!C29917:C47793,""<=""&R5,panel!D29917:D47793,"">=""&R5),VLOOKUP(R5,panel!C29917:E47793,3,1),""No"")"
    
    Const NoonanH = "=IF(COUNTIFS(panel!B47794:B49540,Q5,panel!C47794:C49540,""<=""&R5,panel!D47794:D49540, "">=""&R5),VLOOKUP(R5,panel!C47794:E49540,3,1),""No"")"
    Const NoonanS = "=IF(OR(V5={""intronic"",""splicing""}),(COUNTIFS(panel!B49541:B49755,Q5,panel!C49541:C49755,"">=""&R5,panel!E49541:E49755, ""<=""&R5)+COUNTIFS(panel!B49541:B49755,Q5,panel!D49541:D49755,""<=""&R5,panel!F49541:F49755,"">=""&R5))>0)"
    Const NoonanP = "=IF(COUNTIFS(panel!B49756:B67632,Q5,panel!C49756:C67632,""<=""&R5,panel!D49756:D67632,"">=""&R5),VLOOKUP(R5,panel!C49756:E67632,3,1),""No"")"
    
    If Not IsError(Res) Then
         l = Sheets("annovar").Range("A" & Rows.Count).End(xlUp).Row
         
               Select Case Res
        
            Case "Comprehensive Epilepsy"
                Sheets("annovar").Range("AQ5:AQ" & l).formula = EpilepsyH 'Homopolymer
                Sheets("annovar").Range("AR5:AR" & l).formula = EpilepsyS 'Splice
                Sheets("annovar").Range("AS5:AS" & l).formula = EpilepsyP 'Pseudogene
                
            Case "FBN1 Only"
               Application.ScreenUpdating = False
    With Range("W4:W" & Range("W" & Rows.Count).End(3)(1).Row)
        .AutoFilter 1, "<>FBN1"
        With Range("W5:W" & Range("W" & Rows.Count).End(3)(1).Row).SpecialCells(12)
            .Interior.ColorIndex = 2
            .EntireRow.Interior.ColorIndex = 1
        End With
    End With
    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
                
            Case "Marfan Disorder"
               Sheets("annovar").Range("AQ5:AQ" & l).formula = MarfanH 'Homopolymer
               Sheets("annovar").Range("AR5:AR" & l).formula = MarfanS 'Splice
               Sheets("annovar").Range("AS5:AS" & l).formula = MarfanP 'Pseudogene
               
            Case "Noonan Syndrome"
               Sheets("annovar").Range("AQ5:AQ" & l).formula = NoonanH 'Homopolymer
               Sheets("annovar").Range("AR5:AR" & l).formula = NoonanS 'Splice
               Sheets("annovar").Range("AS5:AS" & l).formula = NoonanP 'Pseudogene
               
        End Select
End If
    When I step through the code Res comes up as empty when it used to have a value. Thanks.
    Reply With QuoteRegister To Reply
  2. 01-31-2015, 05:11 PM #2
    fredlo2010
    fredlo2010 is offline
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: part of VB not working after query moved to different sheet

    Try this:

     Set wsLookUp = Sheets("panel")
    Res = Application.VLookup(Range("A2").Value, wsLookUp.Range("J:O"), 6, 0) '- do I need to reference the INDEX/MATCH here (not sure how)
    Thanks
    Reply With QuoteRegister To Reply
  3. 02-02-2015, 11:44 AM #3
    cmccabe
    cmccabe is offline
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: part of VB not working after query moved to different sheet

    I made the changes and the calculations do not work and stepping through Res is "Empty". Thank you .
    Reply With QuoteRegister To Reply
  4. 02-02-2015, 01:30 PM #4
    cmccabe
    cmccabe is offline
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: part of VB not working after query moved to different sheet

    Below is the full code, using this Res is "Empty"... I think that is part of the problem but I'm not sure. Thank you .

     Sub Calculations()
Dim l As Long
Dim Res As Variant

' Add Additional Selection Information
With Sheets("panel")
Dim LastRowNo As Long
LastRowNo = .Cells(Rows.Count, "J").End(xlUp).Row
formula = "=panel!$J$1:$J$" & lastrow
End With

Worksheets("annovar").Range("B2").formula = "=INDEX(panel!$J:$O,MATCH($A$2,panel!$J:$J,0),2)"
Worksheets("annovar").Range("C2").formula = "=INDEX(panel!$J:$O,MATCH($A$2,panel!$J:$J,0),3)"
Worksheets("annovar").Range("D2").formula = "=INDEX(panel!$J:$O,MATCH($A$2,panel!$J:$J,0),4)"
Worksheets("annovar").Range("E2").formula = "=INDEX(panel!$J:$O,MATCH($A$2,panel!$J:$J,0),5)"
Worksheets("annovar").Range("F2").formula = "=INDEX(panel!$J:$O,MATCH($A$2,panel!$J:$J,0),6)"
   
   ' Add Inheritance
Set wsLookUp = Sheets("panel")
 With Sheets("annovar")
   For Each rngCell In .Range("C5", .Range("C" & Rows.Count).End(xlUp))
     If WorksheetFunction.CountIf(wsLookUp.Range("G:G"), rngCell.Value) > 0 Then
      rngCell.Offset(0, 1).Value = WorksheetFunction.VLookup(rngCell.Value, wsLookUp.Range("G:H"), 2, 0)
     Else
      rngCell.Offset(0, 1).Value = "Item not found"
     End If
  Next rngCell
End With
Set wsLookUp = Sheets("panel")
    Res = Application.VLookup(Range("A2").Value, wsLookUp.Range("J:J"), 6, 0)

    Const EpilepsyH = "=IF(COUNTIFS(panel!B2:B6712,Q5,panel!C2:C6712,""<=""&R5,panel!D2:D6712, "">=""&R5),VLOOKUP(R5,panel!C2:E6712,3,1),""No"")"
    Const EpilepsyS = "=IF(OR(V5={""intronic"",""splicing""}),(COUNTIFS(panel!B6714:B7731,Q5,panel!C6714:C7731,"">=""&R5,panel!E6714:E7731, ""<=""&R5)+COUNTIFS(panel!B6714:B7731,Q5,panel!D6714:D7731,""<=""&R5,panel!F6714:F7731,"">=""&R5))>0)"
    Const EpilepsyP = "=IF(COUNTIFS(panel!B7732:B25608,Q5,panel!C7732:C25608,""<=""&R5,panel!D7732:D25608,"">=""&R5),VLOOKUP(R5,panel!C7732:E25608,3,1),""No"")"
    
    Const MarfanH = "=IF(COUNTIFS(Panel!B25609:B29333,Q5,Panel!C25609:C29333,""<=""&R5,Panel!D25609:D29333, "">=""&R5),VLOOKUP(R5,Panel!C25609:E29333,3,1),""No"")"
    Const MarfanS = "=IF(OR(V5={""intronic"",""splicing""}),(COUNTIFS(Panel!B29334:B29916,Q5,Panel!C29334:C29916,"">=""&R5,Panel!E29334:E29916, ""<="" & R5)+COUNTIFS(Panel!B29334:B29916,Q5,Panel!D29334:D29916,""<="" &R5,Panel!F29334:F29916,"">=""&R5))>0)"
    Const MarfanP = "=IF(COUNTIFS(Panel!B29917:B47793,Q5,Panel!C29917:C47793,""<=""&R5,Panel!D29917:D47793,"">=""&R5),VLOOKUP(R5,Panel!C29917:E47793,3,1),""No"")"
    
    Const NoonanH = "=IF(COUNTIFS(Panel!B47794:B49540,Q5,Panel!C47794:C49540,""<=""&R5,Panel!D47794:D49540, "">=""&R5),VLOOKUP(R5,Panel!C47794:E49540,3,1),""No"")"
    Const NoonanS = "=IF(OR(V5={""intronic"",""splicing""}),(COUNTIFS(Panel!B49541:B49755,Q5,Panel!C49541:C49755,"">=""&R5,Panel!E49541:E49755, ""<=""&R5)+COUNTIFS(Panel!B49541:B49755,Q5,Panel!D49541:D49755,""<=""&R5,Panel!F49541:F49755,"">=""&R5))>0)"
    Const NoonanP = "=IF(COUNTIFS(Panel!B49756:B67632,Q5,Panel!C49756:C67632,""<=""&R5,Panel!D49756:D67632,"">=""&R5),VLOOKUP(R5,Panel!C49756:E67632,3,1),""No"")"
    
    If Not IsError(Res) Then
         l = Sheets("annovar").Range("A" & Rows.Count).End(xlUp).Row
         
               Select Case Res
        
            Case "Comprehensive Epilepsy"
                Sheets("annovar").Range("AQ5:AQ" & l).formula = EpilepsyH 'Homopolymer
                Sheets("annovar").Range("AR5:AR" & l).formula = EpilepsyS 'Splice
                Sheets("annovar").Range("AS5:AS" & l).formula = EpilepsyP 'Pseudogene
                
            Case "Infantile Epilepsy"
                Sheets("annovar").Range("AQ5:AQ" & l).formula = EpilepsyH 'Homopolymer
                Sheets("annovar").Range("AR5:AR" & l).formula = EpilepsyS 'Splice
                Sheets("annovar").Range("AS5:AS" & l).formula = EpilepsyP 'Pseudogene
                
            Case "Theraputic Epilepsy"
                Sheets("annovar").Range("AQ5:AQ" & l).formula = EpilepsyH 'Homopolymer
                Sheets("annovar").Range("AR5:AR" & l).formula = EpilepsyS 'Splice
                Sheets("annovar").Range("AS5:AS" & l).formula = EpilepsyP 'Pseudogene
                
            Case "FBN1 Only"
               Application.ScreenUpdating = False
    With Range("W4:W" & Range("W" & Rows.Count).End(3)(1).Row)
        .AutoFilter 1, "<>FBN1"
        With Range("W5:W" & Range("W" & Rows.Count).End(3)(1).Row).SpecialCells(12)
            .Interior.ColorIndex = 2
            .EntireRow.Interior.ColorIndex = 1
        End With
    End With
    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
                
            Case "Marfan Disorder"
               Sheets("annovar").Range("AQ5:AQ" & l).formula = MarfanH 'Homopolymer
               Sheets("annovar").Range("AR5:AR" & l).formula = MarfanS 'Splice
               Sheets("annovar").Range("AS5:AS" & l).formula = MarfanP 'Pseudogene
               
            Case "Noonan Syndrome"
               Sheets("annovar").Range("AQ5:AQ" & l).formula = NoonanH 'Homopolymer
               Sheets("annovar").Range("AR5:AR" & l).formula = NoonanS 'Splice
               Sheets("annovar").Range("AS5:AS" & l).formula = NoonanP 'Pseudogene
               
        End Select
End If
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Web Query not working because Excel removes part of the URL link!
    By bcn1988 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-11-2013, 10:02 AM
  2. subtracting part from column1 and moved to another column
    By sqlindia in forum Excel General
    Replies: 2
    Last Post: 06-30-2012, 02:34 AM
  3. change part of the address for multiple hyperlinks (folder moved)
    By cch70 in forum Excel General
    Replies: 4
    Last Post: 01-29-2010, 01:19 PM
  4. i am working with excel sheet. in the calculation part i want if
    By srinivas in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-27-2006, 03:20 PM
  5. [SOLVED] Right justify moved data only part way
    By hgopp99 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 11-23-2005, 09:45 AM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1