Formula template change to vba code for userform help

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,259
Office Version
  1. 2010
Platform
  1. Windows
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

Code:
=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]
 
Last edited:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0
Hope you can help please with this :) have tried putting the below code together myself but It is not working
Code:
[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]
 
Last edited:
Upvote 0
Hi I have tried the code below as well, hope you can help?
Code:
[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]
 
Upvote 0
And I have tried...
Code:
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]
 
Last edited:
Upvote 0
Try this

Code:
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
 
Upvote 0
Thank you I shall try this in the morning. Much appreciated thank you.
 
Upvote 0
The code works according to the file you published.
 
Upvote 0
Thank you very much. Cant wait to try it out in the morning. Appreciated your help very much thank you.
 
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top