assigning values to cells in worksheet

[KAH]

I wrote the function below and received the error message 'Object variable or
With block variable not set' for the line:
wsSorting.Cells(idx, 1).Value = myArray(idx)
I think I haven't written this proplery. How do I put a value (number) into
the cell of a worksheet?
I also have a general question about using worksheets in VisualBasic. Are
they all the same or can they be virtual or real? I am using a worksheet to
sort and rank numbers but it may take too long if the worksheet is not
virtual. I also do not want to show the worksheet in Excel. I hope this makes
sense.
Thanks for any help.


Public Function Sorting(myArray() As Double) As Double
'Sorting the permutations and identifying the rank of the observed value
For idx = 1 To UBound(myArray)
wsSorting.Cells(idx, 1).Value = myArray(idx)
wsSorting.Cells(idx, 2).Value = idx
Next idx
wsSorting.Range("A:B").Sort Key1:=wsSorting.Cells(1, 1),
Order1:=xlAscending
For idx = 1 To UBound(myArray)
wsSorting.Cells(idx, 3).Value = idx
Next idx
wsSorting.Range("A:C").Sort Key1:=wsSorting.Cells(1, 2),
Order1:=xlAscending

End Function

 

[Jacob Skaria]

Sheets("Sheet1").cells(1,1).value = 5 will work. So your code should also
work.

Is wsSorting referred to a sheet; and when this is used wihtin a function is
this delcared as a global variable..
Dim wsSorting as Worksheet
Set wsSorting = Workbooks("filename.xls").Sheets("sheetname")


If this post helps click Yes

 

[KAH]

It was declared as a worksheet earlier:
Public wsSorting As Worksheet
But now I think I see the problem - I did not set it as anything. Is it
possible to just keep wsSorting as a virtual worksheet where I can just add
values?

 

[Jacob Skaria]

Pass wsSorting as an argument...like below



Public Function Sorting(wsTemp As workSheet, myArray() As Double) As Double
'Sorting the permutations and identifying the rank of the observed value
For idx = 1 To UBound(myArray)
wsTemp.Cells(idx, 1).Value = myArray(idx)
wsTemp.Cells(idx, 2).Value = idx
Next idx
wsTemp.Range("A:B").Sort Key1:=wsTemp.Cells(1, 1),
Order1:=xlAscending
For idx = 1 To UBound(myArray)
wsTemp.Cells(idx, 3).Value = idx
Next idx
wsTemp.Range("A:C").Sort Key1:=wsTemp.Cells(1, 2),
Order1:=xlAscending

End Function

If this post helps click Yes