How do I trapped the error and optimize the code?

[Jay]

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

 

[JE McGimpsey]

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...

 

[Rick Rothstein]

More than likely, JE has given you what you want; however, it would be
better when you are posting long code to tell us what the code is attempting
to do so we don't have to try and figure it out (I decided not to try,
apparently JE did try). I did want to point out one 'minor' problem with one
part of your code. This line...

Dim pMonths, dMonths As Long

is probably not doing what you intended it to do. Only dMonths is declared
as a Long; pMonths is declared as a Variant. Unlike lots of other languages,
in VB, every variable must be declared as to Type individually; otherwise,
if it isn't, that variable is then declared as the default data Type which,
in VB, is a Variant. The above line needs to be written like this...

Dim pMonths As Long, dMonths As Long

or like this...

Dim pMonths As Long
Dim dMonths As Long

 

[JE McGimpsey]

And to get completely esoteric, the exception to Rick's excellent
explanation is that you *can* have Def<type> statements in the module. I
rarely see this, but you can put this at the top of your code module:

DefLng L-N
DefStr S

Then

Public Sub try2()
Dim sString
Dim nLong
MsgBox TypeName(sString) & vbNewLine & TypeName(nLong)
End Sub

 

[Rick Rothstein]

I had completely forgotten that VB contained DefType statements... I
probably haven't used them since VB3 (the compiled version of VB from around
the early to mid 1990s); and I only used them then because I had some
programs in Fortran IV (which had a similar variable declaration statements)
from our DEC mini-computer which I needed to implement on our IBM personal
computers when we switched over to them. Now that I have been reminded of
their existence, and have given careful thought on them, I will not consider
ever using them in my programs and I will promptly try to forget about their
existence once again.<g>

 

[Jay]

Thanks JE McGimpsey.

Regards,
Jay

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...