Executing Excel Formula in VBS

Puke · Jul 1st 2016

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)

Code

Set xlApp = CreateObject("Excel.Application")
  Set xlBook = xlApp.Workbooks.Open(WScript.Arguments(0) &WScript.Arguments(1), 0, True)
  msgbox "Excel Opened"
  Set oSheet = xlApp.ActiveWorkbook.Worksheets(1)


  row = oSheet.UsedRange.Rows.Count


  for i=2 to row
   oSheet.Cells(i,15).formula = "=IF(oSheet.Cells(i,3).Value=oSheet.Cells(i,12).Value,0,1)"
  next


  xlBook.Save
  xlApp.Quit
  Set oSheet = Nothing
  Set xlBook = Nothing
  Set xlApp = Nothing

Display More

**Ger Plante** · Jul 1st 2016

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

Puke · Jul 1st 2016

Re: Executing Excel Formula in VBS

Quote from Ger Plante;773332

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

Display More

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

**Ger Plante** · Jul 1st 2016

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

Executing Excel Formula in VBS

Participate now!

Share