If function value for cell with text boxes

0
votes

I have two text boxes here. The first has the value 1 to 10 and the second 1 to 2. Depending on the combination of values in the text boxes, I want the cell e. g. E6 to take on a certain value from another sheet. I have tried to approach this step by step, but I can't find a useful solution.

The text boxes:

enter image description here

The possible value combinations from another sheet:

enter image description here

e.g.

TextBox1 value = 1 and TextBox2 value = 1 -> Cell E6 = 3,25

TextBox1 value = 2 and TextBox2 value = 2 -> Cell E6 = 13

TextBox1 value = 3 and TextBox2 value = 2 -> Cell E6 = 19,50 and so on

basic idea that has not worked so far:

Sub Test()
    
    If TextBox1.Value = ("1") And TextBox2.Value = ("1") Then
    Range("E6").Value = 3.25
    End If
    
    
End Sub

maybe someone has a much better solution for this instead of the if function.

2
excelvba

2 Answers

1
votes

Try the next way, please:

If Me.TextBox1.Value = 1 And Me.TextBox2.Value = 1 Then
    Range("E6").Value = 3.25
End If

It will change the range value of the **active worksheet""...

0
votes

Qncy,

If you're willing to change your data table format as follows: enter image description here

Then you can use this function to fill in your answers:

Option Explicit

Function GetValue() As Double

'Function Call: =GetValue() in the cell where you want the answer.

  Dim rng As Range
  
  Application.Volatile
  
  Set rng = Application.Caller()
  
  GetValue = Sheets("Data").Range("$A$2").Offset(rng.Offset(0, -2), rng.Offset(0, -1))
  
End Function

Of course you'll substitute the textbox values for my .Offset(rng.Offset(0, -2), rng.Offset(0, -1) above, as well as the sheet name, e.g. "Data"

I filled in this table using the function in column C:

enter image description here

HTH

Ok, here's an example with TextBoxes. I've used the LinkedCell property to link the value into the cell behind the textbox to make referencing easier.

Option Explicit

Function GetValue() As Double

  Dim rng As Range
  Dim wks As Worksheet
  
  Application.Volatile
  
  Set rng = Application.Caller()
  Set wks = Sheets("Boxes")
  
  GetValue = Sheets("Data").Range("$A$2").Offset(wks.Cells(1, 1), wks.Cells(1, 2))
  
End Function 'GetValue()

Workbook Setup: enter image description here

Here's a link so you can download the test file and see how things are setup: