What distinguishes a Balance row from other rows?
Is the word
Balance in column A for all of them?
Is there a formula in a particular column for balance rows, but not for other rows?
Start with this:
Code:
Option Explicit
Sub InsertRowBelowNegativeEntriesInFGHI()
Dim lLastColRow As Long
Dim lLastRow As Long
Dim lColIndex As Long
Dim lRowIndex As Long
Dim bIsNegative As Boolean
Dim bIsBalanceRow As Boolean
For lColIndex = 6 To 10
lLastColRow = Cells(Rows.Count, lColIndex).End(xlUp).Row
If lLastColRow > lLastRow Then lLastRow = lLastColRow
Next
For lRowIndex = lLastRow - 1 To 2 Step -1
bIsBalanceRow = True 'Change this to set bIsBalanceRow to True if lRowIndex is a Balance Row
bIsNegative = False
For lColIndex = 6 To 10
If Cells(lRowIndex, lColIndex).Value < 0 Then
bIsNegative = True
Exit For
End If
Next
If bIsBalanceRow And bIsNegative Then
If Cells(lRowIndex + 1, 6) & Cells(lRowIndex + 1, 7) & _
Cells(lRowIndex + 1, 8) & Cells(lRowIndex + 1, 9) <> vbNullString Then
'If FGHI in the row below the negative value is not blank
Cells(lRowIndex + 1, 1).EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
End If
End If
bIsBalanceRow = False
Next
End Sub
Hello pbornemeier, the word Balance is there in Column A & yes there is a formula for balance rows but not for others
I have a excel sheet where there are many Balance rows. In Column A there are Demand Collection Balance Rows, where Balance = Demand - Collection.
So there are multiple Balance Rows are there in that sheet. Column FGHIJ are inter related.
I want a macro that if in any Balance row there are negative value in FGHI column followed by any positive value a new row will be insert above the balance row.
for example
1. there is a negative value in F column -100 and positive value in either G or H or I or J column
2. there is a negative value in G column -100 and positive value in either H or I or J column
3.there is a negative value in I column -100 and positive value in either I or J column
4.there is a negative value in I column -100 and positive value in J column
In the above situation a blank row should be insert over the Balance row
If there is any positive value in F column but negative value in G column then row should not be insert.
example 40 -100 0 0 0 in FGHIJ column
Another example 40 -100 20 30 40
Here a new row should be inserted because the negative value is followed by positive values in the column
A new row should insert only if a negative value is followed by a positive value in in F G H I J column serially from left to right.
I want a VBA macro to insert rows above the Balance Row where the condition fulfills
Kindly check the image to understand better, in the blue marked columns a new row should insert as it fulfills the criteria but in the yellow marked columns it should not as the negative value doesn't followed by a positive value.
With your code a Blank row inserts but when it is followed by a zero then also it inserts a blank row, it should be insert when if any value having value 0+
