Chat GPT Case 1 Eng

(Machine translation)

Final result of the query: “I need an Excel script. The script scans column C in the Arkusz1 sheet from the row containing the word START up to the keyword STOP. The column has repeating strings. Upon detecting a string that is different from the previous one, excluding the keywords START and STOP, the script inserts an empty row above. At the same time, it calculates the sum of cell values in column G for the block created in this manner, characterized by the same values in column C, and writes this value in the last row of the block in column N.”

Achieving this result took me about three hours and about 12 iterations with ChatGPT, refining the question, correcting errors, auxiliary questions, etc. I must admit that the impressions from interacting with this tool are one of a kind. I would say it cooperates better than a human.

• What you write to it, it understands literally.
• It doesn’t ask silly questions.
• It doesn’t get annoyed when I change my mind or ask for the same thing again.
• It doesn’t ask “why do you need this.”
• Overall, an “oasis of patience” 🙂

The code:

Sub CreateBlocksAndCalculateSums()
Dim rng As Range
Dim i As Long
Dim sumStart As Long
Dim sumG As Double, sumH As Double
Dim startRow As Long
Dim endRow As Long
Dim lastVal As Variant

‘ Znajdź początek i koniec zakresu
startRow = Application.Match(“START”, ThisWorkbook.Sheets(“Arkusz1”).Range(“D:D”), 0) + 1
endRow = Application.Match(“STOP”, ThisWorkbook.Sheets(“Arkusz1”).Range(“D:D”), 0) – 1

‘ Ustaw zakres od START do STOP w kolumnie C do przeglądania
Set rng = ThisWorkbook.Sheets(“Arkusz1”).Range(“C” & startRow & “:C” & endRow)

‘ Ustal początek sumy
sumStart = startRow

‘ Przechowaj ostatnią przetworzoną wartość
lastVal = rng.Cells(1, 1).Value

‘ Przeszukaj zakres od góry do dołu
i = 2
While i <= rng.Rows.Count
‘ Jeżeli napotkasz inny string, wstaw pusty wiersz
If rng.Cells(i, 1).Value <> lastVal Then
‘ Oblicz sumę wartości z kolumny G i dodaj do ostatniego wiersza bloku w kolumnie N
sumG = Application.WorksheetFunction.Sum(Range(“G” & sumStart & “:G” & startRow + i – 2))
Cells(startRow + i – 2, “N”).Value = sumG
‘ Oblicz sumę wartości z kolumny H i dodaj do ostatniego wiersza bloku w kolumnie O
sumH = Application.WorksheetFunction.Sum(Range(“H” & sumStart & “:H” & startRow + i – 2))
Cells(startRow + i – 2, “O”).Value = sumH
‘ Zresetuj sumStart do aktualnego wiersza
sumStart = startRow + i
rng.Rows(i).EntireRow.Insert
‘ Odśwież zakres po wstawieniu pustego wiersza
Set rng = ThisWorkbook.Sheets(“Arkusz1”).Range(“C” & startRow & “:C” & endRow + i)
‘ Zaktualizuj ostatnią wartość
lastVal = rng.Cells(i + 1, 1).Value
End If
i = i + 1
Wend

‘ Oblicz sumę dla ostatniego bloku po wykonaniu pętli
sumG = Application.WorksheetFunction.Sum(Range(“G” & sumStart & “:G” & startRow + rng.Rows.Count – 1))
Cells(startRow + rng.Rows.Count – 1, “N”).Value = sumG
sumH = Application.WorksheetFunction.Sum(Range(“H” & sumStart & “:H” & startRow + rng.Rows.Count – 1))
Cells(startRow + rng.Rows.Count – 1, “O”).Value = sumH

End Sub