Formula Problem

[isay]

Hi,

Please help me how to handle this thing..

I have this code to know the start and end used cell address:

RStart = Sheet1.Range("c1").End(xlDown).Address
REnd = Sheet1.Range("c65536").End(xlUp).Address

Because I intend to used this in my sum formula. In my program, I cannot identify how many records it will retrieve thats why i used the RStart and REnd code.

This is the sumbycolor function I used when I searched here at the forum:
Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double

Dim OK As Boolean
Dim RStart As Range
Dim REnd As Range
Dim RAddress As Range

'I just tried this to substitute with the cell range
RStart = Sheet1.Range("c1").End(xlDown).Address
REnd = Sheet1.Range("c65536").End(xlUp).Address
RAddress = Range(RStart, REnd)

Application.Volatile True
For Each RAddress In InRange.Cells
If OfText = True Then
OK = (RAddress.Font.ColorIndex = WhatColorIndex)
Else
OK = (RAddress.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(RAddress.Value) Then
SumByColor = SumByColor + RAddress.Value
End If
Next RAddress

End Function

Formula to put in my cell:
=sumbycolor(Raddress,37,false)


But when I tried to used this in my sum formula, it didn't work.
Please help me..

 

[GlennUK]

Can you give a bit more of a clue than "it didn't work" ... in what way did it not work? Did it give you a different number to the one you expected? Did it give an error? Did it give zero?

Describe what the contents of C1 C2 C3 are? What do you expect Sheet1.Range("c1").End(xlDown).Address to point at?

 

[Leonard of Quirm]

Isay,
You can't make a function work this way.
In your cell, you provided:
=SumByColor(RAddress,37,False)

If it tries to run the function, it doesn't yet know what RAddress is - it doesn't get to know that until the function runs.
You need to make sure that when you run the function, the function knows everything it needs.

You could run some code to make this work, with some adjustments:

Dim OK As Boolean
Dim RStart As String
Dim REnd As String
Dim RAddress As Range

Sub Test()

RStart = Sheets("Sheet1").Range("c1").End(xlDown).Address
REnd = Sheets("Sheet1").Range("c65536").End(xlUp).Address
Set RAddress = Range(RStart & ":" & REnd)

x = SumByColor(RAddress, 37, False)
MsgBox (x)

End Sub

Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double


'I just tried this to substitute with the cell range

Application.Volatile True
For Each RAddress In InRange.Cells
If OfText = True Then
OK = (RAddress.Font.ColorIndex = WhatColorIndex)
Else
OK = (RAddress.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(RAddress.Value) Then
SumByColor = SumByColor + RAddress.Value
End If
Next RAddress

End Function

Alternatively, you should change your function like this:

Dim OK As Boolean
Dim RStart As String
Dim REnd As String
Dim RAddress As Range

Public Function SumByColor(WhatColorIndex As Integer, Optional OfText As Boolean = False) As Double

RStart = Sheets("Sheet1").Range("c1").End(xlDown).Address
REnd = Sheets("Sheet1").Range("c65536").End(xlUp).Address
Set RAddress = Range(RStart & ":" & REnd)

'I just tried this to substitute with the cell range

SumByColor = 0
Application.Volatile True
For Each RAddress In RAddress.Cells
    If OfText = True Then
        OK = (RAddress.Font.ColorIndex = WhatColorIndex)
    Else
        OK = (RAddress.Interior.ColorIndex = WhatColorIndex)
    End If

    If OK And IsNumeric(RAddress.Value) Then
        SumByColor = SumByColor + RAddress.Value
    End If
Next

End Function

Also, be careful how you choose RStart and REnd. The method you have chosen will depend on how your data is organised (e.g. is it continuous). You might do better to change your function so that you specify the starting cell (you'll need to include this as a variable when calling the function).

Hope these options help you out a bit more,

Matt

 

[isay]

Hi Glen,
The content of my cells are all number and it gives me an error "a value used in a formula is of the wrong datatype"....


Hi Matt,

Thanks... but I tried your code then when I run it, it gives me a Compile error: Byref argument type mismatch. The eroor highlighted the variable RAddress.

Please advise.

 

[GlennUK]

The error is due to the error that Matt spotted.

The contents of your cell are all number???? In that case I ask again:

What do you expect Sheet1.Range("c1").End(xlDown).Address to point at?

 

[isay]

Book5.xls
	A	B	C	D
6	A	$ 446,507.93	$ 446,507.93	#VALUE!
7		$ 7,434.78	$ 7,434.78
8		$(2,853,793.50)	$(2,853,793.50)
9		$ -	$ -
10		$ 938.81	$ 938.81
11		$ 11.22	$ 11.22
12		$ -	$ -
13		$ 7,242.65	$ 7,242.65
14		$ 39,135.00	$ 39,135.00
15		$ 7,242.65	$ 7,242.65
16		$ 7,242.65	$ 7,242.65
Sheet1

Hi Glenn/Matt,

My sample output is above. I tried putting Byref in the Sub Test, it already give the first and last row used in Col C but the the sum function didn't work.

I used the code below. Please advise.


Sub Test(ByRef RAddress As Range)

'Dim OK As Boolean
Dim RStart As String
Dim REnd As String
'Dim RAddress As Range
Dim x As Range

RStart = Sheets("Sheet1").Range("c1").End(xlDown).Address
REnd = Sheets("Sheet1").Range("c65536").End(xlUp).Address
Set RAddress = Range(RStart & ":" & REnd)

x = SumByColor(RAddress, 37, False)

MsgBox RStart
MsgBox REnd
MsgBox x

End Sub
Public Function SumByColor(WhatColorIndex As Integer, Optional OfText As Boolean = False) As Double

Dim OK As Boolean
Dim RStart As String
Dim REnd As String
Dim RAddress As Range

RStart = Sheets("Sheet1").Range("c1").End(xlDown).Address
REnd = Sheets("Sheet1").Range("c65536").End(xlUp).Address
Set RAddress = Range(RStart & ":" & REnd)

SumByColor = 0
Application.Volatile True
For Each RAddress In RAddress.Cells
If OfText = True Then
OK = (RAddress.Font.ColorIndex = WhatColorIndex)
Else
OK = (RAddress.Interior.ColorIndex = WhatColorIndex)
End If

If OK And IsNumeric(RAddress.Value) Then
SumByColor = SumByColor + RAddress.Value
End If
Next

End Function

 

[GlennUK]

What do you mean "it already give the first and last row used in Col C" ? You code does not do that! Please read Matt's post regarding your arguments.

 

[Leonard of Quirm]

Isay,
You only need the ByRef when calling Test if you are calling it from another procedure. Is that what's happening here?

Also, you've changed the function to be:

Public Function SumByColor(WhatColorIndex As Integer, Optional OfText As Boolean = False) As Double

This means you should be calling it like this:

x = SumByColor(37, False)

There's no need to find RStart, REnd and RAddress in the Test Sub - they are found by the function anyway!

You need to make sure you understand how to call functions and subs if they have variables specified when calling. Make sure when you call it, you have specified the correct number of variables (as above) and make sure you have them in the right order.
See the VBA help files for more guidance on this.

Matt

 

[isay]

Thanks alot.... I already figured it out. Its now working. I am so sorry for I am new for this kind of work. Thank you so much for the help and most of all for the patience to teach.