locked
VBA formulas RRS feed

  • Question

  • How do I express the following formula in VBA code:

    Cell Z3 =If(And(E3<>0,H3<>0),1,0)

    Copy above in the range Z4:Z10000

    Thank you

    Sunday, January 6, 2019 2:54 PM

All replies

  • Probably like this:

     

        Dim E3 As Double

        Dim H3 As Double

       

        E3 = . . . ' some value

        H3 = . . . ' some value

       

        Dim Z3 As Boolean

        Z3 = E3 <> 0 And H3 <> 0

       

    Or

        Dim Z3 As Integer

        Z3 = IIf(E3 <> 0 And H3 <> 0, 1, 0)

    Sunday, January 6, 2019 4:32 PM
  • [Z3:Z10000].FormulaR1C1="=IF(AND(RC5<>0,RC8<>0),1,0)"

    Tuesday, January 8, 2019 10:52 AM
  • There are multiple ways you can get the desired output.

    the simplest one is given below 

        ActiveSheet.Range("Z3").Select
        ActiveCell = "=If(And(E3<>0,H3<>0),1,0)"
        Selection.Copy
        Application.Goto Reference:="R10000C26" ' goto 10000th row col 26(Z)
        Range(Selection, Selection.End(xlUp)).Select
        ActiveSheet.Paste
        
    Monday, January 14, 2019 11:47 AM