Excel formula or vba? distribution of given amounts of fuel?

my English isn't good enough.

I have TWO reservoires R-1&R-2 of fuel. I need to have AROUND 10% more in R-1 after delivery. In cell A1 is 0,1 as 10%. Truck/Lorry/tanker have 30,000 of fuel with 5 section in which is 6,000 litres each. Only ONE section can be dropped in ONE reservoire not in both! In cells G7:G11 are amount of each section of tanker. In cells E2:E3 are current amount of fuel in reservoires before delivery. In cells E7:E11 I need formula to give me result as "R-1" or "R-2" for each section of tanker. I repeat, only ONE section can be delivered into one not in both reservoires. Cannot split 6,000. In F2:F3 need total amount of delivered plus amount before delivery, it must be R-1 > R-2 for AROUND cell A1 value. It is EASY when there E2:E3 are 0, reservoires was empty before delivery, then we would drop section 1,2 and 3(18,000 total) to R-1 and sections 4 and 5(12,000 total) to R-2 reservoire and total amount of reservoires would be R-1 AROUND 10% > than R-2. There can be sections of tanker with empty sections, eg. 24,000 litres, sections 1-4 are 6,000 litres and s5 is empty, Or other way around. Thanky you very much for your time and interest!!

Sub RasponKomora()
    Dim kolicinaCisterne As Double
    Dim kolicineKomora() As Double
    Dim rezervoar1 As Double
    Dim rezervoar2 As Double
    Dim razlika As Double
    Dim postotak As Double
    
    kolicinaCisterne = Range("G6").Value
    kolicineKomora = Range("G7:G11").Value  <<<<<<<<<run-time 13 type mismatch errorrr
    rezervoar1 = Range("E2").Value
    rezervoar2 = Range("E3").Value
    postotak = Range("A1").Value
    
    razlika = (rezervoar1 - rezervoar2) * (1 + postotak)
    
    For i = 1 To 5
        If kolicinaCisterne > 0 Then
            If kolicineKomora(i, 1) <= razlika Then
                rezervoar1 = rezervoar1 + kolicineKomora(i, 1)
                razlika = razlika - kolicineKomora(i, 1)
                Range("e7").Offset(i, 0).Value = "R-1"
            ElseIf kolicineKomora(i, 1) <= kolicinaCisterne Then
                rezervoar1 = rezervoar1 + kolicinaCisterne
                razlika = razlika - kolicinaCisterne
                Range("e7").Offset(i, 0).Value = "R-1"
            Else
                rezervoar1 = rezervoar1 + razlika
                razlika = 0
                Range("e7").Offset(i, 0).Value = "R-1"
            End If
            kolicinaCisterne = kolicinaCisterne - kolicineKomora(i, 1)
        Else
            Range("e7").Offset(i, 0).Value = ""
        End If
    Next i
    
    Range("F2").Value = rezervoar1 - Range("E2").Value
    Range("F3").Value = rezervoar2 - Range("E3").Value
End Sub

Tried ChatGPT tried numerous excel forums...
I just need to have more fuel in R-1

Hello,

Based on your description, you need a formula to distribute the sections of the tanker between two reservoirs (R-1 and R-2) in such a way that the total amount of fuel in R-1 is approximately 10% more than in R-2, considering the current amounts in cells E2:E3.

You also need a formula in cells E7:E11 to determine which reservoir each section should be delivered to, ensuring that each section is delivered to only one reservoir.

Additionally, you need the total amounts of fuel in R-1 and R-2, including the amounts before delivery, in cells F2:F3.

I understand that the sections cannot be split, and only one section can be delivered to each reservoir.

Please note that the formula for achieving an exact 10% difference may not be feasible in all cases due to the constraints of whole sections and the need to deliver them to only one reservoir. However, we can create a formula that will distribute the sections to achieve an approximate 10% difference.

I hope this clarifies the context of your question. If you have any specific queries or if there's anything else you'd like to add, please let me know.

Regenerate response

Distribution of Fuel to Two Reservoirs

• This doesn't account for more fuel in the tanker than the reservoirs can hold indicated by Tim Williams in the comments.
• Also, this uses the following condition:
  In reservoir 1 there must be at least 10% more fuel than in reservoir 2.
  Keep in mind that this cannot be achieved if e.g. reservoir 1 is empty and reservoir 2 has 30,000 liters.

