Help converting IF NOT OR AND to VBA IF statement

[Joyner]

Hello everyone, I am trying to convert the below working IF to VBA:

=NOT(OR(AND(LEFT($C6,1)="T",LEN($C6)=9,ISNUMBER(-RIGHT($C6,8))),AND(ISNUMBER(-$C6),LEN($C6)=9)))

What I have tried is:

  [FONT=Calibri][SIZE=3][COLOR=#000000]If Not ((Left(Cells(i + 1, 3), 1).Value = "T" AndLen(Cells(i + 1, 3)) = 9 And IsNumeric(-Right(Cells(i + 1, 3), 8))) Or(IsNumeric(-Cells(i + 1, 3)) And Len(Cells(i + 1, 3)) = 9)) Then [/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000][/Co[/COLOR][/SIZE][/FONT]de]

This is giving me a run time error 424 object required.

What am I missing?

Thank you

 

[mikerickson]

Other than replacing the cell references with Cells(row,column) and changing ISNUMBER to the VBA function IsNumeric, the working formula should work as it.

 

[Joyner]

Hello mikerrickson and thank you for the reply. Sorry but I don't quite understand, are you saying I should be able to use the formula working on the worksheet, something like this:

 If OR(AND(LEFT(Left(Cells(i + 1, 3),1)="T",LEN(Left(Cells(i + 1, 3))=9,IsNumeric(-RIGHT(Left(Cells(i + 1, 3),8))),AND(IsNumeric(-Left(Cells(i + 1, 3)),LEN(Left(Cells(i + 1, 3))=9)))= False Then

 

[mikerickson]

In the OP, you mention a "working IF", but the formula posted has no IF function. It is a valid Excel worksheet formula that returns a boolean value.

That formula can be uses as the first argument of a worksheet's IF formula.

I don't understand why you want to convert that to VBA.

 

[Joyner]

Sorry, no it is not an IF function, it was a conditional formula to return TRUE or FALSE that I tested on the sheet and wanted the same result in code to highlight cells that meet the same criteria. I need the VBA because the code rebuild the data when ran and tests the cells or the criteria. I hope that is clear.

Thanks

 

[mikerickson]

What code "rebuilt the data"?
Have you tried using that as a Conditional Formatting formula to highlight the cells you want?

 

[Joyner]

Hello,
The data is built with the loop shown below.
I did use conditional formatting to format the column afterthe code ran, but changed my approach because I also needed to count the occurrenceswhere the cells don’t match the criteria (cell colored) so I was trying tocount the cells based on their interior color but was having a hard time withthat since it seems the code I had didn’t work for conditional formatting colorso I thought it would be easier to just format and count in the loop as shownbelow. This is working fine except forthe one IF statement line that errors out as I mentioned above (I can't seem to highlight it Red); “run-time error424 object required” I am also showing DIM statements in case those are causing the issue.
So what I would like if possible, is how to properly writethe conditional statement in VBA that is working in a regular worksheet formulashown above.
Thanks

[FONT=Calibri][SIZE=3][COLOR=#000000]Dim objFSO As Object[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim objFolder As Object[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim objSubFolder As Object[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim i As Integer[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim sStr As String[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim rng As Range[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim lastRow As Long[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim myRange As Range[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim iCount As Integer[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]icount =0[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]For Each objSubFolder In objFolder.subfolders[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    'print folder name[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Cells(i + 1, 2) =objSubFolder.Name[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        sStr =Right(objSubFolder.Name, 9)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        Cells(i + 1,3).NumberFormat = "@"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]           If IsNumeric(Right(objSubFolder.Name, 8))Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]               Cells(i + 1, 3) = sStr[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                Cells(i + 1, 2) =Left(objSubFolder.Name, Len(objSubFolder.Name) - 10)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                    IfNot (Left(Cells(i + 1, 3), 1).Value = "T" And Len(Cells(i + 1, 3)) =9 And IsNumeric(-Right(Cells(i + 1, 3), 8)) Or IsNumeric(-Cells(i + 1, 3)) AndLen(Cells(i + 1, 3)) = 9) Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       With Cells(i + 1, 3)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                           .Interior.Color = 13551615[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                           .Font.Color = -16383844[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                       iCount = iCount + 1[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                   End If[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]            Else[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]               Cells(i + 1, 3) = ""[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                WithCells(i + 1, 3)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                   .Interior.Color = 13551615[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                   .Font.Color = -16383844[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                EndWith[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]                iCount= iCount + 1[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]            End If[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    'print folder path[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Cells(i + 1, 4) =objSubFolder.Path[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    i = i + 1[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Next objSubFolder
[/COLOR][/SIZE][/FONT]

 

[Joyner]

Okay, it seems this:

Left(Cells(i + 1, 3), 1).Value = "T" was throwing the 424 Object required error, changed to :

Left(Cells(i + 1, 3), 1) = "T"

Now I have a run time error 13 type mismatch here:
IsNumeric(-(Cells(i + 1, 3))) when the value in the cell starts with a “T”
In my worksheet formula where I use ISNUMBER, that simplyreturns TRUE or FALSE, but here, I guess IsNumeric errors if the value is Text,or is that wrong and something else is the issue?
Thanks

 

[Joyner]

Solved:

had to remove the negative signs so this works:

 If Not (Left(Cells(i + 1, 3), 1) = "T" And Len(Cells(i + 1, 3)) = 9 And IsNumeric(Right(Cells(i + 1, 3), 8)) Or IsNumeric(Cells(i + 1, 3)) And Len(Cells(i + 1, 3)) = 9) Then

 

[Rick Rothstein]

Solved:

had to remove the negative signs so this works:

 If Not (Left(Cells(i + 1, 3), 1) = "T" And Len(Cells(i + 1, 3)) = 9 And IsNumeric(Right(Cells(i + 1, 3), 8)) Or IsNumeric(Cells(i + 1, 3)) And Len(Cells(i + 1, 3)) = 9) Then

If I am reading your original formula (Message #1 ) correctly, I believe this line of code will also work...

[table="width: 500"]
[tr]
	[td]IF Cells(i + 1, 3) Like "[A-SU-Z]########" Or Cells(i + 1, 3) Like "########" Then[/td]
[/tr]
[/table]

As an aside, if you wanted to go back to your original concept that you abandoned, the Range object in XL2010 and later has a property named DisplayFormat that will let you obtain formats assigned to cells via Conditional Formatting or normal formatting. For example, if you wanted the interior color of, say, cell A2, you could obtain it this way...

MsgBox Range("A2").DisplayFormat.Interior.Color

Note, though, that this property does not distinguish where the color came from (manually applied or from Conditional Formatting)... it simply tells you the color that is displayed in the cell at the moment. The same applies to any other format that DisplayFormat is used to obtain.