type mismatch
 

I am a beginner in programming VBA

I want to write a function that returns an array where each element is
increased by 1.

However, something is not working. Could you please help me?

Function b(r As Range)
Dim i As Integer, j As Integer
b = r
For i = 1 To UBound(b, 1)
For j = 1 To UBound(b, 2)
b(i, j) = b(i, j) + 1
Next j
Next i
End Function

For example,

............A..........B
1.........1...........2
2.........2...........3
3.........3...........4

In this case, B1:B3 contains the formula =b(A1:a3) entered with
Ctrl-Shift-Enter

Thank you

type mismatch
 

Why not select B1:B3 and enter the following

= (select A1:A3) + 3

Commit with CTRL SHIFT ENTER.

"vsoler" wrote:

I am a beginner in programming VBA

I want to write a function that returns an array where each element is
increased by 1.

However, something is not working. Could you please help me?

Function b(r As Range)
Dim i As Integer, j As Integer
b = r
For i = 1 To UBound(b, 1)
For j = 1 To UBound(b, 2)
b(i, j) = b(i, j) + 1
Next j
Next i
End Function

For example,

............A..........B
1.........1...........2
2.........2...........3
3.........3...........4

In this case, B1:B3 contains the formula =b(A1:a3) entered with
Ctrl-Shift-Enter

Thank you

type mismatch
 

On 10 jun, 01:34, Barb Reinhardt
wrote:
Why not select B1:B3 and enter the following

= (select A1:A3) + 3

Commit with CTRL SHIFT ENTER.



"vsoler" wrote:
I am a beginner in programming VBA

I want to write a function that returns an array where each element is
increased by 1.

However, something is not working. Could you please help me?

Function b(r As Range)
Dim i As Integer, j As Integer
b = r
For i = 1 To UBound(b, 1)
For j = 1 To UBound(b, 2)
b(i, j) = b(i, j) + 1
Next j
Next i
End Function

For example,

............A..........B
1.........1...........2
2.........2...........3
3.........3...........4

In this case, B1:B3 contains the formula =b(A1:a3) entered with
Ctrl-Shift-Enter

Thank you- Ocultar texto de la cita -

- Mostrar texto de la cita -

Barb,

The solution you propose does not work in my Excel 2003. I get a value
of 3 in all my 3 cells.

I'd rather have a function, because I need to preserve the original
values and put the result of the calculation just side by side.

Can anybody help?

type mismatch
 

Hi V,

Barb's suggestion works for me, provided that,
as suggested, you confirm the formula with the
key combination

CTRL-SHIFT-ENTER.

rather than Enter.

For your purposes. however, I think that you
should change Barb's +3 to +1.

To utlise your function, try the following
adaptation:

'=============
Function b(r As Range) As Variant
Dim i As Long, j As Long
Dim arr As Variant
arr = r.Value
For i = 1 To UBound(arr, 1)
For j = 1 To UBound(arr, 2)
arr(i, j) = arr(i, j) + 1
Next j
Next i
b = arr
End Function
'<<=============


---
Regards,
Norman


"vsoler" wrote in message
ps.com...
On 10 jun, 01:34, Barb Reinhardt
wrote:
Why not select B1:B3 and enter the following

= (select A1:A3) + 3

Commit with CTRL SHIFT ENTER.



"vsoler" wrote:
I am a beginner in programming VBA

I want to write a function that returns an array where each element is
increased by 1.

However, something is not working. Could you please help me?

Function b(r As Range)
Dim i As Integer, j As Integer
b = r
For i = 1 To UBound(b, 1)
For j = 1 To UBound(b, 2)
b(i, j) = b(i, j) + 1
Next j
Next i
End Function

For example,

............A..........B
1.........1...........2
2.........2...........3
3.........3...........4

In this case, B1:B3 contains the formula =b(A1:a3) entered with
Ctrl-Shift-Enter

Thank you- Ocultar texto de la cita -

- Mostrar texto de la cita -

Barb,

The solution you propose does not work in my Excel 2003. I get a value
of 3 in all my 3 cells.

I'd rather have a function, because I need to preserve the original
values and put the result of the calculation just side by side.

Can anybody help?

type mismatch
 

On 10 jun, 09:36, "Norman Jones"
wrote:
Hi V,

Barb's suggestion works for me, provided that,
as suggested, you confirm the formula with the
key combination

CTRL-SHIFT-ENTER.

rather than Enter.

For your purposes. however, I think that you
should change Barb's +3 to +1.

To utlise your function, try the following
adaptation:

'=============
Function b(r As Range) As Variant
Dim i As Long, j As Long
Dim arr As Variant
arr = r.Value
For i = 1 To UBound(arr, 1)
For j = 1 To UBound(arr, 2)
arr(i, j) = arr(i, j) + 1
Next j
Next i
b = arr
End Function
'<<=============

---
Regards,
Norman

"vsoler" wrote in message

ps.com...



On 10 jun, 01:34, Barb Reinhardt
wrote:
Why not select B1:B3 and enter the following

= (select A1:A3) + 3

Commit with CTRL SHIFT ENTER.

"vsoler" wrote:
I am a beginner in programming VBA

I want to write a function that returns an array where each element is
increased by 1.

However, something is not working. Could you please help me?

Function b(r As Range)
Dim i As Integer, j As Integer
b = r
For i = 1 To UBound(b, 1)
For j = 1 To UBound(b, 2)
b(i, j) = b(i, j) + 1
Next j
Next i
End Function

For example,

............A..........B
1.........1...........2
2.........2...........3
3.........3...........4

In this case, B1:B3 contains the formula =b(A1:a3) entered with
Ctrl-Shift-Enter

Thank you- Ocultar texto de la cita -

- Mostrar texto de la cita -

Barb,

The solution you propose does not work in my Excel 2003. I get a value
of 3 in all my 3 cells.

I'd rather have a function, because I need to preserve the original
values and put the result of the calculation just side by side.

Can anybody help?- Ocultar texto de la cita -

- Mostrar texto de la cita -

Norman,

It works fine, many thanks.

However, I cannot still understand why I have to use an intermediate
variant array "arr" instead of using directly the function name "b",
since both of them are variant. Where is the difference between the
two?

Thanks again

type mismatch
 

Hi V,

'---------------
It works fine, many thanks.

However, I cannot still understand why I have to use an intermediate
variant array "arr" instead of using directly the function name "b",
since both of them are variant. Where is the difference between the
two?
'---------------

You have named your function as b and the
function expects a single range argument.

Therefore, attempting to pass multiple Integer
(or Long) values in the line:

b(i, j) = b(i, j) + 1

will cause the encountered problem.


---
Regards,
Norman


Norman,

It works fine, many thanks.

However, I cannot still understand why I have to use an intermediate
variant array "arr" instead of using directly the function name "b",
since both of them are variant. Where is the difference between the
two?

Thanks again