What I have:
I have values in cells C2:G601
That's 600 rows of 5 cells in each row
The values in the cells come from 43 product ID numbers. Ex, AB1, AB2, CR5, etc.
What I humbly request:
A macro (For Each? doesn't have to be) comparing (=) each cell.value to the cells.values above it, as far above as it needs to go within the range Cells (3, 3) , Cells (LastRow, 7) to find an equal value.
On the first match of equal cell.value
count the rows between each cell value
Offset the Row Count results to Cells (0, 10) For Each cell
If no equal value is found for a cell, then leave the Row Count result in cells (0, 10) blank or " " and go to next cell.
Some cells values will equal another cell value in the row above it meaning there is no row count, the Row Count results should be 0 or "0"
Stop finding equal cell.values after the first match is found and row count between cell.values has been counted and results offset, just go to the next cell
ChatGPT Attempt:
The macro above leaves all result cells blank except any cell that has an equal value in the first row above it. The macro gives the result as -1,
it should be 0, since there are no rows to count between equal value cells. It only looks for a match in the row above it when there is a match 2 or more rows above it.
Can someone be kind enough to correct the AI?
I have values in cells C2:G601
That's 600 rows of 5 cells in each row
The values in the cells come from 43 product ID numbers. Ex, AB1, AB2, CR5, etc.
What I humbly request:
A macro (For Each? doesn't have to be) comparing (=) each cell.value to the cells.values above it, as far above as it needs to go within the range Cells (3, 3) , Cells (LastRow, 7) to find an equal value.
On the first match of equal cell.value
count the rows between each cell value
Offset the Row Count results to Cells (0, 10) For Each cell
If no equal value is found for a cell, then leave the Row Count result in cells (0, 10) blank or " " and go to next cell.
Some cells values will equal another cell value in the row above it meaning there is no row count, the Row Count results should be 0 or "0"
Stop finding equal cell.values after the first match is found and row count between cell.values has been counted and results offset, just go to the next cell
ChatGPT Attempt:
VBA Code:
Sub CountRowsBetweenEqualCells()
'Define variables
Dim rng As Range
Dim r As Long
Dim c As Long
Dim lastRow As Long
'Set range
Set rng = Range("C2:G601")
'Loop through each row
For r = 1 To rng.Rows.Count
'Loop through each cell in row
For c = 1 To rng.Columns.Count
'Check if value of current cell is equal to value of cell above
If rng.Cells(r, c).Value = rng.Cells(r - 1, c).Value Then
'If equal, calculate rows between current cell and cell above
lastRow = r - r - 1
'Offset results to column 10 for each cell
rng.Cells(0, 10).Offset(r, c).Value = lastRow
Else
'If not equal, set results to blank or ""
rng.Cells(0, 10).Offset(r, c).Value = ""
End If
Next c
Next r
End SubThe macro above leaves all result cells blank except any cell that has an equal value in the first row above it. The macro gives the result as -1,
it should be 0, since there are no rows to count between equal value cells. It only looks for a match in the row above it when there is a match 2 or more rows above it.
Can someone be kind enough to correct the AI?