<!-- language: lang-vb -->

Option Explicit
Sub CalculateCompartmentsPerReservoir()
' s - Source
Const SRC_COMPARTMENTS As String = "G7:G11"
Const SRC_RESERVOIRS As String = "E2:E3"
Const SRC_PERCENTAGE As String = "A1"
' d - Destination (Result)
Const DST_COMPARTMENTS_COLUMN As String = "F"
Const DST_RESERVOIRS_COLUMN As String = "F"
Const DST_ABBREVIATION_1 As String = "R-1"
Const DST_ABBREVIATION_2 As String = "R-2"
Const DST_EMPTY_RESERVOIR As String = "Empty"
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
' c - Compartments (Komore)
Dim dcrg As Range, scData(), CompartmentsCount As Long
With ws.Range(SRC_COMPARTMENTS)
CompartmentsCount = .Rows.Count
scData = .Value
Set dcrg = .EntireRow.Columns(DST_COMPARTMENTS_COLUMN)
End With
' r - Reservoirs (Rezervoari)
Dim drrg As Range, srData(), ReservoirsCount As Long
With ws.Range(SRC_RESERVOIRS)
ReservoirsCount = .Rows.Count
srData = .Value
Set drrg = .EntireRow.Columns(DST_RESERVOIRS_COLUMN)
End With
' Ratio (Omjer)
Dim Ratio As Double: Ratio = 1
With ws.Range(SRC_PERCENTAGE)
If VarType(.Value) = vbDouble Then
Ratio = Ratio + .Value
End If
End With
' Destination Arrays
Dim drData() As Long: ReDim drData(1 To ReservoirsCount, 1 To 1)
Dim dcData() As String: ReDim dcData(1 To CompartmentsCount, 1 To 1)
' Loop 1
Dim CompartmentFuel, n As Long, WouldBeReservoir2Fuel As Long
For n = 1 To CompartmentsCount
CompartmentFuel = scData(n, 1)
If VarType(CompartmentFuel) = vbDouble Then
WouldBeReservoir2Fuel = srData(2, 1) + CompartmentFuel
If srData(1, 1) < WouldBeReservoir2Fuel * Ratio Then
srData(1, 1) = srData(1, 1) + CompartmentFuel
drData(1, 1) = drData(1, 1) + 1
Else
srData(2, 1) = WouldBeReservoir2Fuel
drData(2, 1) = drData(2, 1) + 1
End If
Else
scData(n, 1) = 0
dcData(n, 1) = DST_EMPTY_RESERVOIR
End If
Debug.Print n, srData(1, 1), srData(2, 1), WouldBeReservoir2Fuel
Next n
' Loop 2
' The fuel in the 1st compartments will be used to fill the 1st reservoir.
' Switch the expressions in the inner If statement
' to fill the 2nd reservoir with the fuel of the 1st compartments.
For n = 1 To CompartmentsCount
If Len(dcData(n, 1)) = 0 Then
If drData(1, 1) > 0 Then
dcData(n, 1) = DST_ABBREVIATION_1
drData(1, 1) = drData(1, 1) - 1
ElseIf drData(2, 1) > 0 Then
dcData(n, 1) = DST_ABBREVIATION_2
drData(2, 1) = drData(2, 1) - 1
End If
End If
Debug.Print n, drData(1, 1), drData(2, 1), dcData(n, 1)
Next n
' Write to the worksheet.
drrg.Value = srData
dcrg.Value = dcData
' Inform.
MsgBox "Compartments per reservoir calculated.", vbInformation
End Sub

 Dim kolicineKomora() As Double

change this to:<code>Dim kolicineKomora As variant</code>

then <code>kolicineKomora = Range("G7:G11").Value</code> will have no error.

Otherwise if you prefer to use array,try this:

Dim kolicineKomora() As Double
Dim i As Long
Dim array_index As Long  'for calculating items in array
array_index = 0
For i = 7 To 11
ReDim Preserve kolicineKomora(array_index)
kolicineKomora(array_index) = Cells(i, "G")
array_index = array_index + 1
Next i
'print the items in kolicineKomora()
For i = LBound(kolicineKomora) To UBound(kolicineKomora)
Debug.Print i; kolicineKomora(i)
Next i
Erase kolicineKomora