resize(Ubound, Lbound)

[ina]

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

 

[Bob Phillips]

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]

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

 

[Tom Ogilvy]

rNonetheless, you assignment should be

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

 

[ina]

Thanks Tom. I will do it

Ina