small fuction problem pass arguments
 

hello all,

I have one sub and one function; ma function need to calculate the
range from the activecell + size array.

this is the code;

Sub display_array()
Dim s(3, 3) As String
Dim arraysize As Integer
Dim rng As range

s(1, 1) = "A"
s(2, 1) = "B"
s(3, 1) = "C"

arraysize = 3

rng = selectRange(arraysize)


rng = s

End Sub


Function selectRange(size As Integer) As range
Dim arraysize, j, h As Integer
Dim newcolumn As String
Dim rng As range

arraysize = size

Dim addr As String
addr = ActiveCell.Address
j = ColRef2ColNo(addr)

h = j + arraysize

newcolumn = ColNo2ColRef(h)
range(ActiveCell, newcolumn + CStr(10)).Select

selectRange = rgn


End Function


I have a problem because my function is empty and I do not understand
why. Can someone help me on that?

Ina

small fuction problem pass arguments
 

You don't supply the sub ColNo2ColRef, but this line

rng = selectRange(arraysize)


should at the least be

Set rng = selectRange(arraysize)

You use

j = ColRef2ColNo(addr)

and

newcolumn = ColNo2ColRef(h)

which are different names, and then

selectRange = rgn

which is an undeclared variable and should be

Set selectRange = rng

You need to start to learn to use Option Explicit at the start of your
modules.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ina" wrote in message
oups.com...
hello all,

I have one sub and one function; ma function need to calculate the
range from the activecell + size array.

this is the code;

Sub display_array()
Dim s(3, 3) As String
Dim arraysize As Integer
Dim rng As range

s(1, 1) = "A"
s(2, 1) = "B"
s(3, 1) = "C"

arraysize = 3

rng = selectRange(arraysize)


rng = s

End Sub


Function selectRange(size As Integer) As range
Dim arraysize, j, h As Integer
Dim newcolumn As String
Dim rng As range

arraysize = size

Dim addr As String
addr = ActiveCell.Address
j = ColRef2ColNo(addr)

h = j + arraysize

newcolumn = ColNo2ColRef(h)
range(ActiveCell, newcolumn + CStr(10)).Select

selectRange = rgn


End Function


I have a problem because my function is empty and I do not understand
why. Can someone help me on that?

Ina

small fuction problem pass arguments
 

Thank you Bob for this explanamtion the colno2colref and colref2colino
are function that transform the column number in a column letter.

Function ColRef2ColNo(ColRef As String) As Integer
ColRef2ColNo = 0
On Error Resume Next
ColRef2ColNo = range(ColRef & "1").column
End Function

Function ColNo2ColRef(ColNo As Integer) As String
If ColNo < 1 Or ColNo 256 Then
ColNo2ColRef = "#VALUE!"
Exit Function
End If
ColNo2ColRef = Cells(1, ColNo).Address(True, False, xlA1)
ColNo2ColRef = Left(ColNo2ColRef, InStr(1, ColNo2ColRef, "$") - 1)
End Function


But I have a problem with my code I cannot do

rgn = s

why?

ina

Bob Phillips wrote:
You don't supply the sub ColNo2ColRef, but this line

rng = selectRange(arraysize)


should at the least be

Set rng = selectRange(arraysize)

You use

j = ColRef2ColNo(addr)

and

newcolumn = ColNo2ColRef(h)

which are different names, and then

selectRange = rgn

which is an undeclared variable and should be

Set selectRange = rng

You need to start to learn to use Option Explicit at the start of your
modules.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ina" wrote in message
oups.com...
hello all,

I have one sub and one function; ma function need to calculate the
range from the activecell + size array.

this is the code;

Sub display_array()
Dim s(3, 3) As String
Dim arraysize As Integer
Dim rng As range

s(1, 1) = "A"
s(2, 1) = "B"
s(3, 1) = "C"

arraysize = 3

rng = selectRange(arraysize)


rng = s

End Sub


Function selectRange(size As Integer) As range
Dim arraysize, j, h As Integer
Dim newcolumn As String
Dim rng As range

arraysize = size

Dim addr As String
addr = ActiveCell.Address
j = ColRef2ColNo(addr)

h = j + arraysize

newcolumn = ColNo2ColRef(h)
range(ActiveCell, newcolumn + CStr(10)).Select

selectRange = rgn


End Function


I have a problem because my function is empty and I do not understand
why. Can someone help me on that?

Ina

small fuction problem pass arguments
 

You need to declare the array as 1 based

Dim s(1 To 3, 1 To 3) As String

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ina" wrote in message
ups.com...
Thank you Bob for this explanamtion the colno2colref and colref2colino
are function that transform the column number in a column letter.

Function ColRef2ColNo(ColRef As String) As Integer
ColRef2ColNo = 0
On Error Resume Next
ColRef2ColNo = range(ColRef & "1").column
End Function

Function ColNo2ColRef(ColNo As Integer) As String
If ColNo < 1 Or ColNo 256 Then
ColNo2ColRef = "#VALUE!"
Exit Function
End If
ColNo2ColRef = Cells(1, ColNo).Address(True, False, xlA1)
ColNo2ColRef = Left(ColNo2ColRef, InStr(1, ColNo2ColRef, "$") - 1)
End Function


But I have a problem with my code I cannot do

rgn = s

why?

ina

Bob Phillips wrote:
You don't supply the sub ColNo2ColRef, but this line

rng = selectRange(arraysize)


should at the least be

Set rng = selectRange(arraysize)

You use

j = ColRef2ColNo(addr)

and

newcolumn = ColNo2ColRef(h)

which are different names, and then

selectRange = rgn

which is an undeclared variable and should be

Set selectRange = rng

You need to start to learn to use Option Explicit at the start of your
modules.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ina" wrote in message
oups.com...
hello all,

I have one sub and one function; ma function need to calculate the
range from the activecell + size array.

this is the code;

Sub display_array()
Dim s(3, 3) As String
Dim arraysize As Integer
Dim rng As range

s(1, 1) = "A"
s(2, 1) = "B"
s(3, 1) = "C"

arraysize = 3

rng = selectRange(arraysize)


rng = s

End Sub


Function selectRange(size As Integer) As range
Dim arraysize, j, h As Integer
Dim newcolumn As String
Dim rng As range

arraysize = size

Dim addr As String
addr = ActiveCell.Address
j = ColRef2ColNo(addr)

h = j + arraysize

newcolumn = ColNo2ColRef(h)
range(ActiveCell, newcolumn + CStr(10)).Select

selectRange = rgn


End Function


I have a problem because my function is empty and I do not understand
why. Can someone help me on that?

Ina