resize(Ubound, Lbound)

hello,

I have a problem with Ubound and Lbound

I have this function

Function Client(code as integer) as variant

Dim r as integer, code as integer
Dim var(1 To 1000, 1 To 6) As Variant
Dim NextCell as range
r = 1

While code > 256

var(r, 1) = "salut"
var(r, 2) = "nom"
var(r, 3) = "prenom"
var(r, 4) = currentdate
var(r, 5) = "status"
var(r, 6) = "prof"
var(r, 7) = "OFF"
var(r, 8) = "COMMENTS"


r = r + 1


Wend

' In order to have always last first empty cell

Set NextCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)


NextCell.Resize(UBound(var, 1) - LBound(var, 1), UBound(var, 2) -
LBound(var, 2)).Value = var
' I have a problem here
End function


Could someone help me on that?

ina

This doesn't even begin to work for me, long before where you indicate a
problem.

You include code as a function argument, and then declare it as a variable.
No can do.

You use a variable not declared or initialised, currentDate.

You do a loop testing code for > 256 but never change code, so the loop goes
on forever, or actually until you overflow the array.

You define the array 1 To 1000, 1 To 6, then try to load var(r,7). No can
do.

You use . notation on non qualified objects, Set NextCell =
..Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0). no can do.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ina" <roberta.inalbon@gmail.com> wrote in message
news:1146477976.352308.280190@i40g2000cwc.googlegroups.com...
> hello,
>
> I have a problem with Ubound and Lbound
>
> I have this function
>
> Function Client(code as integer) as variant
>
> Dim r as integer, code as integer
> Dim var(1 To 1000, 1 To 6) As Variant
> Dim NextCell as range
> r = 1
>
> While code > 256
>
> var(r, 1) = "salut"
> var(r, 2) = "nom"
> var(r, 3) = "prenom"
> var(r, 4) = currentdate
> var(r, 5) = "status"
> var(r, 6) = "prof"
> var(r, 7) = "OFF"
> var(r, 8) = "COMMENTS"
>
>
> r = r + 1
>
>
> Wend
>
> ' In order to have always last first empty cell
>
> Set NextCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
>
>
> NextCell.Resize(UBound(var, 1) - LBound(var, 1), UBound(var, 2) -
> LBound(var, 2)).Value = var
> ' I have a problem here
> End function
>
>
> Could someone help me on that?
>
> ina
>

I am sorry the code comes from a query (and it is in another sub) and
the current date I forgot this "" the problem was the size of my array
because I add more two values (r,7) and (r,8) and forgot to resize it
to Dim var(1 To 1000, 1 To 9) As Variant


Now it is working fine.

Thanks for your answer and observations. I will pay more attention when
I copy my code here.

Ina

rNonetheless, you assignment should be

NextCell.Resize(UBound(var, 1) - LBound(var, 1) + 1, _
UBound(var, 2) -LBound(var, 2) + 1).Value = var

--
Regards,
Tom Ogilvy


"ina" wrote:

> I am sorry the code comes from a query (and it is in another sub) and
> the current date I forgot this "" the problem was the size of my array
> because I add more two values (r,7) and (r,8) and forgot to resize it
> to Dim var(1 To 1000, 1 To 9) As Variant
>
>
> Now it is working fine.
>
> Thanks for your answer and observations. I will pay more attention when
> I copy my code here.
>
> Ina
>
>

Thanks Tom. I will do it

Ina