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

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Forum statistics

Threads
1,141,095
Messages
5,704,311
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top