Having an And in the outcome of an If statement. . .

[johngio]

Hi all,

Having a bit of a problem with my macro. I'm trying to add the condition that if the value in a column is zero, then I would like to place the difference between the cells above and the Y Row in the J column, which would look something like:
Cells(iRow,10)=Cells(iRow-1,25)-Cells(iRow-1,iCol)

Here is my macro.

Sub Min()
Dim LastRow As Integer, LastCol As Integer, GRow As Integer

'G row
i = 16
Do
i = i + 1
Loop Until Cells(i, 7) > 0
GRow = Cells(i, "G").Row

'Y or Z
Set findit = Range("Y:Z").Find(what:="*", searchDirection:=xlPrevious, after:=Range("Z1"))
roww = findit.Row + 1
Do
roww = roww - 1
Loop Until Cells(roww, "Y") > 0 Or Cells(roww, "Z") > 0

LastRow = roww
If Cells(LastRow, "Y") > 0 Then
LastCol = 25
Else
LastCol = 26
End If

For iRow = GRow To LastRow
For iCol = 7 To 9

Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range(Cells(iRow - 1, 7), Cells(iRow - 1, 9))

Cells(iRow - 1, 1) = Application.WorksheetFunction.Min(myRange)

If Cells(iRow, iCol) <> "" Then
Cells(iRow, iCol) = Cells(iRow, iCol)

ElseIf Cells(iRow - 1, iCol).Value = Cells(iRow - 1, 1).Value And Cells(iRow - 1, iCol) <> 0 And Cells(iRow, 25).Value <> 0 And Cells(iRow - 1, iCol).Value - Cells(iRow - 2, iCol).Value <> Cells(iRow - 1, 17).Value And Cells(iRow - 1, iCol).Value > Cells(iRow, 25).Value Then
Cells(iRow, iCol) = Cells(iRow - 1, iCol) - Cells(iRow, 25)
ElseIf Cells(iRow - 1, iCol).Value = Cells(iRow - 1, 1).Value And Cells(iRow - 1, iCol) <> 0 And Cells(iRow, 25).Value <> 0 And Cells(iRow - 1, iCol).Value - Cells(iRow - 2, iCol).Value <> Cells(iRow - 1, 17).Value And Cells(iRow - 1, iCol).Value < Cells(iRow, 25).Value Then
Cells(iRow, iCol) = 0
ElseIf Cells(iRow - 1, iCol) = 0 Then
Cells(iRow, iCol) = Cells(iRow, iCol)
ElseIf Cells(iRow - 1, iCol) - Cells(iRow - 2, iCol) = Cells(iRow - 1, 17) And Cells(iRow - 1, iCol).Value < 100000 Then
Cells(iRow, iCol) = Cells(iRow - 1, iCol).Value + Cells(iRow, 17).Value
Else:
Cells(iRow, iCol) = Cells(iRow - 1, iCol)
End If

Next iCol
Next iRow
End Sub

Basically, I would like to know if it is possible to place the outcome on an If statement as containing an And, such as

ElseIf Cells(iRow - 1, iCol).Value = Cells(iRow - 1, 1).Value And Cells(iRow - 1, iCol) <> 0 And Cells(iRow, 25).Value <> 0 And Cells(iRow - 1, iCol).Value - Cells(iRow - 2, iCol).Value <> Cells(iRow - 1, 17).Value And Cells(iRow - 1, iCol).Value < Cells(iRow, 25).Value Then
Cells(iRow, iCol) = 0 And Cells(iRow,10)=Cells(iRow-1,25)-Cells(iRow-1,iCol)

Any infortmation would be appreciated.

Thanks

John

 

[johngio]

Got it