Log In
Register
Home
December 1, 2020
Offline
I'm trying to make a form where I can find an alias code that has been given to a primary code of a product. I knowledge level on VBA is very very limited and been trying to pick this up from videos.
I'm trying to search column 1 for the primary code to find the alias code in column 2, then I was going to see if it is not found, then have a command button to add it to the end of the file.
I get a:
Run-time error `9':
Subscript out of range
My code and form:
Private Sub CommandButton1_Click()
Dim RT_Line_Code As String
RT_Line_Code = Trim(TextBox1.Text)
'Find last row of table
lastrow = Worksheets("CODEv2").Cells(Rows.Count, 1).End(x1Up).Row
For i = 2 To lastrow
If Worksheets("CODEv2").Cells(i, 1).Value = RT_Line_Code Then
TextBox2.Text = Worksheets("CODEv2").Cells(i, 2).Value
End If
Next
End Sub
Trusted Members
Moderators
November 1, 2018
Offline
Judging by the picture, your worksheet is called "CODE v2" - note the space in the middle.
December 1, 2020
Offline
I made the change and get a Run-time error '1004': Application-defined or object-defined error
Attached is the code and error message.
Thanks for the help, I appreciate it very much. I know I'm going about this the wrong way, by not sitting down and learning VBA and DAX before jumping into these projects, but I don't have a choice, I am wearing about 5 hats and I don't have much time for in depth learning, thank goodness for ya-all (I'm from Texas)
Trusted Members
December 20, 2019
Offline
it is much easier if you add a workbook with the code rather than pictures that we cant actually do anything with
December 1, 2020
Offline
I know, but the information in the workbook is confidential to the company, I will change the the data to get by this, let me work on it, thank you for your help.
December 29, 2020
Offline
Hi
From your screenshot it looks as if you may have missed one:
_.___________________________________________________________________________
The search and replace thing in the VB Editor is sometimes useful for changing things in the VB Editor.
One way to use that is:
_ 1 First highlight all the coding that might have in it what you want to change,
_ then 2 3 4 5 6 7 like in this screenshot:
I personally usually find that way of doing it more reliable then relying on myself to find and replace manually all the things correctly
.
.
.
.
.
:
After its finished, it will tell you how many times it changed something, which can be a helpful indication sometimes
Alan
Trusted Members
Moderators
November 1, 2018
Offline
I would also recommend using a variable so you only need to alter it in one place:
Private Sub CommandButton1_Click()
Dim RT_Line_Code As String
RT_Line_Code = Trim(TextBox1.Text)
dim CodeSheet as Worksheet
Set CodeSheet = Worksheets("CODE v2")
'Find last row of table
with CodeSheet
lastrow = .Cells(.Rows.Count, 1).End(x1Up).Row
For i = 2 To lastrow
If .Cells(i, 1).Value = RT_Line_Code Then TextBox2.Text = .Cells(i, 2).Value
Next
End With
End Sub
You don't strictly need the variable here as you could just put the sheet reference into the With block that I added, but I thought it was worth demonstrating both.
1 Guest(s)
