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

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
---------------
Jacob Skaria


"KAH" wrote:

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

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" wrote:

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
---------------
Jacob Skaria


"KAH" wrote:

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

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
---------------
Jacob Skaria


"KAH" wrote:

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" wrote:

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
---------------
Jacob Skaria


"KAH" wrote:

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