Specifying cell addresses in Excel formula arguments using VBA

I have A column and B column,开发者_JS百科 They compare their previous values And Iam comparing it with the following formulae

    =IF((OR(AND(A2=A1,B2=B1),K2=0),0,1) it puts the 0 or 1 on the coresponding Q column

so when it goes to the 5th cell then it becames

    =IF((OR(AND(A5=A4,B5=B4),K5=0),0,1)

But im trying to apply it in my VBA code like these

  For numm = 2 To lastRow
  Sheet1.Cells(numm, "Q").Value = ="IF(OR(AND(sheet1.cells(numm,""A"").value=sheet1.cells(numm-1,""A"")simlar way becolumn),sheet1.cells(numm,""k"").value=0),1,0)"
  Next numm

But Im unable to peform the action it says 1004 error and object error How do i use cells(numm,"A") in my VBA formulae or atleast any other way to put my formula and make it work

The reference to the looping numm within your formula needs to be out of the string.

Maybe you can set in VBA the cell formula itself...

For numm = 2 To lastRow

    Sheet1.Cells("Q" & numm).Formula = _
         "=IF((OR(AND(A" & numm & "=A" & numm - 1 & ",B" & numm & _
         "=B" & numm - 1 & "),K" & numm & "=0),0,1)"

Next numm

Personally, I'd do the whole statement within VBA (ifs, ors, ands) and just drop the value back to Excel. Using Excel formulas makes the code harder to read.

Are you looking for something like:

Public Sub test()
    Dim Sheet1 As Excel.Worksheet
    Set Sheet1 = ActiveSheet
    Dim numm As Integer, lastrow As Integer
    lastrow = 5

    For numm = 2 To lastrow
        Sheet1.Cells(numm, "Q").Value = "=IF(OR(AND(" & Sheet1.Cells(numm, "A").Address & "=" & Sheet1.Cells(numm - 1, "A").Address & "," & Sheet1.Cells(numm, "B").Address & "=" & Sheet1.Cells(numm - 1, "B").Address & ")," & Sheet1.Cells(numm, "k").Address & "=0),1,0)"
    Next numm

End Sub

Sheet1 can't be referred to inside the value of the cell, it only exists in the vba function you're creating, so instead you can append the strings together, and just get the cell addresses out to match the sort of function you were creating earlier.