If statement with multiple variables

[Legacy 93538]

Hi

Does anyone know whether its possible with an if statement to say if the cell value is like "*Gross" but does not contain "Total New", "Total Used" or "Sub-Total"?

As i need to create an if statement which looks at a cell value and if it contains "gross" then put "gross" in the cell of another sheet but if it contains Gross and one of the following "Total New", "Total Used" or "Sub-Total" then ignore it

Thanks

Jessicaseymour

 

[Gerald Higgins]

Not very elegant, but this seems to work.

=IF(AND(ISERROR(FIND("Total New",A1,1)),
ISERROR(FIND("Total Used",A1,1)),ISERROR(FIND("Sub-Total",A1,1))),
IF(ISERROR(FIND("Gross",A1,1)),"","Gross"),"")

Note, the FIND() function is case sensitive. If that's a problem, try SEARCH() instead.

 

[Legacy 93538]

Hi

Thanks for replying!

Now i know its possible i should provide more detail.

The If statement will go inside a loop which loops through the cell range F4:U640 on the WB variable PPFWB.Sheets("IRFORM") and then has two if statements one to check if the cell is empty, if it hasn't it should then complete the if statment you created.

The sheet it needs to paste value in is a variable called PPCWBSht. the code i use to place the value in that sheet is

PPCWBSht.Cells(NRow, 4).Value =

This is what i have so far but i am not sure how i would interrogate your code.

NRow = 2
Application.StatusBar = "Loop through calcs Form and populate calculations table"
For Each cell In PPFWB.Sheets("IRFORM").Range("F4:U640")
    If cell.Value <> "" Then
         <  IF(AND(ISERROR(FIND("Total New",A1,1)),
ISERROR(FIND("Total Used",A1,1)),ISERROR(FIND("Sub-Total",A1,1))),
IF(ISERROR(FIND("Gross",A1,1)),"","Gross"),"") >
       End If
            NRow = NRow + 1
            Application.StatusBar = cell.Address
    End If
Next cell

 

[Gerald Higgins]

Sorry I'm not a VBA expert.
I'm sure there will be a way of handling the logic I coded into that formula, in VBA, but I'm not the best person to give you that.

Anyone else ?