# Formula Problem

#### isay

##### New Member
Hi,

I have this code to know the start and end used cell 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

'I just tried this to substitute with the cell range

Application.Volatile True
If OfText = True Then
Else
End If
End If

End Function

Formula to put in my cell:

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

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### GlennUK

##### Well-known Member
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

##### Board Regular
Isay,
You can't make a function work this way.

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:
Code:
``````Dim OK As Boolean
Dim RStart As String
Dim REnd As String

Sub Test()

Set RAddress = Range(RStart & ":" & REnd)

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
If OfText = True Then
Else
End If
End If

End Function``````

Alternatively, you should change your function like this:
Code:
``````Dim OK As Boolean
Dim RStart As String
Dim REnd As String

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

Set RAddress = Range(RStart & ":" & REnd)

'I just tried this to substitute with the cell range

SumByColor = 0
Application.Volatile True
If OfText = True Then
Else
End If

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).

Matt

#### isay

##### New Member
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.

#### GlennUK

##### Well-known Member

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

##### New Member
Book5.xls
ABCD
6A\$ 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.

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

Set RAddress = Range(RStart & ":" & REnd)

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

Set RAddress = Range(RStart & ":" & REnd)

SumByColor = 0
Application.Volatile True
If OfText = True Then
Else
End If

End If
Next

End Function

#### GlennUK

##### Well-known Member

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

##### Board Regular
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

##### New Member
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.

Replies
3
Views
177
Replies
3
Views
57
Replies
2
Views
254
Replies
2
Views
138
Replies
3
Views
74