# 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

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### 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
2
Views
96
Replies
1
Views
140
Replies
9
Views
156
Replies
1
Views
224
Replies
3
Views
141

1,171,065
Messages
5,873,585
Members
432,985
Latest member
leahw

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back