Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Calculation help (2000)

    Hi all,

    I have a sub that isn't working the way that I would like, can someone take a look at it to tell me if my structure is O.K.

    Public Sub SubsetCount()
    Dim X As Integer 'Plate count for edition subset
    Dim Y As Integer 'Film count for edition subset
    On Error Resume Next
    X = CBC(Sheets("Edition_Subset").Range("A468"), 4, False)
    Y = CBC(Sheets("Edition_Subset").Range("E4:H68"), 4, False)
    Sheets("Edition_Attributes").Range("B22") = X * Sheets("Editon_Main").Range("AK1").Value
    Sheets("Edition_Attributes").Range("B11") = Y
    Sheets("Edition_Attributes").Range("B23") = Y
    End Sub

    The Problem is with the multiplication of range AK1, am I able to times the variable, by the value of AK1?

    Thanks, Darryl.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Calculation help (2000)

    Is there a typo in the name of the worksheet? I would think that Sheets("Editon_Main").Range("AK1") should be Sheets("Edition_Main").Range("AK1").

    Apart from that, you haven't told us what CBC is, so it is impossible to know what the code does.

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Calculation help (2000)

    Thanks Hans,

    Correct on the Edition Main

    Darryl

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculation help (2000)

    You get 4 types of errors:
    Syntax errors, spoted on a line by line basis by VBA
    Compiler errors, spotted by VBA when code is compiled
    Runtime errors, that work sometimes and fail at other occations, at runtime.
    Then you get:
    Logical errors, things like spelling and human volitility!!! These are that worst type of error as it just ain't easy to find and fix unless you have the <img src=/S/hansv.gif border=0 alt=HansV width=27 height=26> factor!!!!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation help (2000)

    That error should have resulted in a "Subscript out of range" error. It would have been a big help diagnosing your problem if you had told us what occurred (Not just that you can't multiply), and what line it occurred on. Your message was kind of like me sending you a message: "I can't start my car, can you tell me what is wrong?", and attaching a picture of the car.
    Legare Coleman

  6. #6
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Calculation help (2000)

    Thanks Legare,

    I wanted to post Hans Correction, and also the function CBC - (count by color), "subscript out of range " How did you know that? You're right about the car, i'm guessing that subscrit out of range is the logical error, and one defined when I don't refer to an object(my spelling mistake)? Is that correct?

    Thanks,
    Darryl.





    Public Sub SubsetCount()
    Dim X As Integer 'Film count for edition subset
    Dim Y As Integer 'Plate count for edition subset
    Application.ScreenUpdating = False
    On Error Resume Next
    X = CBC(Sheets("Edition_Subset").Range("A468"), 4, False)
    Y = CBC(Sheets("Edition_Subset").Range("E4:H68"), 4, False) * Sheets("Edition_Main").Range("AK1").Value
    Sheets("Edition_Attributes").Range("B22") = X
    Sheets("Edition_Attributes").Range("B11") = X
    Sheets("Edition_Attributes").Range("B23") = Y
    Application.ScreenUpdating = True
    End Sub


    Function CBC(InRange As Range, WhatColorIndex As Integer, Optional OfText As Boolean = False) As Long
    '
    ' This function return the number of cells in InRange with
    ' a background color, or if OfText is True a font color,
    ' equal to WhatColorIndex.
    '
    Dim Rng As Range


    For Each Rng In InRange

    If OfText = True Then
    CBC = CBC - _
    (Rng.Font.ColorIndex = WhatColorIndex)
    Else
    CBC = CBC - _
    (Rng.Interior.ColorIndex = WhatColorIndex)
    End If
    Next Rng

    End Function

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Calculation help (2000)

    If your workbook has 5 sheets, the Sheets collection has 5 members, Sheets(1) to Sheets(5). The number (1...5) is called the index or the subscript. The term subscipt is from mathematics, where the members of an array are conventionally indicated by placing the index number subscripted (lowered) next to the name of the array. If you use a number that does not correspond to an existing member, such as Sheets(-3) or Sheets(37), you get an error message "Subscript out of range."

    It is also possible to refer to a member of the Sheets collection by its name: Sheets("Sheet1"), for example. If you use a non-existing name, you get the "Subscript out of range" error too, although strictly speaking, the name is not the same as the index/subscript.

  8. #8
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Calculation help (2000)

    Thanks once again

    I have another tool to add to my toolchest.

    Darryl.

Posting Permissions

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