i am new to Am new to exel vba. getting error in my code. pl

June 4, 2016 at 12:00:35
Specs: Windows 8.1
13 columns in spread sheet
±350 records in spreadsheet
want to send the record i lookup to a separate spreadsheet.


Private Sub cmdClose_Click()
'USED TO CLOSE THE USERFORM
Unload Me
End Sub


Private Sub cmdSend_Click()
'DIM THE VARIABLES
Dim cNum As Integer
Dim X As Integer
Dim nextrow As Range
'CHANGE THIS NUMBERT TO THE NUMBER OF TEXTBOXES IN THE USERFORM
cNum = 13
'SET THE SHEET NUMBER WHERE THE INFORMATION MUST BE SEND TO eg. NEXT CLEAN SHEET
Set nextrow = Sheet4.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
For X = 1 To cNum
nextrow = Me.Controls("Reg" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next
MsgBox "The data has been sent"
'AFTER THE BUTTON IS CLICKED CLEAR THE CONTROLS
cNum = 13
For X = 1 To cNum
Me.Controls("Reg" & X).Value = ""
Set nextrow = nextrow.Offset(0, 1)
Next
End Sub

Private Sub Reg1_AfterUpdate()
'CHECK SHEET TO CONFIRM IF THE NUMBER ENTERED HAS BEEN LOCATED
If WorksheetFunction.CountIf(Sheet2.Range("B:B"), Me.Reg1.Value) = 0 Then
MsgBox "This is an incorrect Force Number"
Me.Reg1.Value = ""
Exit Sub
End If
'IF THE NUMBER HAS BEEN FOUND IN SHEET PROCESS THE REST OF THE CODE TO COMPLETE THE OTHER FIELDS AS WELL
With Me

.Reg2 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Lookup"), 2, 0)
.Reg3 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Lookup"), 3, 0)
.Reg4 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Lookup"), 4, 0)
.Reg5 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Lookup"), 5, 0)
.Reg6 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Lookup"), 6, 0)
.Reg7 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Lookup"), 7, 0)
.Reg8 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Lookup"), 8, 0)
.Reg9 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Lookup"), 9, 0)
.Reg10 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Lookup"), 10, 0)
.Reg11 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Lookup"), 11, 0)
.Reg12 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Lookup"), 12, 0)
.Reg13 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Lookup"), 13, 0)

End With
End Sub


Private Sub Reg13_Change()

End Sub

Private Sub UserForm_Click()

End Sub


See More: i am new to Am new to exel vba. getting error in my code. pl

Report •

#1
June 6, 2016 at 00:40:07
Just saying that you are getting an error in your code wont really help us help you, when you receive the error hit the debug button, a line of code should be highlighted in yellow, can you tell us which line is being highlighted and what the error message says exactly?

Report •

#2
June 6, 2016 at 10:37:34
Sorry about that. I will try to explain my problem.
We have a lot of info on the Worksheet (Master List - Sheet2) need to search with a 8 digit number to complete 12 text boxes to confirm it is the correct member. then i would want to transfer the data to a worksheet named Output. in the same workbook.
Spreadsheet has 13 columns and about 350 rows with entries in.
I have made the input form and is happy with it. if the number does not exist in sheet it shows me a message.
On the input form is a button Send to Datasheet that must send data to the Output sheet and then clear all text boxes to accept the next number.
when I enter the number and press TAB I get the following message:Run-time error '1004'
Unable to get the Vlookup property of the WorksheetFunction class
The following line is being highlighted
.Reg2 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Lookup"), 2, 0)

I hope this is a better explanation. I can make printscreens of everything it that will help but i do not know how to include them here.

Again thanx for helping.


Report •

#3
June 8, 2016 at 00:13:14
it looks like you have some controls on your worksheet/form that could be missing?

control reg1 through to reg13 are they all there?

also check that you have a sheet (Sheet2) and its name is "Lookup"


Report •

Related Solutions

#4
June 14, 2016 at 06:59:23
I have checked. All the controls Reg1 to Reg13 is on the form. they basically is the same sequence as the columns/headings
I think the problem is in the lookup table that i am TRYING to construct. That is named Lookup as per the example.If i understand the video correctly this is where the program when it runs will get the information from to complete the rest of the fields when I enter the force number.
I am using Excel 2016 and the example was in 2010 i think.

Report •

Ask Question