How to call and interpolate data from a table?

[Mas Juliza Alias]

hi,
I am currently developing a program that contains a table with two columns: 1)Elevation 2)Volume.
There is also a graph of Elevation vs Volume next to the table. 2 text boxes have been placed at the bottom of the graph so that user can enter a value of elevation at textbox1, and the program will call the volume at that elevation from the table and displays it in textbox2. Another concern is on the interpolation of the volume data from the table, if the elevation entered by user is in between the range of elevation in the table, for example
Data in table:
Elevation | Volume
1.000 | 50.000
1.100 | 60.000
1.200 | 70.500
Elevation entered by user: 1.156
Volume difference = (70.500-60.000)*[(1.156-1.100)/(1.200-1.100)] = 5.88
Hence, volume at 1.156 = 60.000 + Volume difference = 60.588

How to code to read the data in the table and apply the above formula to get the volume at the elevation as entered by user?

Thank you in advance.

[Guido Geurs]

Run through the list and when the value in the list > input then the top = value in list and bottom = value with (index-1).
See attachment for code

PS:
Must it not be 65.88 ?
Volume difference = (70.500-60.000)*[(1.156-1.100)/(1.200-1.100)] = 5.88
Hence, volume at 1.156 = 60.000 + Volume difference = 65.88

[Mas Juliza Alias]

You are such an EXPERT! I have applied the code in my program and it works nicely.

However there is one thing that I want to modify but I don't know how. If I enter the highest value, 1.200, and click the Calculate button, the volume does not appear. How to code so that it will appear like we enter the other values of elevation?

Thank YOU

[Guido Geurs]

Check Min. and Max. value in the grid with next code (see attachment):

Q1: what do you mean with "like we enter the other values of elevation" ?
Is it: when it's less then then Min value in the grid, show the min value for the volume and when it's more then the Max value, show the max volume ?

If so, this is the code:

...
   With MSFlexGrid
      LabelMsg.Visible = False
      TextMin.Text = ""
      TextMax.Text = ""
      '§ check minimum
      If Val(TextInput.Text) < .TextMatrix(1, 0) Then
         TextCalc.Text = Format(.TextMatrix(1, 1), "0.000")
         LabelMsg.Caption = "Vallue is less then Minimum !"
         LabelMsg.Visible = True
         Exit Sub
      End If
      '§ check maximum
      If Val(TextInput.Text) > .TextMatrix(.Rows - 1, 0) Then
         TextCalc.Text = Format(.TextMatrix(.Rows - 1, 1), "0.000")
         LabelMsg.Caption = "Vallue is more then Maximum !"
         LabelMsg.Visible = True
         Exit Sub
      End If
      '§ calculate
      For ROWidx = 1 To .Rows - 1
...

[Mas Juliza Alias]

There is no volume appear when I enter 1.200 elevation.

[Guido Geurs]

Fahad Ali,

Please make your own call via "Ask Your Question" in the forum "Microsoft Access / VBA"

[Guido Geurs]

Sorry, my mistake.
This is the solution when you enter a value that is equal to a value in the list:

Private Sub ComCalc_Click()
Dim ROWidx As Integer
Dim ELEVATIONmin As Double
Dim ELEVATIONmax As Double
Dim VOLUMEmin As Double
Dim VOLUMEmax As Double
Dim VOLUMEDIF As Double
   With MSFlexGrid
      LabelMsg.Visible = False
      TextMin.Text = ""
      TextMax.Text = ""
      '§ check minimum
      If Val(TextInput.Text) < Val(.textmatrix(1, 0)) Then
         TextCalc.Text = Format(.textmatrix(1, 1), "0.000")
         LabelMsg.Caption = "Vallue is less then Minimum !"
         LabelMsg.Visible = True
         Exit Sub
      End If
      '§ check maximum
      If Val(TextInput.Text) > Val(.textmatrix(.Rows - 1, 0)) Then
         TextCalc.Text = Format(.textmatrix(.Rows - 1, 1), "0.000")
         LabelMsg.Caption = "Vallue is more then Maximum !"
         LabelMsg.Visible = True
         Exit Sub
      End If
      '§ calculate
      For ROWidx = 1 To .Rows - 1
         If Val(.textmatrix(ROWidx, 0)) = Val(TextInput.Text) Then
            TextCalc.Text = Format(.textmatrix(ROWidx, 1), "0.000")
            Exit Sub
         ElseIf Val(.textmatrix(ROWidx, 0)) > Val(TextInput.Text) Then
            TextMin.Text = .textmatrix(ROWidx - 1, 0)
            TextMax.Text = .textmatrix(ROWidx, 0)
            ELEVATIONmin = Val(.textmatrix(ROWidx - 1, 0))
            ELEVATIONmax = Val(.textmatrix(ROWidx, 0))
            VOLUMEmin = Val(.textmatrix(ROWidx - 1, 1))
            VOLUMEmax = Val(.textmatrix(ROWidx, 1))
            VOLUMEDIF = (VOLUMEmax - VOLUMEmin) * _
               (Val(TextInput.Text) - ELEVATIONmin) / _
                      (ELEVATIONmax - ELEVATIONmin)
            TextCalc.Text = Format(VOLUMEmin + VOLUMEDIF, "0.000")
            Exit Sub
         End If
      Next
   End With
End Sub