Results 1 to 2 of 2

Code for adding section broken - help!

Threaded View

  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.

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