Hi,

I have Two Macros that Work Well Except for One thing.
I want the First Macro to Produce Results in Cells "A1:C100", then Cells
"F1:H100" and then Cells "K1:M100" etc. For this the Code Below Works
Fine :-

For i = 1 To nMaxF - 1
For j = i + 1 To nMaxF
nCount = nCount + 1
If nCount = 101 Then
nCount = 1
ActiveCell.Offset(-100, 5).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(-1, 0).Value = i
ActiveCell.Offset(-1, 1).Value = j
ActiveCell.Offset(-1, 2).Value = nTest1(i, j)
Next j
Next i

The Second Macro is Called from the First Macro and I want to Produce
Results in Cells "D1:D100", then Cells "I1:I100" and then Cells
"N1:N100" etc. For this the Code Below Does NOT Work, it Gives a '1004'
Error :-

For i = 1 To nMaxF - 1
For j = i + 1 To nMaxF
nCount = nCount + 1
If nCount = 101 Then
nCount = 1
ActiveCell.Offset(-100, 5).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(-1, 3).Value = nTest2(i, j)
Next j
Next i

It is the Above Line …
ActiveCell.Offset(-100, 5).Select
… that is Giving the Error.

If in the Two Macros I Do NOT Use Offset, Everything is Fine.

Any Help will be Appreciated.
All the Best.
Paul

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Paul,

I don't completely understand your code.
However the line...

"ActiveCell.Offset(-100, 5).Select'"

will error if the ActiveCell is in Row 100 or less.

Regards,
Jim Cone
San Francisco, USA


"Paul Black" wrote in message ...
Hi,

I have Two Macros that Work Well Except for One thing.
I want the First Macro to Produce Results in Cells "A1:C100", then Cells
"F1:H100" and then Cells "K1:M100" etc. For this the Code Below Works
Fine :-

For i = 1 To nMaxF - 1
For j = i + 1 To nMaxF
nCount = nCount + 1
If nCount = 101 Then
nCount = 1
ActiveCell.Offset(-100, 5).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(-1, 0).Value = i
ActiveCell.Offset(-1, 1).Value = j
ActiveCell.Offset(-1, 2).Value = nTest1(i, j)
Next j
Next i

The Second Macro is Called from the First Macro and I want to Produce
Results in Cells "D1:D100", then Cells "I1:I100" and then Cells
"N1:N100" etc. For this the Code Below Does NOT Work, it Gives a '1004'
Error :-

For i = 1 To nMaxF - 1
For j = i + 1 To nMaxF
nCount = nCount + 1
If nCount = 101 Then
nCount = 1
ActiveCell.Offset(-100, 5).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(-1, 3).Value = nTest2(i, j)
Next j
Next i

It is the Above Line .
ActiveCell.Offset(-100, 5).Select
. that is Giving the Error.

If in the Two Macros I Do NOT Use Offset, Everything is Fine.

Any Help will be Appreciated.
All the Best.
Paul

Thanks for the Reply Jim.
Here is the Full Code :-

Option Explicit
Option Base 1

Dim i As Integer
Dim j As Integer
Dim nCount As Long
Dim nDw As Integer
Dim nMinA As Integer
Dim nMaxF As Integer
Dim nNo(7) As Integer

Sub Test()
Dim nNoB(20, 20) As Integer

Application.ScreenUpdating = False
Sheets("Data 1").Select
Range("A2").Select

nMinA = 1
nMaxF = 20

Do While ActiveCell.Value 0
nDw = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Loop

Range("A1").Select

