VBA IF AND Statement

[hobbiton73]

Hi, I wonder if someone may be able to help me please.

I'm trying to put together the following formula in VBA: =IF(AND($C3<>$D3,$B3="B&C"),$D3-$C3,0), but I'm having a little difficulty in doing so.

I've put together the following code snippet which will add the calculation to each row within a given range to the last used row.

Please Login or Register  to view this content.

But Debug shows a 'Compile' error.

I just wondered whether someone could possibly look at this please and let me know where I'm going wrong.

Many thanks and kind regards

[Olly]

It's failing because of single quotation marks. Use:

Please Login or Register  to view this content.

[Andy Pope]

you need to double up on the quotes inside quotes.

"=IF(RC3<>RC4 AND RC2=""B&C""),RC4-RC3,0)"

[Olly]

Code corrected for relative ranges in formula, and to apply to multiple cell range:

Please Login or Register  to view this content.

[hobbiton73]

Hi @OllyXLS, thank you very much for taking the time to reply to my post and for the solution, it works perfectly.

All the best and kind regards

[hobbiton73]

Hi Andy, thank you very much for taking the time to reply to my post and for the formula.

I did try the code you kindly provided but the line caused a 'Type Mismatch' error.

Many thanks and kind regards

[Andy Pope]

I assume you also corrected the invalid formula syntax in your orginal code? I was just pointing out the lack of double quotes.

Please Login or Register  to view this content.

[hobbiton73]

Hi Andy, sorry my mistake.

All the best and kind regards