IF formula in VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-2007402%22%20slang%3D%22en-US%22%3EIF%20formula%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2007402%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20wanting%20to%20know%20if%20I%20can%20do%20an%20IF%20statement%20in%20VBA.%20I%20tried%20to%20research%20on%20how%20to%20do%20it%20but%20nothing%20works.%20I%20need%20a%20formula%20that%20states%20if%20F49%3AJ50%20says%20%22Cont...%22%20then%20to%20input%20a%20zero%20if%20not%20then%20leave%20the%20number%20value%20the%20same.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20pull%20data%20from%20a%20different%20program%2C%20it%20will%20pull%20the%20data%20from%20a%20reference%20point.%20So%20if%20123%2C456%20is%20in%20the%20reference%20point%2C%20then%20I%20want%20it%20to%20input%20it%20into%20the%20cell%20and%20I%20want%20it%20to%20keep%20it%20there.%20But%20if%20it%20pulls%20%22Cont...%22%20from%20the%20reference%20point%20then%20I%20want%20it%20to%20input%20a%200%20(zero)%20in%20the%20cell.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2007402%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2007583%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20formula%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2007583%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F888027%22%20target%3D%22_blank%22%3E%40catherine9910%3C%2FA%3E%26nbsp%3BVBA%20can%20absolutely%20do%20this%20but%20why%3F%26nbsp%3B%20If%20you%20are%20asking%2C%20I%20suspect%20that%20you%20aren't%20too%20familiar%20with%20VBA%20so%20a%20much%20easier%20solution%20would%20be%20to%20use%20an%20in-cell%20formula.%26nbsp%3B%20Simply%20insert%20another%20rows%2Fcolumns%20or%20use%20a%20set%20of%20empty%20cells%20and%20type%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(ISNUMBER(F49%3AJ50)%2CF49%3AJ50%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eor%20more%20accurate%20to%20your%20original%20request%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(F49%3AJ50%3D%22Cont...%22%2C0%2CF49%3AJ50)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ENote%20if%20these%20'array'%20functions%20don't%20work%20for%20you%20just%20replace%26nbsp%3B%3CSPAN%3EF49%3AJ50%20with%20F49%20and%20then%20copy%2Fdrag%20that%20cell%20down%20and%20then%20over%20to%20cover%20the%20corresponding%20range.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ELastly%2C%20if%20you%20really%20need%20to%20replace%20the%20original%26nbsp%3B%20data%20then%20you%20can%20copy%20the%20new%20data%20and%20then%20paste%20special%20-%26gt%3B%20values%20only%20on%20top%20of%20the%20original%20data%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2024962%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20formula%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2024962%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F888027%22%20target%3D%22_blank%22%3E%40catherine9910%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20Condition%20Then%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Do%20this%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I am wanting to know if I can do an IF statement in VBA. I tried to research on how to do it but nothing works. I need a formula that states if F49:J50 says "Cont..." then to input a zero if not then leave the number value the same.

 

When I pull data from a different program, it will pull the data from a reference point. So if 123,456 is in the reference point, then I want it to input it into the cell and I want it to keep it there. But if it pulls "Cont..." from the reference point then I want it to input a 0 (zero) in the cell.

2 Replies

@catherine9910 VBA can absolutely do this but why?  If you are asking, I suspect that you aren't too familiar with VBA so a much easier solution would be to use an in-cell formula.  Simply insert another rows/columns or use a set of empty cells and type 

=IF(ISNUMBER(F49:J50),F49:J50,0)

or more accurate to your original request

=IF(F49:J50="Cont...",0,F49:J50)

Note if these 'array' functions don't work for you just replace F49:J50 with F49 and then copy/drag that cell down and then over to cover the corresponding range.

Lastly, if you really need to replace the original  data then you can copy the new data and then paste special -> values only on top of the original data

@catherine9910 

If Condition Then
          Do this
End If