nth cell in the range

[Anne-Marie]

Within a VBA Module, I would like to set the ith component of my array
A(1 to N) to be equal to the 100*(nth cell in a column in excel).
How can I do this within a loop?

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Frank Kabel]

Hi
try something like the following

sub foo()
dim c_values(1 to 100)
dim i
for i = 1 to 100
c_values(i)=activesheet.cells(i*100,"A").value
next
end sub

 

[Anne-Marie]

THanks for the suggestion.

I tried the line below, but it comes up with debug and in yellow.


Vt(i) = Workbooks("test2").Worksheets("Data").Cells(j, "A")

Any other suggestions?
My full loop is:
For i = 1 To nsim
j = Int(Rnd() * 100)
Vt(i) = Workbooks("test2").Worksheets("Data").Cells(j,
"A")
If Vt(i) >= K Then
Fsum = Fsum + (Vt(i) - K)
Else
End If
Next i

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Frank Kabel]

Hi
code works for me (after adapting to my workbook set-up). What error
message do you receive?

 

[Anne-Marie]

The error message reads:
Run-time error '438'
Object doesn't support this property or method

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Frank Kabel]

Hi
check that the Excel/Office library is set in your VBA environment
8'goto 'tools - references' for this)

 

[Anne-Marie]

When I do that I am prompted for a password which i do not have

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **