Help with Userform to Useform Vlookup

[sweepea]

It's kinda of complicated. I'm creating a Userform which enables my coworkers to track their work. They are required to enter the phone number and other information. What I'm trying to do it, when they've entered the phone number in txtBTN, I want the macro to take the first 6 digits (9 digits will be entered in the text box, without any spaces) and do a vlookup to an excel worksheet ("Markets")whick has the 6 digits in column A, and the markets are in colum B. And to bring back the market that's matched to a text box called txtMkt in the Userform.

I have the coding to do this from one excel sheet to another, but I can't get it to work, userform component to userform component.

This is what I have for the macro for the excel sheet to excel sheet.

Option Explicit


Sub MarketSearch()
On Error Resume Next

Dim strMarketFileName As String ' Area Code and Market Name File
Dim intLastRowPasteFile As Integer ' Last Row in Source File
Dim strPasteBookName As String ' file name to paste data in
Dim strPasteBookVlookupColumn As String * 1 ' row with phone numbers
Dim intPasteBookPasteColumn As Integer 'column to paste market data in
Dim lngAreaCodeAniToFind As Long ' Area Code and ANI to Find
Dim strMarketName As String ' Market Name in strMarketFileName
Dim I As Integer ' counter
Dim RangeObj As Object

I = 0 'init
intLastRowPasteFile = 0

Application.ScreenUpdating = False
Application.DisplayAlerts = False


'====Source File====start===============

strPasteBookName = ActiveWorkbook.Name ' file name to paste data in
Range("A1").Select
' row for vlookup
Cells.Find(What:="Originating Number", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False).Activate
strPasteBookVlookupColumn = Chr(ActiveCell.Column + 64)
intPasteBookPasteColumn = ActiveCell.Column + 5
intLastRowPasteFile = Range("A1").End(xlDown).Row

'====Source File====end===============

'====Market File====start===============

strMarketFileName = GetFileName() ' Area Code and Market Name File
Workbooks.Open (strMarketFileName)
strMarketFileName = ActiveWorkbook.Name

'====Market File====end===============

'vlookup====start===================
For I = 1 To intLastRowPasteFile

lngAreaCodeAniToFind = 0 'reset

Err.Clear

Workbooks(strPasteBookName).Activate
lngAreaCodeAniToFind = Str(Left((Range(strPasteBookVlookupColumn & I).Value), 6))
If lngAreaCodeAniToFind = 0 Or lngAreaCodeAniToFind = Null Then GoTo NextILoop
Workbooks(strMarketFileName).Activate
Columns("A:A").Select
Set RangeObj = Selection.Find(What:=lngAreaCodeAniToFind, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False)
If RangeObj Is Nothing Then GoTo NextILoop Else RangeObj.Select


strMarketName = ActiveCell.Offset(0, 1).Value
Workbooks(strPasteBookName).Activate
Cells(I, intPasteBookPasteColumn).Value = strMarketName

NextILoop:

Next I

'vlookup====end===================

Application.ScreenUpdating = True
Application.DisplayAlerts = True



End Sub

I'm willing to try anything right now.

Thanks in Advance

 

[Colo]

On 2002-09-25 15:39, sweepea wrote:
It's kinda of complicated. I'm creating a Userform which enables my coworkers to track their work. They are required to enter the phone number and other information. What I'm trying to do it, when they've entered the phone number in txtBTN, I want the macro to take the first 6 digits (9 digits will be entered in the text box, without any spaces) and do a vlookup to an excel worksheet ("Markets")whick has the 6 digits in column A, and the markets are in colum B. And to bring back the market that's matched to a text box called txtMkt in the Userform.

Hi sweepea,

I'm not sure what is Userform to Useform Vlookup.

Please put this code on your Userform and try it.

<pre>
Private Sub txtBTN_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim ragFound As Range
Set ragFound = Sheets("Markets").Columns(1).Find(Left(txtBTN.Text, 6), Lookat:=xlWhole)
If Not ragFound Is Nothing Then
txtMkt.Text = ragFound.Offset(, 1).Value
Else
txtMkt.Text = "Market is not fund."
End If
End Sub
</pre>

 

[sweepea]

Thank you so much Colo. I've working on this way too long, and I've been making it way to complicated. I really appreciate your help.

 

[ivylig]

I'm new to userform/macro/VBA.
What is the syntax/code if you have more than 1 columns to retrieve the information from a sheet to your userform/textboxes? I have additional 6 textboxes to extract information from a sheet: let's say a person types a certain area code on a given textbox1 and hit enter. The output from other textboxes should list the salesperson, phone number, manager, etc.
Whats the code I should add and where? THANKS!
The above code worked but it's only showing the first column... I know this is easy, but I'm not a programmer.