For i = 1 To nDw
For j = 1 To 7
nNo(j) = ActiveCell.Offset(i, j).Value
Next j
nNoB(nNo(1), nNo(2)) = nNoB(nNo(1), nNo(2)) + 1
nNoB(nNo(1), nNo(3)) = nNoB(nNo(1), nNo(3)) + 1
nNoB(nNo(1), nNo(4)) = nNoB(nNo(1), nNo(4)) + 1
nNoB(nNo(1), nNo(5)) = nNoB(nNo(1), nNo(5)) + 1
nNoB(nNo(1), nNo(6)) = nNoB(nNo(1), nNo(6)) + 1
nNoB(nNo(2), nNo(3)) = nNoB(nNo(2), nNo(3)) + 1
nNoB(nNo(2), nNo(4)) = nNoB(nNo(2), nNo(4)) + 1
nNoB(nNo(2), nNo(5)) = nNoB(nNo(2), nNo(5)) + 1
nNoB(nNo(2), nNo(6)) = nNoB(nNo(2), nNo(6)) + 1
nNoB(nNo(3), nNo(4)) = nNoB(nNo(3), nNo(4)) + 1
nNoB(nNo(3), nNo(5)) = nNoB(nNo(3), nNo(5)) + 1
nNoB(nNo(3), nNo(6)) = nNoB(nNo(3), nNo(6)) + 1
nNoB(nNo(4), nNo(5)) = nNoB(nNo(4), nNo(5)) + 1
nNoB(nNo(4), nNo(6)) = nNoB(nNo(4), nNo(6)) + 1
nNoB(nNo(5), nNo(6)) = nNoB(nNo(5), nNo(6)) + 1
Next i

Sheets("Results").Select
Range("A1").Select

For i = 1 To nMaxF - 1
For j = i + 1 To nMaxF
nCount = nCount + 1
If nCount = 101 Then
nCount = 1
ActiveCell.Offset(-100, 5).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(-1, 0).Value = i
ActiveCell.Offset(-1, 1).Value = j
ActiveCell.Offset(-1, 2).Value = nNoB(i, j)
Next j
Next i

Call Extra

Application.ScreenUpdating = True
End Sub

Private Sub Extra()
Dim nB(20, 20) As Integer

Sheets("Data 2").Select
Range("A2").Select

Do While ActiveCell.Value " "
nDw = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Loop

Range("A1").Select

For i = 1 To nDw
For j = 1 To 7
nNo(j) = ActiveCell.Offset(i, j).Value
Next j
nB(nNo(1), nNo(2)) = nB(nNo(1), nNo(2)) + 1
nB(nNo(1), nNo(3)) = nB(nNo(1), nNo(3)) + 1
nB(nNo(1), nNo(4)) = nB(nNo(1), nNo(4)) + 1
nB(nNo(1), nNo(5)) = nB(nNo(1), nNo(5)) + 1
nB(nNo(1), nNo(6)) = nB(nNo(1), nNo(6)) + 1
nB(nNo(1), nNo(7)) = nB(nNo(1), nNo(7)) + 1
nB(nNo(2), nNo(3)) = nB(nNo(2), nNo(3)) + 1
nB(nNo(2), nNo(4)) = nB(nNo(2), nNo(4)) + 1
nB(nNo(2), nNo(5)) = nB(nNo(2), nNo(5)) + 1
nB(nNo(2), nNo(6)) = nB(nNo(2), nNo(6)) + 1
nB(nNo(2), nNo(7)) = nB(nNo(2), nNo(7)) + 1
nB(nNo(3), nNo(4)) = nB(nNo(3), nNo(4)) + 1
nB(nNo(3), nNo(5)) = nB(nNo(3), nNo(5)) + 1
nB(nNo(3), nNo(6)) = nB(nNo(3), nNo(6)) + 1
nB(nNo(3), nNo(7)) = nB(nNo(3), nNo(7)) + 1
nB(nNo(4), nNo(5)) = nB(nNo(4), nNo(5)) + 1
nB(nNo(4), nNo(6)) = nB(nNo(4), nNo(6)) + 1
nB(nNo(4), nNo(7)) = nB(nNo(4), nNo(7)) + 1
nB(nNo(5), nNo(6)) = nB(nNo(5), nNo(6)) + 1
nB(nNo(5), nNo(7)) = nB(nNo(5), nNo(7)) + 1
nB(nNo(6), nNo(7)) = nB(nNo(6), nNo(7)) + 1
Next i

Sheets("Results").Select
Range("A1").Select

For i = 1 To nMaxF - 1
For j = i + 1 To nMaxF
nCount = nCount + 1
If nCount = 101 Then
nCount = 1
ActiveCell.Offset(-100, 5).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(-1, 3).Value = nB(i, j)
Next j
Next i

