2^9 is only 512 rows - not big in Excel terms

To shift the table to a new sheet, insert a sheet, name it "New Sheet", and use this code

With Worksheets("New Sheet")
myR = .Cells(Rows.Count, 8).End(xlUp)(2).Row
..Cells(myR, 8).Value = myB2.Value
..Cells(myR, 9).Value = myB4.Value
Range("G9:G11").Copy
..Cells(myR, 10).PasteSpecial xlValues, Transpose:=True
End With


HTH,
Bernie
MS Excel MVP


"Oligo" wrote in message
...
actually my data combinations will be like 2^9 . haha that is too much.
now my problems is how to shift the table??? preferably to another excel
sheet. but how to reference the vba data from other sheet?


"Bernie Deitrick" wrote:

My bad: When I converted to ranges from arrays, I forgot to add the Set command.

To fix it, simply change

myB2Arr = Worksheets("Main Data").Range("A3:A41")
myB4Arr = Worksheets("Main Data").Range("A3:A41")

to

Set myB2Arr = Worksheets("Main Data").Range("A3:A41")
Set myB4Arr = Worksheets("Main Data").Range("A3:A41")


HTH,
Bernie
MS Excel MVP


"Oligo" wrote in message
...
Sub MakeATable()
Dim myB2Arr As Range
Dim myB4Arr As Range
Dim myB2 As Range
Dim myB4 As Range
Dim myR As Long

myB2Arr = Worksheets("Main Data").Range("A3:A41")
myB4Arr = Worksheets("Main Data").Range("A3:A41")

For Each myB2 In myB2Arr
For Each myB4 In myB4Arr

Range("B2").Value = myB2.Value
Range("B4").Value = myB4.Value

Application.CalculateFull
myR = Cells(Rows.Count, 8).End(xlUp)(2).Row
Cells(myR, 8).Value = myB2.Value
Cells(myR, 9).Value = myB4.Value
Range("G9:G11").Copy
Cells(myR, 10).PasteSpecial xlValues, Transpose:=True
Next myB4
Next myB2

End Sub


i got a runtime error saying object variable or with block variable not set.
what's wrong?thx