Formula template change to vba code for userform help

[Patriot2879]

Hi I have this formula below which works on a template I have but I want to add this into my userform1.
My range is in sheet called ‘Data’ A1:R17 and the AC3 (Match) is now ComboBox2 and AC4(Match) is now ComboBox1 and I want the result to go into TextBox53

=IFERROR(INDEX($A$1:$R$17,MATCH(AC3,$A$1:$A$17,0),MATCH(AC4,$A$2:$R$2, 0)),"")
[FONT=Times New Roman][/CO[/FONT]DE]


My userformis called ‘UserForm1'.


 

Please canyou help me to put this into vba code to go into TextBox53.

 

ComboBox1 is Dates and ComboBox2 is areas what it is cross referencing to find the number in a cell A1:R17

 

And the A2:R2is the dates which is in ‘Data’ and this corresponds to my combobox1.

 

Hope you can help please.


[/COLOR][/SIZE][/FONT]

 

[Patriot2879]

what it does is matches A2 - R2 with combobox2 and A1 - R1 with combobox1 and cross references, and puts the figure into textbox53. hope this makes sense

 

[Patriot2879]

Hope you can help please with this have tried putting the below code together myself but It is not working

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Private Sub TextBox53_AfterUpdate()[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][SIZE=3][COLOR=#000000]Dim Fnd As Range[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][SIZE=3][COLOR=#000000]Set Fnd =Sheets("Data").Range("A1:R17").Find(ComboBox1.Value, ComboBox2.Value,, xlWhole, , , False, , False)[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][SIZE=3][COLOR=#000000]If Not Fnd IsNothing Then[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]   [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/CO[/COLOR][/SIZE][/FONT]DE]

 

[Patriot2879]

Hi I have tried the code below as well, hope you can help?

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Tahoma"][COLOR=#000000]Private Sub TextBox53_Change()[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT="Tahoma"][COLOR=#000000]Dim myRange As Range
[/COLOR][COLOR=#000000]Sheets("Data").Combobox1.Clear
Set myRange = Sheets("Data").Range("A1:R17")
i = Combobox1.Value
Combobox2.Value = Application.VLookup(i, myRange, 2, False)[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/CO[/COLOR][/SIZE][/FONT]DE]

 

[Patriot2879]

And I have tried...

Option Explicit
"
"   Private Sub TextBox53_Change()
"
"       Dim Stext As String, sFind As String
"
"       With Me
"
"           If .ComboBox1.Value <> vbNullString Then
If .ComboBox2.Value <> vbNullString Then
"               sFind = .ComboBox1.Value
"   sFind = .ComboBox2.Value
"
"               Stext = WorksheetFunction.VLookup(sFind, Data.Range("A1:R17")
"               .TextBox53 = Stext
"           End If
"
"       End With
"
End Sub

[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Times New Roman][SIZE=3][COLOR=#000000][/CO[/COLOR][/SIZE][/FONT]DE]
[/COLOR][/SIZE][/FONT]

 

[Patriot2879]

Hi I have added my excel file to dropbox if you can possibly have a look and help.

https://www.dropbox.com/s/q5nnh2z3kejxpv5/Capacity.xlsm?dl=0

 

[DanteAmor]

Try this

Private Sub ComboBox1_Change()
  Call [COLOR=#0000ff]find_date_area[/COLOR]
End Sub


Private Sub ComboBox2_Change()
  Call[COLOR=#0000ff] find_date_area[/COLOR]
End Sub


Sub [COLOR=#0000ff]find_date_area[/COLOR]()
  If ComboBox1 = "" Or ComboBox1.ListIndex = -1 Then Exit Sub
  If ComboBox2 = "" Or ComboBox2.ListIndex = -1 Then Exit Sub
  Dim wRow As Long, wCol As Long
  wRow = ComboBox2.ListIndex + 2
  wCol = ComboBox1.ListIndex + 2
  TextBox53 = Sheets("Data").Cells(wRow, wCol)
End Sub

 

[Patriot2879]

Thank you I shall try this in the morning. Much appreciated thank you.

 

[DanteAmor]

The code works according to the file you published.

 

[Patriot2879]

Thank you very much. Cant wait to try it out in the morning. Appreciated your help very much thank you.