If And Funtions

[bopit87]

Hi,

Would anyone know what the problem is with this function? I basically want it to pick out the correct size vessel depending on the annual usage.

Thank you

Sub Sizes()
    Dim VesselName(5) As String
    Dim MinDailyUse(5) As Integer
    Dim AnnualUse As Integer
    AnnualUse = Range("e1")

    VesselName(0) = "a"
    VesselName(1) = "b"
    VesselName(2) = "c"
    VesselName(3) = "d"
    VesselName(4) = "e"
    VesselName(5) = "f"

    MinDailyUse(0) = 3.5
    MinDailyUse(1) = 7
    MinDailyUse(2) = 14
    MinDailyUse(3) = 22
    MinDailyUse(4) = 30
    MinDailyUse(5) = 38
 
    For i = 0 To MinDailyUse(i) - 1
        If (AnnualUse >= MinDailyUse) And AnnualUse < MinDailyUse(i + 1) Then
            Range("f1").Value = VesselName
        End If
    Next

End Sub

 

[jmcilhinney]

Firstly, I have formatted your code properly. Please see that you do so for us in future as it makes reading the code and therefore solving the issue so much easier.

Secondly, it might be a good idea to explain what you expect to happen and what actually happens, otherwise we have no idea of what we're looking for.

That said, I'm guessing that the issue is here:

For i = 0 To [B][U]MinDailyUse(i) - 1[/U][/B]

Presumably you actually meant:

For i = 0 To [B][U]MinDailyUse.Length - 1[/U][/B]

or, preferably:

For i = 0 To [B][U]MinDailyUse.GetUpperBound(0)[/U][/B]

 

[bopit87]

Thank you, would you be able to help me with this part? I have tried to add in a message box so that max daily allowances are accounted for. But I keep getting an out of range for the Minimum daily allowance once I use (i+1)

Sub TankSize()
Dim VesselName(5) As String
Dim MinDailyUse(5) As Integer
Dim MaxDailyUse(5) As Integer
Dim AnnualUse As Integer
Dim i As Integer

AnnualUse = Range("e1")

VesselName(0) = "a"
VesselName(1) = "b"
VesselName(2) = "c"
VesselName(3) = "d"
VesselName(4) = "e"
VesselName(5) = "f"

MinDailyUse(0) = 3.5
MinDailyUse(1) = 7
MinDailyUse(2) = 14
MinDailyUse(3) = 22
MinDailyUse(4) = 30
MinDailyUse(5) = 38

MaxDailyUse(0) = 8
MaxDailyUse(1) = 16
MaxDailyUse(2) = 24
MaxDailyUse(3) = 32
MaxDailyUse(4) = 40
MaxDailyUse(5) = 48

If (AnnualUse >= MinDailyUse(0)) And (AnnualUse < MinDailyUse(1)) Then
Range("g1").Value = VesselName(0)
End If

For i = 1 To Ubound(MinDailyUse)
If (AnnualUse >= MinDailyUse(i)) And (AnnualUse < MinDailyUse(i + 1)) And AnnualUse <= MaxDailyUse(i - 1) Then
Range("g1").Value = VesselName(i)
MsgBox "Please be aware that Tank Size " & VesselName(i - 1) & " can be used up to a capacity of " & MaxDailyUse(i - 1) & " metres cubed."
End If
Next

End Sub

 

[bopit87]

I basically want to enter an annual use value, and then go through the minimum and maximum values to decide on a corresponding vessel.

So if the annual usage is greater than or equal to min usage (1) but less than min usage (2) then the vessel name (1) will be entered into the cell.

But if the annual usage is greater than or equal to min usage (1) but less than max usage (0) i want a message box to pop up showing that the option is there to pick vessel name (0).

 

[jmcilhinney]

Please copy your code from the IDE and paste it as plain text into the formatting tags. Your latest code snippet is easier to read than before but still lacks indenting, which is the most important factor in making code readable.

The issue is fairly obvious:

For i = 1 To [B][U]Ubound(MinDailyUse)[/U][/B]
    If (AnnualUse >= MinDailyUse(i)) And (AnnualUse < [B][U]MinDailyUse(i + 1)[/U][/B]) And AnnualUse <= MaxDailyUse(i - 1) Then

If i is equal to the upper bound then how can you get the element at index (i + 1)?

 

[bopit87]

But even if i write

for i = 1 to MinDailyUse (4) the code still wont work.

(And sorry i still dont inderstand what you mean by copying and pasting)

 

[jmcilhinney]

But even if i write

for i = 1 to MinDailyUse (4) the code still wont work.

(And sorry i still dont inderstand what you mean by copying and pasting)

And who said anything about doing that? Use some logic. If you want to use (i + 1) inside the loop then the loop has to go to one less than the last index.