Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Populate array value using For Next loop

  Asked By: Magenta    Date: Nov 06    Category: MS Office    Views: 844
  

I am trying to use a For Next loop for populating cells in a
spreasheet from an alreday defined array. K(17)
I have three Worsheets in my file config, report, Sheet3.
My problem is that, although I activate the sheet "report" the values
of the array (K17) are written to the spreadsheet "config".
I also changed the code as follows.
Sheets("report").Range(Cells(1, d), Cells(1, d)).value=K(d) OR
Worksheets("report").Range(Cells(1, d), Cells(1, d)).value=K(d)

however that generate a run-tem error (Application-defined or object-
defined error).


d = 1
For co = 1 To 17
Worksheets("report").Activate
Range(Cells(1, d), Cells(1, d)).value = K(d)
d = d + 1
Next co

Share: 

 

5 Answers Found

 
Answer #1    Answered By: Jared Adams     Answered On: Nov 06

Not sure if this is what you are looking for,
If the arrray is referring to Column K, try this:

ColumnK = Range("k1").Columns.Column
For co = 1 To 17
Worksheets(" report"). Activate
Cells( 1, d).value = Cells(ColumnK,d).value
Next co

 
Answer #2    Answered By: Tarrant Thompson     Answered On: Nov 06

I am trying to populate  the contents of the array  K(1-17) into any
seelected row/column in the spreadhsheet "report". For some reason it
will not write to and gives me the run time error  or object defined
error. I was wondeering if I need to explicity define the
worksheet "report".?

 
Answer #3    Answered By: Janelle Evans     Answered On: Nov 06

I goofed on that last transmission!

If "K" is a defined  range somewhere in the worksheet or workbook and you are
looking to copy to a vertical contiguous cell range, then try

target = Sheets("sheet3").Index
For co = 1 To 17
Sheets(target).Cells(co, 1) = Range("k")(co)
Next co

Also, is this array  'K' generated by some VB code  earlier in the process?
Your original version appears to copy the K range to a diagonal range of cells!
Is this correct? Based on the line items d=1 & following d=d+1 in the loop
structure.

 
Answer #4    Answered By: Lela Lynch     Answered On: Nov 06

Worksheets("report").Cells(1, d).value = K(d)

(Not tested, but should be right.)

I don't think Worksheets has a Range member - if you want to specify
Worksheets as part of a range, they actually go inside the parentheses, as
part of the Range call's parameters. E.g.

Range (Worksheets("report").Cells(1, 1), Worksheets("report").Cells(1,
3)).etc

 
Answer #5    Answered By: Mark R     Answered On: Nov 06

You can avoid the For.. Next loop

Try this:

Sheets("report").Activate
Range(Cells(1, 1), Cells(1, 17)) = K

Or if you want to keep the upper range of K flexible, then try:

u = UBound(K)
Sheets("report").Activate
Range(Cells(1, 1), Cells(1, u)) = K

 
Didn't find what you were looking for? Find more on Populate array value using For Next loop Or get search suggestion and latest updates.




Tagged: