When you spend enough time around both farms and spreadsheets, you start seeing them the same way – full of moving parts, complex dependencies, and a fragile balance between structure and chaos.
This project started as a simple task tracker for animal health programs. It evolved into a full-blown Excel-based ERP-like system, complete with automated forecasting, daily logging, and VBA-driven workflows for multiple farm sites.
The result – summary interpretation
This file is practically a customized ERP mini-system in Excel that:
- programs and tracks production at the farm level,
- automates data collection and logging,
- predicts yield and performance,
- and connects all data sets (farm, program, hybrid, log).
In other words:
A macro-supported livestock management system built into Excel, covering everything from planning to daily reporting.
Step 1: Defining the Problem
Each farm was managing different hybrids – broilers, layers, growers – all with their own weekly schedules and health control programs. The biggest challenge was centralizing the data while keeping it flexible enough for local use.
I needed one workbook that could:
- Handle multiple farms (telephelyek)
- Store and forecast production programs
- Log daily operational data
- Predict outcomes weeks ahead
So I built _program.xlsm – a macro-enabled Excel system that ties together every stage of the production and monitoring process.
Step 2: Structuring the Workbook
Every sheet had a clear role in the workflow:
What started as a set of templates became a living system that could plan, record, and predict.
Step 3: Automating the Forecast
The core of the logic lives inside one macro:
Since I designed the applicable health programs and the templates for each site with a fixed number of rows, sequential processing was given a serious shortcut. Thus, the data for the 30 sites is processed in seconds. The weekly forecast sorts the tasks to be completed and the tasks that have not been completed according to the number of weeks set on the Helper worksheet, starting from the current week.
‘ Visual Basic
Sub elorejelzes()
Application.ScreenUpdating = False
Application.Calculation = xlManual
' Törlöm az előrejelzés munkalap fejlécen kívüli részét
sh_elorejelzes.Rows("2:5000").Delete
hova_irok = 2
' Hány hetet nézünk előre
hetek = sh_seged.Range("Q3").Value
' a: munkalap ciklus
For A = 1 To 2
If A = 1 Then
Set ws = sh_prog_novendek_telep
meddig = 1363
tipus = "növendék"
Else
Set ws = sh_prog_tojo_telep
meddig = 683
tipus = "tojó"
End If
hanyadik_het_van = ws.Range("A2").Value
akt_ev = Year(Date)
' b: a munkalapon meddig vannak sorok
For b = 4 To meddig
oF = ws.Range("F" & CStr(b)).Value
' Fejléc meghatározása:
If (oF = "Telephely:") _
Then
szerepel_a_tervezesben = ws.Range("Q" & CStr(b + 2)).Value
If szerepel_a_tervezesben = False _
Then
' Ha nem szerepel a napló a tervezésben, skippelem
b = b + 67
Else
telephely = ws.Range("H" & CStr(b)).Value
If telephely = "" _
Then
' Ha nincs megadva a telephely, skippelem a naplót
b = b + 67
Else
' Van telephely, szerepel a tervben => beolvasom a fejléc többi sorát
prog_mintavetel = ws.Range("H" & CStr(b + 1)).Value
prog_vakcina = ws.Range("H" & CStr(b + 2)).Value
istalo_tol = ws.Range("K" & CStr(b)).Value
istalo_ig = ws.Range("L" & CStr(b)).Value
telepites = ws.Range("K" & CStr(b + 1)).Value
allomany_1 = ws.Range("K" & CStr(b + 2)).Value
allomany_2 = ws.Range("L" & CStr(b + 2)).Value
allomany_3 = ws.Range("M" & CStr(b + 2)).Value
het = ws.Range("M" & CStr(b + 1)).Value
kor = ws.Range("Q" & CStr(b + 1)).Value
'MsgBox telephely
' A napló felső fele a mintavétel
program = "mintavétel"
For c = b + 6 To b + 66
' Innentől vakcina sorok
If c = b + 36 Then program = "vakcina"
akt_het = ws.Range("C" & CStr(c)).Value
prg_het = ws.Range("M" & CStr(c)).Value
teny_datum = ws.Range("Q" & CStr(c)).Value
allapot = ws.Range("P" & CStr(c)).Value
megjegyzes = ws.Range("R" & CStr(c)).Value
' Ezzel a feléc sorokat skippelem
If IsNumeric(akt_het) _
Then
sor_ev = Year(ws.Range("B" & CStr(c)).Value)
kell_a_sor = False
' Ezek már tisztán az adat sorok. A paraméterek alapján döntjük el, hogy szerepel -e az előrejelzésben.
If prg_het >= akt_het And prg_het <= akt_het + hetek Then kell_a_sor = True
If teny_datum <> "" Then kell_a_sor = False
If prg_het <> 0 And prg_het < hanyadik_het_van And teny_datum = "" And sor_ev <= akt_ev Then kell_a_sor = True
If kell_a_sor _
Then
' A fejléc adatok minden sorba bekerülnek:
sh_elorejelzes.Range("A" & CStr(hova_irok)).Value = telephely
sh_elorejelzes.Range("B" & CStr(hova_irok)).Value = istalo_tol
sh_elorejelzes.Range("C" & CStr(hova_irok)).Value = istalo_ig
sh_elorejelzes.Range("D" & CStr(hova_irok)).Value = telepites
sh_elorejelzes.Range("E" & CStr(hova_irok)).Value = "'" & het
sh_elorejelzes.Range("F" & CStr(hova_irok)).Value = allomany_1
sh_elorejelzes.Range("G" & CStr(hova_irok)).Value = allomany_2
sh_elorejelzes.Range("H" & CStr(hova_irok)).Value = allomany_3
sh_elorejelzes.Range("I" & CStr(hova_irok)).Value = prog_mintavetel
sh_elorejelzes.Range("J" & CStr(hova_irok)).Value = prog_vakcina
sh_elorejelzes.Range("K" & CStr(hova_irok)).Value = kor
sh_elorejelzes.Range("L" & CStr(hova_irok)).Value = tipus
'Az adat sorok másolással kerülnek át:
ws.Range("F" & CStr(c) & ":L" & CStr(c)).Copy
sh_elorejelzes.Range("M" & CStr(hova_irok)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
sh_elorejelzes.Range("T" & CStr(hova_irok)).Value = prg_het
sh_elorejelzes.Range("U" & CStr(hova_irok)).Value = allapot
sh_elorejelzes.Range("V" & CStr(hova_irok)).Value = megjegyzes
hova_irok = hova_irok + 1
End If
End If
Next c
b = b + 67
End If
End If
End If
Next b
Next A
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Call elorejelzes_rendezes(hanyadik_het_van)
MsgBox "Végeztem az előrejelzéssel."
End Sub
Step 4: The Sorting Issue
At one point, a simple task – sorting a list of records – became the biggest roadblock.
The macro crashed with:
“Too few arguments to function”
and later:
“Argument #1 ($value) must be of type Countable|array, string given.”
The culprit?
Merged cells. Excel’s sorting engine simply refuses to work on merged ranges.
After some minutes of debugging, I rewrote the routine:
‘ Visual Basic
.SortFields.Clear
.SortFields.Add2 _
Key:=rendezni.Range("L37:L66"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange rendezni.Range("A37:N66")
.Header = xlNo
Once I unmerged the range and used the modern .Add2 method, the problem disappeared.
That fix wasn’t just a patch – it was a reminder: Excel doesn’t forgive structural shortcuts.
Step 5: Connecting It All
After that, the system finally worked seamlessly:
- New programs can be created from templates
- Data flows from daily logs to forecasts
- Reports are updated automatically with one click
- And every farm’s lifecycle is reflected in a single file
It’s not just an Excel file anymore – it’s a data-driven management tool.
What I Learned
- Merged cells are the enemy of automation.
- VBA macros need a clear separation between data, logic, and output sheets.
- Building an ERP-like system in Excel is absolutely possible – if you respect structure.
- Every small bug (like that sorting issue) hides a deeper design lesson.
Final Thoughts
What started as a weekend experiment became a living ecosystem for animal health management.
This project taught me that even inside the “limited” world of Excel, there’s room for real engineering.
You just need to think like a developer – and debug like one too.
Sometimes the best systems aren’t built with frameworks – they’re built with patience, logic, and a few thousand cells of pure intent.Buy me a coffee?
If you enjoyed this story, you can buy me a coffee. You don’t have to – but it means a lot and I always turn it into a new adventure.
Buy a coffee for Steve
Linktree
Short introduction