Executing Excel Formula in VBS

  • I'm novice to scripting. I've written a vbs code which executes the formulas on the excel. However the formula is not getting executed. The code where the issue is coming is highlighted below (within the brackets)


  • Re: Executing Excel Formula in VBS


    This is a question related to VB Script, so I'm not entirely sure about that syntax, but at the very least, this is NOT a valid Excel Formula (a formula that appears in a cell).


    =IF(oSheet.Cells(i,3).Value=oSheet.Cells(i,12).Value,0,1)


    Can you open Excel and type in the correct formula that you would like to see appear in the cell and reply back here and tell us what that formula is and we might be able to help construct the correct script for the VBS....


    Thanks,
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Executing Excel Formula in VBS



    Thanks for the feedback. The excel formula is
    =IF(C2=L2,0,1)

  • Re: Executing Excel Formula in VBS


    Sure, OK.... as I say, I dont know VBS that well, but the VBA for that Excel formula would look like this:

    Code
    For i=2 To row 
        'targetted excel formula is =IF(C2=L2,0,1)
        'using .formula ->
        osheet.Cells(i, 15).Formula = "=IF(" & osheet.Range("C" & i).Address & "=" & osheet.Range("L" & i).Address & ",0,1)"
        
        'or considering using .formulaR1C1 which is shorter (uses relative referencing)
        'osheet.Cells(i, 15).FormulaR1C1 = "=IF(RC[-12]=RC[-3],0,1)"
    Next


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!