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
--
Rick (MVP - Excel)
"Jay" <(E-Mail Removed)> wrote in message
news:8C174C90-9DC0-4D71-BBA5-(E-Mail Removed)...
> 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
Similar Threads
