Help with OFFSET Problem Please

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" <Anonymous@Discussions.Microsoft.com> wrote in message news:edo98A5GFHA.3536@TK2MSFTNGP14.phx.gbl...
> 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" <Anonymous@Discussions.Microsoft.com> wrote in message
news:edo98A5GFHA.3536@TK2MSFTNGP14.phx.gbl...
> 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" <Anonymous@Discussions.Microsoft.com> wrote in
message news:eTBBFiBHFHA.3332@TK2MSFTNGP15.phx.gbl...
> 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!