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!

Help with OFFSET Problem Please
 

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

Help with OFFSET Problem Please
 

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!

Help with OFFSET Problem Please
 

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!

Help with OFFSET Problem Please
 

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

Help with OFFSET Problem Please
 

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!