"Value if true" for AND Formula

[glaupie]

Hi,

For work I do a monthly text message marketing campaign to accounts on our book that meet certain criteria. To do this I have a lenghty macro which runs tests against our account extract, then creates a calculation sheet to show how many account were excluded for what reason, and finally it highlights all the accounts that meet the criteria for the campaign.

One of the tests in the macro is to see if the cellphone number on our books is a valid number. I used to do this by saying IF the number is 10 characters in length and starts with 07 or 08 then the value is "T", otherwise it is "F". All my subsequent calculations and formulas then use this "T" and "F".

My problem is that now our company has changed the numbers to include international dialling codes. My formula now looks like this:
=AND(OR(AND(LEN(AV3)=15,LEFT(AV3,6)="+27(0)"),AND(LEN(AV3)=16,LEFT(AV3,7)="+264(0)")),ISNUMBER(VALUE(RIGHT(AV3,9))))

So the number is valid when the right 9 characters are numeric, AND when one of the following is true:
length is 15 and 6 left characters are +27(0) OR
length is 16 and 7 left characters are +264(0)

So this works fine but now it gives me "TRUE" or "FALSE" as an answer, which throws out all my other calculations in the macro, which all look for "T" and "F".

Can I edit the above formule so it shows T or F instead of the default True or False?

Please help!

 

[Peter_SSs]

Try

=IF(your existing formula,"T","F")

 

[glaupie]

Hi,

Thanks for the quick response.

I tried this (within the macro), but when I try to save it it is highlighted as red. When I run it it highlights the "T" and says: Compile error: expected: end of statement.

Sub CheckCellCol()
'
' CheckCellCol Macro
'
Sheets("Daily_AccountExtract").Select
Range("DP3").Select
' ActiveCell.FormulaR1C1 = _
' "=IF(AND(OR(LEFT(RC[-72],2)=""07"",LEFT(RC[-72],2)=""08""),LEN(RC[-72])=10),""T"",""F"")"
'
' ******** New Test ********
'
ActiveCell.FormulaR1C1 = _
"=IF(AND(OR(AND(LEN(RC[-72])=15,LEFT(RC[-72],6)=""+27(0)""),AND(LEN(RC[-72])=16,LEFT(RC[-72],7)=""+264(0)"")),ISNUMBER(VALUE(RIGHT(RC[-72],9)))),"T","F")"
'
Range("DP3").Select
Selection.Copy
Range("DP4:DP" & LastRow).Select
ActiveSheet.Paste
End Sub

 

[Peter_SSs]

The formula I gave was a conversion of your existing Worksheet formula. Applying it via vba requires double quotes for the ""T"" anf the ""F"", just like the text bits earlier in your R1C1 type formula.
eg Like this bit: =""+27(0)""

 

[glaupie]

Yes, I just realised my stupid mistake 2 minutes ago! and the original IF that I should have added was just as obvious - It took me a week to figure this macro out and it was a year ago so I'm a bit rusty!

Thank you