If within If VBA

[Homegrownandy]

Im using some code to manipulate some raw data before its imported into access.

here is where the problem is occurring:

Sub newtest()
celltxt2 = ActiveSheet.Range("e2").Text
    celltxt = ActiveSheet.Range("F2").Text
    
    
If InStr(1, celltxt2, "Exp") Then
    If InStr(1, celltxt, "0") Then
   MsgBox "test"
    Range("A2:F2").Select
    Application.CutCopyMode = False
    Selection.ListObject.ListRows(1).Delete
    Else
    MsgBox ("Expenses")
    Exit Sub
    End If
''''''''''''''''''''''''''''
Else
MsgBox "raw data is formatted incorrectly"
End If
End Sub

the variable celltxt always = 0 when its within the first if....



If this code is on its own (without the first if and associated code) then it works fine.

I did have this all working together briefly but now I have made some changes and it wont work.

Does anyone know what I'm doing wrong here?

[aytee111]

Doesn't if have to be "If Instr(.....) > 0 Then"?

[ranman256]

Dont need instr(1,... ,if starting at one.
Don't need parenthesis around msgbox.
Not sure about your delete tho,if there's only one in it.

Sub newtest()
celltxt2 = ActiveSheet.Range("e2").Text
    celltxt = ActiveSheet.Range("F2").Text
    
    
If InStr(celltxt2, "Exp")>0 Then
    If InStr(celltxt, "0")>0 Then
        MsgBox "test"
        Range("A2:F2").Select
        Application.CutCopyMode = False
        Selection.ListObject.ListRows(1).Delete.   ' not sure here
    Else
        MsgBox "Expenses"
        Exit Sub
    End If
''''''''''''''''''''''''''''
Else
    MsgBox "raw data is formatted incorrectly"
End If
End Sub

[andy49]

If InStr(1, celltxt2, "Exp")

Does INSTR return True/false?

This line implies that is does but it may return a number eg 7

[JoeM]

Does INSTR return True/false?

This line implies that is does but it may return a number eg 7

I have seen people use this kind of implied logic before, and it usually does work.
In Boolean expressions (TRUE/FALSE), the number 0 usually equates to FALSE, and all other numbers equate to TRUE.
So you often can set up your expressions like that.

[Missinglinq]

Does INSTR return True/false?

No, it doesn't! It returns an Integer or, in certain cases (I believe) Null.

Linq ;0)>

[JoeM]

I am not sure when it would return Null (maybe if you had a NULL for one of the arguments)?
If it is not found, it would return 0, otherwise it returns a positive integer if found.

To prove what I said in my previous post is true, you can set up a real simple example. Create a Form, add a button to it, and add this code to the Click event of the button:

Private Sub Command14_Click()

    If InStr(1, "Microsoft", "soft") Then
        MsgBox "Example 1 evaluates to true"
    Else
        MsgBox "Example 1 evaluates to false"
    End If
    
    If InStr(1, "Microsoft", "Access") Then
        MsgBox "Example 2 evaluates to true"
    Else
        MsgBox "Example 2 evaluates to false"
    End If
    
End Sub

Now click the button and what what happens!

[orange]

More info on Instr()

[Micron]

Getting back to the original question for the moment, is this "0" text or a number?
InStr(1, celltxt, "0")
It won't work if it's a number.

As for Instr, the type is a variant because if one of the strings is Null, the function returns Null, otherwise the data type is Long.
MsgBox InStr(1, "Microsoft", "soft") - will return 6
MsgBox TypeName(InStr(1, "Microsoft", "soft")) - will return "Long"

As for the If block, any of these will evaluate to True
If 1 Then ; If 6 Then ; If -1 Then because none of them are 0. So for the example given, an evaluation that returns 0, as in InStr(1, "Microsoft", "Access") will be interpreted as False only because 0 is not True in Access.

[Homegrownandy]

wow, thanks for the reply's. a lot to take in here so ill answer some questions before looking to change the code with suggestions.

0 is a number and I want it to =0. The reason is.. No one should be claiming for expenses. If they are I want it to be flagged up and dealt with manually. The rest is automated.

Micron this code does work with a number if it is not inside the initial if statement. But its an interesting point about 0 being false. Although it was working ill change it a bit and see what happens. Think that's covered anything I can actually answer. Updates soon.

[Homegrownandy]

So this does work with text characters. Just tried it with A and put in D the code came back with the message box "expenses".

weird how the code would work with the 0 value before it was added to here.

In a cell near the table I have the indirect function reference the cell, then next to that I have the code =IF(F2=0,TRUE,FALSE) so now I have some text values to work with. should all be good. Thanks.

[Micron]

I believe I owe a correction. In Access, this will work as expected when the control is bound to a number field
If InStr(Text0, "1") Then MsgBox "found"
So I'm thinking your initial problem might be due to using the .Text property of the sheet range rather than the .Value property
I'm not an Excel vba guru by any means, but I do manage to stumble through it with a lot of Google help since it's an entirely different object model. A quick search on how to get the contents of a cell indicates the use of .Value rather than .Text It seems that .Text removes any formatting, but I suspect that's still not the reason for the initial problem