End Sub

Thanks in Advance.
All the Best.
Paul

"Paul Black" wrote in message
...
Hi,

I have Two Macros that Work Well Except for One thing.
I want the First Macro to Produce Results in Cells "A1:C100", then
Cells
"F1:H100" and then Cells "K1:M100" etc. For this the Code Below Works
Fine :-

For i = 1 To nMaxF - 1
For j = i + 1 To nMaxF
nCount = nCount + 1
If nCount = 101 Then
nCount = 1
ActiveCell.Offset(-100, 5).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(-1, 0).Value = i
ActiveCell.Offset(-1, 1).Value = j
ActiveCell.Offset(-1, 2).Value = nNoB(i, j)
Next j
Next i

The Second Macro is Called from the First Macro and I want to Produce
Results in Cells "D1:D100", then Cells "I1:I100" and then Cells
"N1:N100" etc. For this the Code Below Does NOT Work, it Gives a
'1004'
Error :-

For i = 1 To nMaxF - 1
For j = i + 1 To nMaxF
nCount = nCount + 1
If nCount = 101 Then
nCount = 1
ActiveCell.Offset(-100, 5).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(-1, 3).Value = nB(i, j)
Next j
Next i

It is the Above Line .
ActiveCell.Offset(-100, 5).Select
. that is Giving the Error.

If in the Two Macros I Do NOT Use Offset, Everything is Fine.

Any Help will be Appreciated.
All the Best.
Paul


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Not to Worry, I have Managed to Sort it Out.
One Question though, How can I get it to Start the Output in "A1"
WITHOUT Using a Minus in the Offset Please.
Snippet of Code Used for the Offset.

For i = 1 To nMaxF - 1
For j = i + 1 To nMaxF
nCount = nCount + 1
If nCount = 65001 Then
nCount = 1
ActiveCell.Offset(-65000, 5).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(-1, 0).Value = i
ActiveCell.Offset(-1, 1).Value = j
ActiveCell.Offset(-1, 2).Value = nNoB(i, j)
ActiveCell.Offset(-1, 3).Value = nB(i, j)
Next j
Next i

If I Don't Use the Minus it Starts the Output from Cell "A2".
Thanks in Advance.
All the Best.
Paul


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Paul,

Move the ActiveCell.Select statement to the bottom and
change all the "-1" values to 0. The following appeared
to work for me...
'----------------------------
Sub TestAgain()
Dim nMaxF As Long
nMaxF = 10
Dim i As Long
Dim j As Long
Dim nCount As Long

For i = 1 To nMaxF - 1
For j = i + 1 To nMaxF
nCount = nCount + 1
If nCount = 65001 Then
nCount = 1
ActiveCell.Offset(-65000, 5).Select
End If
ActiveCell.Value = i
ActiveCell.Offset(0, 1).Value = j
ActiveCell.Offset(0, 2).Value = nNoB(i, j)
ActiveCell.Offset(0, 3).Value = nB(i, j)
ActiveCell.Offset(1, 0).Select
Next j
Next i
End Sub
'---------------------------------
Regards,
Jim Cone
San Francisco, USA


"Paul Black" wrote in
message ...
Not to Worry, I have Managed to Sort it Out.
One Question though, How can I get it to Start the Output in "A1"
WITHOUT Using a Minus in the Offset Please.
Snippet of Code Used for the Offset.
For i = 1 To nMaxF - 1
For j = i + 1 To nMaxF
nCount = nCount + 1
If nCount = 65001 Then
nCount = 1
ActiveCell.Offset(-65000, 5).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(-1, 0).Value = i
ActiveCell.Offset(-1, 1).Value = j
ActiveCell.Offset(-1, 2).Value = nNoB(i, j)
ActiveCell.Offset(-1, 3).Value = nB(i, j)
Next j
Next i
If I Don't Use the Minus it Starts the Output from Cell "A2".
Thanks in Advance.
All the Best.
Paul

Hi Jim,

Thanks Very Much, that Worked Perfect.

Thanks for your Time. Have a Good Weekend.
All the Best.
Paul



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!