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
‘ 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

‘ 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

Skontaktuj siÄ™ z nami

Chcesz dowiedzieć się więcej? Zadzwoń lub wypełnij formularz kontaktowy.

+48 516 125 484