1. ## 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. ## 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. ## Re: Calculation help (2000)

Thanks Hans,

Correct on the Edition Main

Darryl

4. ## 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>

5. ## 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.

6. ## 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. ## 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. ## 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
•