+ Reply to Thread
Results 1 to 4 of 4

Why 'object required' error from this code?

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-04-2013
    Location
    Europe
    MS-Off Ver
    Office 365 ProPlus
    Posts
    147

    Why 'object required' error from this code?

    Hi when running this code while the first check box "cb_crude" IS NOT ticked i get a "object required" error from the "if not usePrices is nothing then" line. Do I have to define usePrices as "" to make it an object? It's weird because the same code worked for an example workbook..

    Sub updatebutton()
        'define variables
        Dim wb As Workbook
        Dim presentation, prices_sheet, volumes_sheet, pavEvaluated As Worksheet
        Dim cb_crude, cb_fuel, cb_gas, cb_dest As CheckBox
        Dim allPrices, usePrices, allVolumes, useVolumes As Range
        
        'set workbook and worksheets
        Set wb = ThisWorkbook
        Set presentation = wb.Sheets("Presentation")
        Set prices_sheet = wb.Sheets("Prices")
        Set volumes_sheet = wb.Sheets("Volumes")
        Set pavEvaluated = wb.Sheets("Prices and Volumes evaluated")
       
        'set check boxes
        Set cb_crude = presentation.CheckBoxes("Check Box Crude")
        Set cb_fuel = presentation.CheckBoxes("Check Box Fuel")
        Set cb_gas = presentation.CheckBoxes("Check Box Gas")
        Set cb_dest = presentation.CheckBoxes("Check Box Dest")
        
        'set ranges
        Set allPrices = prices_sheet.Range("A1").CurrentRegion
        Set allPrices = allPrices.Offset(4, 1).Resize(allPrices.Rows.Count - 4, allPrices.Columns.Count - 1)
        Set allVolumes = volumes_sheet.Range("A1").CurrentRegion
        Set allVolumes = allVolumes.Offset(2, 2).Resize(allVolumes.Rows.Count - 2, allVolumes.Columns.Count - 2)
        
        'Set what happens when the check boxes are ticked
        If cb_crude.Value = 1 Then
            Set usePrices = allPrices.Columns("A:O")
            Set useVolumes = allVolumes.Columns("A:O")
        End If
        If cb_fuel.Value = 1 Then
            If Not usePrices Is Nothing Then
                Set usePrices = Union(usePrices, allPrices.Columns("P:X"))
                Set useVolumes = Union(useVolumes, allVolumes.Columns("P:X"))
            Else
                Set usePrices = allPrices.Columns("P:X")
                Set useVolumes = allVolumes.Columns("P:X")
            End If
        End If
        If cb_gas.Value = 1 Then
            If Not usePrices Is Nothing Then
                Set usePrices = Union(usePrices, allPrices.Columns("Y:DA"))
                Set useVolumes = Union(useVolumes, allVolumes.Columns("Y:DA"))
            Else
                Set usePrices = allPrices.Columns("Y:CZ")
                Set useVolumes = allVolumes.Columns("Y:CZ")
            End If
        End If
        If cb_dest.Value = 1 Then
            If Not usePrices Is Nothing Then
                Set usePrices = Union(usePrices, allPrices.Columns("DA:DC"))
                Set useVolumes = Union(useVolumes, allVolumes.Columns("DA:DC"))
            Else
                Set usePrices = allPrices.Columns("DA:DC")
                Set useVolumes = allVolumes.Columns("DA:DC")
            End If
        End If
        
        If Not usePrices Is Nothing Then
            usePrices.Copy pavEvaluated.Range("A1")
            useVolumes.Copy pavEvaluated.Cells(Rows.Count, "A").End(xlUp).Offset(2, 0)
        End If
    End Sub

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Why 'object required' error from this code?

    usePrices is declared as a Variant not an object type
    change
    Dim allPrices, usePrices, allVolumes, useVolumes As Range
    to
    Dim allPrices As Range, usePrices As Range, allVolumes As Range, useVolumes As Range
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Forum Contributor
    Join Date
    03-04-2013
    Location
    Europe
    MS-Off Ver
    Office 365 ProPlus
    Posts
    147

    Re: Why 'object required' error from this code?

    Quote Originally Posted by JosephP View Post
    usePrices is declared as a Variant not an object type
    change
    Dim allPrices, usePrices, allVolumes, useVolumes As Range
    to
    Dim allPrices As Range, usePrices As Range, allVolumes As Range, useVolumes As Range
    Thank you so much! Would have taken me hours to figure out!

  4. #4
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Why 'object required' error from this code?

    You have only allocated the memory for them with your DIM there is nothing passed unless that check box is ticked. After the dim set them all to be nothing, so Set UsePrices=Nothing

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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