+ Reply to Thread
Results 1 to 2 of 2

Code for adding section broken - help!

  1. #1
    Registered User
    Join Date
    08-21-2016
    Location
    Shanghai, China
    MS-Off Ver
    Professional Plus 2013
    Posts
    1

    Question Code for adding section broken - help!

    Hi everyone,

    I just took ownership of someone else's Excel file, and some of the code seems to be broken. I'm very far from an excel expert - so would really appreciate any help!!

    When I edit cell C18 in the attached picture the code should run, and add or take away sections depending on how many "channels" I need - but it breaks every time:

    Below is the code that is messing things up - the bug seems to be in the third line of the "INIT" section.

    Very happy to send the file to anyone that thinks they could help. Thank you so much!!

    Public Sub HideChannels(ByVal iChannels As Integer)

    Dim xlSheet As Excel.Worksheet
    Dim xlRange As Excel.Range
    Dim xlFormulaRange As Excel.Range

    Dim mFormula As New Scripting.Dictionary
    Dim lHSRow As Long
    Dim lHERow As Long

    Set xlSheet = ThisWorkbook.Worksheets(SHEETNAME_SUMMARY)
    ' GET ROW
    Set xlRange = xlSheet.Range(DECISION_COLUMN)
    For Each xlFormulaRange In xlRange.SpecialCells(xlCellTypeFormulas)
    mFormula.Add Key:=xlFormulaRange.Formula, Item:=xlFormulaRange.Row
    Next xlFormulaRange
    Set xlFormulaRange = Nothing
    Set xlRange = Nothing

    ' INIT
    lHSRow = mFormula(Replace(USER_FORMULA_1, "%rownumber%", "2")) - 1
    lHERow = mFormula(Replace(USER_FORMULA_2, "%rownumber%", CStr(LAST_CHANNEL_NUM))) + 1
    xlSheet.Range(CStr(lHSRow) & ":" & CStr(lHERow - 1)).EntireRow.Hidden = False

    ' Channels=4:Exit
    If iChannels = 4 Then
    GoTo ExitSub:
    End If

    ' HIDDEN.1-CHANNEL1...CHANNEL4...
    With xlSheet
    lHSRow = mFormula(Replace(USER_FORMULA_1, "%rownumber%", CStr(iChannels + 1))) - 1
    lHERow = .Range(DECISION_COLUMN).Find(What:=DELIMITER_STRING).Row - 1
    .Range(CStr(lHSRow) & ":" & CStr(lHERow)).EntireRow.Hidden = True
    End With
    ' HIDDEN.2-TABLE:CHANNEL1...CHANNEL4...
    With xlSheet
    lHSRow = mFormula(Replace(USER_FORMULA_2, "%rownumber%", CStr(iChannels + 1)))
    lHERow = mFormula(Replace(USER_FORMULA_2, "%rownumber%", CStr(LAST_CHANNEL_NUM)))
    .Range(CStr(lHSRow) & ":" & CStr(lHERow)).EntireRow.Hidden = True
    End With

    ExitSub:
    Set mFormula = Nothing

    Set xlFormulaRange = Nothing
    Set xlRange = Nothing
    Set xlSheet = Nothing
    End Sub
    Attached Images Attached Images
    Last edited by 0darroch; 08-21-2016 at 11:49 PM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,041

    Re: Code for adding section broken - help!

    On the face of it, there is nothing wrong with the code - you can attach a workbook to a post if you want, to allow others to troubleshoot your code with all the variables set properly. You can remove any sensitive data before attaching your workbook, or you can try troubleshooting it yourself: place a break on the line you think is the issue, and see what values the variables lHSRow and lHERow have been assigned.
    Bernie Deitrick
    Excel MVP 2000-2010

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Adding currency broken in two columns?
    By Blue Hornet in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 07:05 PM
  2. [SOLVED] Adding currency broken in two columns?
    By lperisich in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  3. Adding currency broken in two columns?
    By lperisich in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  4. Adding currency broken in two columns?
    By Blue Hornet in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 04:05 AM
  5. Adding currency broken in two columns?
    By lperisich in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  6. [SOLVED] Adding currency broken in two columns?
    By lperisich in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. Adding currency broken in two columns?
    By lperisich in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  8. [SOLVED] Adding currency broken in two columns?
    By lperisich in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-04-2005, 11:05 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1