About Us

Jay

Thanks JE McGimpsey.

Regards,
Jay

"JE McGimpsey" wrote:

Perhaps:

With ActiveCell
If .Column = 5 And .Column <= 64 Then _
MsgBox Choose(.Column Mod 4 + 1, "Forecast", _
"Beginning Inventory", "Ending Inventory", _
"Production") & " Column"
End With

I'm assuming you didn't mean to duplicate the column numbers for
clBInventory and clEInventory...

In article ,
Jay wrote:

How do I trapped this error in my code?
kindly try using the procedure just paste it.

-Input/ click on the activecell.column which is not indicated in those sets
of array?
-how do I optimized this code?

Thank you in advance,
Jay

Sub Batchdist()

Dim pMonths, dMonths As Long
Dim DomesticBlkTotcell, ForecastValue, clBInventory, clProduction,
clEInventory As Variant
Dim clBonna As Long
Dim clProdx, clBInvx, clFcstx, clEInvx As Variant
Dim blStatus As Boolean

clprdnBonna = 17
clfcstBonna = 18
cleiBonna = 19

'//List of all Production column number transferred to an array
clProduction = Array(7, 11, 15, 19, 23, 27, 31, 35, 39, 43, 47, 51,
55, 59, 63)
clBInventory = Array(5, 9, 13, 17, 21, 25, 29, 33, 37, 41, 45, 49,
53, 57, 61)
clForecast = Array(8, 12, 16, 20, 24, 28, 32, 36, 40, 44, 48, 52,
56, 60, 64)
clEInventory = Array(9, 13, 17, 21, 25, 29, 33, 37, 41, 45, 49, 53,
57, 61, 65)

With ActiveCell
rw = .Row
cl = .Column
End With

'//Checks if Activecell is on Production Column
clProdx = clProduction
clBInvx = clBInventory
clFcstx = clForecast
clEInvx = clEInventory

For clProduction = 0 To 15
For clBInventory = 0 To 15
For clForecast = 0 To 15
For clEInventory = 0 To 15

If ActiveCell.Column = clProdx(clProduction) Then
MsgBox "Production Column"
Exit Sub

Else
On Error GoTo nxtLine:

If ActiveCell.Column = clBInvx(clBInventory) Then
MsgBox "Beginning Inventory Column"
Exit Sub

Else
On Error GoTo nxtLine1:

If ActiveCell.Column = clFcstx(clForecast) Then
MsgBox "Forecast Column"
Exit Sub

Else
On Error GoTo nxtLine2:

If ActiveCell.Column = clEInvx(clEInventory) Then
MsgBox "Ending Inventory Column"
Exit Sub

Else
On Error GoTo nxtLine3:

End If
End If
End If
End If

nxtLine3:
Next clEInventory
nxtLine2:
Next clForecast
nxtLine1:
Next clBInventory
nxtLine:
Next clProduction

End Sub

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Form Err.Raise error not trapped by entry procedure error handler	[email protected]	Excel Programming	1	February 8th 06 10:19 AM
Error trapped only while stepping through the code - Not triggered when run	Jeff	Excel Discussion (Misc queries)	7	March 7th 05 06:29 PM
Error trapped only while stepping through the code - Not triggered when run	Jeff	Excel Discussion (Misc queries)	0	February 28th 05 06:26 PM
Error not trapped - please help!!!	Bura Tino	Excel Programming	8	April 18th 04 09:27 PM
Can error 1004 be trapped????	Richard m	Excel Programming	3	November 29th 03 02:55 PM

Menu

About Us