Set a range = to array in different but open workbook

[dhusband]

Hi guys I can't figure this one out. (I'm a novice) I really want to learn to use arrays to and from ranges because it will be much faster than the looping methods I currently use. I searched the forum but see nothing exactly like my problem. The code I pasted is part of one of my procedures and the last line generates a "Run Time error 1004... Application defined or Object defined error". I did some test code with a range format of "A2:D2" and it works, writing perfectly where I direct it. However I need to access the range with cell variable references and that code doesn't work. You'll see below. I'm going nuts with this one. Most of the time it's something silly that I did and can figure it out. When I finally get this thing working how I want, it will manipulate large amounts of data and speed will be critical. Please help.

Dave H.



Public Sub VTest()


Dim RcInfoArr() As Variant
Dim I As Integer
Dim X As Integer



ReDim RcInfoArr(3)

For X = 0 To 3
RcInfoArr(X) = Cells(X + 1, "C").Value
Next X

I = 0

Do
I = I + 1
Loop Until Workbooks("Stats").Worksheets("PO1sts").Cells(I, "A").Value = ""

'The line below works fine

Workbooks("Stats").Sheets("PO1sts").Range("A2:D2").Value = RcInfoArr

X = 0
'This line doesn't
Workbooks("Stats").Worksheets("PO1sts").Range(Cells(I, X + 1), Cells(I, X + 4)).Value = RcInfoArr

 

[patel45]

your code works on my test sheet, need sample files.
you can find the last not empy row in column A with

I = Workbooks("Stats").Sheets("PO1sts").Cells(Rows.Count, "A").End(xlUp).Row + 1

 

[patel45]

try so

With Workbooks("Stats").Sheets("PO1sts")
  .Range(.Cells(I, X + 1), .Cells(I, X + 4)).Value = RcInfoArr
End With

ignore my previous post

 

[dhusband]

try so

With Workbooks("Stats").Sheets("PO1sts")
  .Range(.Cells(I, X + 1), .Cells(I, X + 4)).Value = RcInfoArr
End With

ignore my previous post

Cool Thanks I will use both your suggestions.
I assume that "rows.count" syntax can be used also as "Columns.count" ?
Anyway my code looks like this now

Dim RcInfoArr() As Variant
Dim I As Integer
Dim X As Integer
Dim WbStats As Workbook
Dim WSPO1sts As Worksheet

'Set WbStats = Worksbooks("Stats.xlsx")
'Set WSPO1sts = WbStats.Sheets("PO1sts")


ReDim RcInfoArr(3)

For X = 0 To 3
RcInfoArr(X) = Cells(X + 1, "C").Value
Next X

I = 0

'Do
'I = I + 1
'Loop Until WSPO1sts.Cells(I, "A").Value = ""
I = Workbooks("Stats").Sheets("PO1sts").Cells(Rows.Count, "A").End(xlUp).Row + 1
X = 0
With Workbooks("Stats").Sheets("PO1sts")
  .Range(.Cells(I, X + 1), .Cells(I, X + 4)).Value = RcInfoArr
End With

It works great Thanks! Last Night after Posting I continued to play with it and found that

 Workbooks("Stats").Worksheets("PO1sts").Range _
    (Workbooks("Stats").Worksheets("PO1sts").Cells(I, X + 1), _
    Workbooks("Stats").Worksheets("PO1sts").Cells(I, X + 4)).Value = RcInfoArr

Worked also... So VBA doesn't trust that the cell references point at the same WB.WS as the range reference
and the with statement shortcuts what I found. I began attempting to set my workbook.worksheet to an object
variable but I'm getting a compile error: sub or function not defined at the

'Set WbStats = Worksbooks("Stats.xlsx")

Line above. I've commented it out so I could run your suggestions. Whoops, maybe I'm overstepping I'll post that
as my next question sorry, thanks a million for your help!!!

Dave H