Finding if a variable is equal to a number set

[Finndus]

Im trying to compare a variable from the main program into a sub with a set numbers. Im also using a function which will check if the variable is true or false. The sub runs in a loop.

As i run the program excel melts down.
Please Help!!

Below is the sample code:

THIS IS CALLED FROM A DO LOOP

Private Sub InternalTools(ByVal G As Integer)
Dim i As Integer
Dim InternalToolsAre As Integer

i = G

'check for internal tool
On Error GoTo InternalToolsAreNotTrue
For i = 5 To 11 Step 1
If InternalTool(i) = True Then
InternalToolsAre = 1
Exit Sub
End If
Next i
InternalToolsAreNotTrue:
InternalToolsAre = 0
End Sub


AND THE FUNCTION

Function InternalTool(Z) As Boolean
On Error GoTo Nexti
If i = Z Then
InternalTool = True
Exit Function
Else
InternalTool = False
End If
Nexti:
End Function

 

[alan.m]

I may be way off track, but:
i is assigned the value of G
then, in your For loop, i is set to 5 and increments by 1's
so you don't really need the line i = G because it's changed by the line i = 5 to 11

and

in the Function
if i = Z
i is never set a value, so is "empty". i = Z will only be true if Z is also empty?

 

[Finndus]

Ok, I've done a few mods and i think im getting closer but still not right. if you follow the comments I think it will make it enough sense - in essence these run from a main program, im trying to get excel to record the present tool number and then only throw the user form up if the tool number is in the range of 5 to 11, if not skip to the next different tool number.

Thanks for your help so far.



Private Sub ToolSleeveAndToolOut(ByVal D As Integer, ByVal E As Integer, ByVal F As Integer)
'd=toolnumber
'e=nexttoolnumber
'f=userformtoolnumber

Dim SheetToolDescription, UserFormToolNumber, Process As String
Dim i As Integer

'goto first tool cell
Range("C17").Select
D = ActiveCell
UserFormToolNumber = ActiveCell
E = 0
i = 5
Do
'see if tools are equal
If E = D Then
ActiveCell.Offset(1, 0).Select 'get next tool
E = ActiveCell
UserFormToolNumber = ActiveCell
Else 'see if different tool number falls in the range of 5 to 11
For i = 5 To 11 Step 1
If InternalTool(D) = True Then
GoTo DoForm
Else
End If
Next i
i = 5 'reset i
GoTo Continue
DoForm:
'offset to get process
ActiveCell.Offset(0, 4).Select
Process = ActiveCell
'offset to get tool description
ActiveCell.Offset(0, 1).Select
'get tool description
SheetToolDescription = ActiveCell
'load userform with information
Load IntToolClearance
With IntToolClearance
.ToolNumber.Caption = "Tool Number: " & UserFormToolNumber
.ToolDescription = "Tool Description: " & SheetToolDescription
.Process.Caption = "Process: " & Process
.CboSleeve.AddItem "1"
.CboSleeve.AddItem "2"
.Show
'referance back to tool number
ActiveCell.Offset(0, -10).Select
'save current tool number
D = ActiveCell
'offset to next tool number
ActiveCell.Offset(1, 0).Select
'get tool number to skip
E = ActiveCell
F = ActiveCell
End With
End If
Continue:
'loop until blank space
Loop Until IsEmpty(ActiveCell)
Call CancelTools
End Sub




Function InternalTool(D As Integer) As Boolean
On Error GoTo Nexti
If InternalTool(D) = i Then
InternalTool = True
Exit Function
Else
InternalTool = False
End If
Nexti:
End Function

 

[alan.m]

Hi,
I've been looking at ways to do this and made a couple of changes. Particularly around checking if D is between 5 & 11.
It would be easier if I had the main sub that call this on, cause I dont have values to pass to this sub, but it doesn't appear that the values passed are used?

Private Sub ToolSleeveAndToolOut()
'd=toolnumber
'e=nexttoolnumber
'f=userformtoolnumber
Dim SheetToolDescription, UserFormToolNumber, Process As String
Dim i, D As Integer
'goto first tool cell
Range("C17").Select
D = ActiveCell
UserFormToolNumber = ActiveCell
E = 0
Do
    'see if tools are equal
    If E = D Then
        ActiveCell.Offset(1, 0).Select 'get next tool
        E = ActiveCell
        UserFormToolNumber = ActiveCell
    Else 'see if different tool number falls in the range of 5 to 11
        If D >= 5 And D <= 11 Then
            'offset to get process
            Process = ActiveCell.Offset(0, 4)
            'offset to get tool description
            'get tool description
            SheetToolDescription = ActiveCell.Offset(0, 5)
            'load userform with information
            Load IntToolClearance
            With IntToolClearance
                .ToolNumber.Caption = "Tool Number: " & UserFormToolNumber
                .ToolDescription = "Tool Description: " & SheetToolDescription
                .Process.Caption = "Process: " & Process
                .CboSleeve.AddItem "1"
                .CboSleeve.AddItem "2"
                .Show
                'referance back to tool number
                ActiveCell.Offset(0, -10).Select
                'save current tool number
                D = ActiveCell
                'offset to next tool number
                ActiveCell.Offset(1, 0).Select
                'get tool number to skip
                E = ActiveCell
                F = ActiveCell
            End With
        End If
    'loop until blank space
Loop Until IsEmpty(ActiveCell)
Call CancelTools
End Sub

You use offset a lot, and I've left a lot of it alone because I didn't have your data to follow where things were jumping to.
Hope this is of some help.

 

[Finndus]

Hi Alan,

No it didnt work

The offsetting is fine because if i run the macro without variable range check it runs through as it should.

Any other ideas?