Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Selecting a Variable Column Range

  1. #1
    Join Date
    21st June 2006
    Posts
    3

    Selecting a Variable Column Range

    Good Day,

    I'll do my best to explain the issue, but please forgive me as i am a VBA newbie with some inherited code soo please be gentle : D . I have a workbook that generates sheets for each year based on selected criteria. It starts at Column H and goes too AH and beyond. When my loop reaches Z it errors out. I think this is happening because the code is referencing the column as ASCII. Here is the code:

    VB:
    Sub Test() 
        Dim d As Date 
        Dim yrint, i, num_years, fields, field_start As Integer 
        Dim yrstr, crit1, crit2, left_column_range_fixed, right_column_range_fixed, left_column_range_var, right_column_range_var, left_column_range, right_column_range, cost_column, cost_column_var, cost_column_fixed As String 
        left_column_range_fixed = "H" 
        right_column_range_fixed = ":AH" 
        cost_column_fixed = "2" 
        crit1 = "=x" 
        crit2 = ">0" 
        d = Date 
        yrint = Sheets("Overall").Range("H2") 
        field_start = 9 'changed from 9
        num_years = InputBox("Enter the number of years") 'inputs the # of years that the macro will create sheets for
        For i = 0 To num_years 'loop begins
            yrstr = CStr(yrint + i) 'changes the year to i + the year in a string
            fields = field_start + i 'sets which rows will be filtered
            left_column_range_var = Chr(Asc("h") + i) 'sets the variable column range for the leftmost columns
            right_column_range_var = Chr(Asc("j") + i) 'sets the variable column range for the rightmost columns
            cost_column_var = Chr(Asc("i") + i) 'sets the variable column range for the cost column
            Sheets("Overall").Select ' this and the next 4 lines create the next years' sheet
            Sheets("Overall").Copy After:=Sheets(i + 1) 
            Sheets("Overall (2)").Select 
            Sheets("Overall (2)").Name = yrstr 'sets the name to the appropriate year
            Selection.AutoFilter Field:=fields, Criteria1:=crit1, Operator:=xlOr, Criteria2:=crit2 'selects the rows to be shown based on whether they have an x or int value in them
            Range("A1:B1").Select 'this and the next line set the sheet title to the correct year
            ActiveCell.FormulaR1C1 = "Tasks to be Completed - " + yrstr 
            Columns("D:E").Select 'hides the task type, time frame, and priority columns
            Selection.EntireColumn.Hidden = True 
            left_column_range = left_column_range_fixed + left_column_range_var 'sets which columns will be hidden
            Columns(left_column_range).Select ' this selects the leftmost column(s) to be hidden
            Selection.EntireColumn.Hidden = True 
            right_column_range = right_column_range_var + right_column_range_fixed 'sets which columns will be hidden
            Columns(right_column_range).Select 'this selects the rightmost column(s) to be hidden
            Selection.EntireColumn.Hidden = True 
            cost_column = cost_column_var + cost_column_fixed + ":" + cost_column_var + cost_column_fixed 'selects the correct column to put the cost formula
            Range(cost_column).Select 
            ActiveCell.FormulaR1C1 = "Cost" 'copies the formula to the correct cell, and names it "Cost"
            ActiveWindow.View = xlPageBreakPreview 'sets the page preview view for the sheet
        Next i 'loop ends
        Worksheets.FillAcrossSheets (Worksheets("Overall").Range("A2:B99")) 'a workaround for the 255 character limit of sheets(x).copy.  Its ugly but it works :-/
        Sheets("Overall").Select 'sets the selected worksheet back to Overall
    End Sub 
    
    
    Thanks in advance for your help

    Terry

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    2,597

    Re: Selecting a Variable Column Range

    Hi Terry,

    Welcome to the OzGrid forum.

    I haven't tried to figure out exactly what your code is trying to do, but perhaps some code along these lines might help, assuming that the variables are numeric:
    VB:
    Range(Cells(1, left_column_range), Cells(1, right_column_range)).EntireColumn.Hidden = True 
    
    
    Hope this helps.
    Regards,
    Batman.

  3. #3
    Join Date
    21st June 2006
    Posts
    3

    Re: Selecting a Variable Column Range

    Sorry i'll try to explain in more detail

    Basically what happens is that each row item contains detail data that is to be used only on specific years. What happens is that from Columns H too AH and beyond are each labled as a Year (H2 being 2006, I2 being 2007 , etc). A user will place an X on the specific cell where the row is required for that particular year. What hte macro does is go over each year (starting with H) and autofilters based on cells that have "x" in them. It copies the autofiltered rows into a new sheet (named for the year) and then hides the columns that do not have anything to do with that year. The issue in question has to do with the following:

    This code selects the columns which vary for each loop (taken from the code in the original post)
    VB:
    left_column_range_var = Chr(Asc("h") + i) 'sets the variable column range for the leftmost columns
    right_column_range_var = Chr(Asc("j") + i) 'sets the variable column range for the rightmost columns
    
    
    And then it hides the columns selected
    VB:
    left_column_range = left_column_range_fixed + left_column_range_var 'sets which columns will be hidden
    Columns(left_column_range).Select ' this selects the leftmost column(s) to be hidden
    Selection.EntireColumn.Hidden = True 
    right_column_range = right_column_range_var + right_column_range_fixed 'sets which columns will be hidden
    Columns(right_column_range).Select 'this selects the rightmost column(s) to be hidden
    Selection.EntireColumn.Hidden = True 
    
    
    When i run this macro for 30 years (30 loops) it gets an error when it tries to hide columns too the right of Z, getting a Run-time error '13', Type mismatch. I'm assuming this is because it is using ASCII too identify which columns to hide ... but i'm not sure because i'm a total newbie at this.

    I'll try your suggestion though ... just thought i'd explain it in a little more detail

    Thanks

    Terry

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    21st June 2006
    Posts
    3

    Re: Selecting a Variable Column Range

    Bump

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    2,597

    Re: Selecting a Variable Column Range

    Terry,

    The reason your macro is erroring out after column Z is that the character number 1 greater than Z is not AA. As I alluded to earlier, try using column numbers rather than letters. Also, I would suggest avoiding selecting objects in order to take action using the Selection object. You can do the same thing, only better, using the original object. E.g.
    VB:
    Columns("D").Select 
    Selection.EntireColumn.Hidden = True 
    
    
    can be replaced by
    VB:
    Columns("D").Hidden = True 
     'or
    Columns(4).Hidden = True 
    
    
    Using numbers for columns you will not have the problem of erroring when you go past Z.

    However, to refer to a range of columns by number to hide them you need to use the code I gave you earlier, i.e.
    VB:
    Range(Cells(1, left_column_range), Cells(1, right_column_range)).EntireColumn.Hidden = True 
    
    
    but this assumes that the variables left_column_range and right-column_range are numeric and not strings.

    You should be aware that your coding of the declarations only declares the last item in each declaration statement as the variable type you define. You need to type 'As Integer' (or whatever) after each variable. Otherwise the variable is declared as the default type of Variant.

    Hope this helps.
    Regards,
    Batman.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Selecting a variable range and then subtotaling it
    By minimeme in forum EXCEL HELP
    Replies: 3
    Last Post: October 25th, 2006, 00:37
  2. Replies: 1
    Last Post: June 17th, 2004, 21:13
  3. Replies: 2
    Last Post: January 13th, 2004, 00:40
  4. [Solved] Selecting variable range for formulas
    By dazed in forum EXCEL HELP
    Replies: 8
    Last Post: April 7th, 2003, 06:13
  5. selecting variable range array of cells
    By 21MSU in forum EXCEL HELP
    Replies: 3
    Last Post: April 4th, 2003, 05:09

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
  •  
porno