• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

What am I doing wrong with this code? lastrow|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / What am I doing wrong with this code? lastrow|VBA & Macros|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosWhat am I doing wrong with this cod…
sp_PrintTopic sp_TopicIcon
What am I doing wrong with this code? lastrow
Avatar
Doug Richards
Member
Members
Level 0
Forum Posts: 19
Member Since:
December 1, 2020
sp_UserOfflineSmall Offline
1
February 2, 2021 - 8:12 am
sp_Permalink sp_Print

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

Lastrow.JPGImage Enlarger

Userform-1.JPGImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage Lastrow.JPG (40 KB)
  • sp_PlupImage Userform.JPG (18 KB)
  • sp_PlupImage Userform-1.JPG (18 KB)
Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
2
February 2, 2021 - 7:14 pm
sp_Permalink sp_Print

Judging by the picture, your worksheet is called "CODE v2" - note the space in the middle.

Avatar
Doug Richards
Member
Members
Level 0
Forum Posts: 19
Member Since:
December 1, 2020
sp_UserOfflineSmall Offline
3
February 3, 2021 - 1:49 am
sp_Permalink sp_Print

I made the change and get a Run-time error '1004':  Application-defined or object-defined error

Attached is the code and error message.

Run-time-error-1004-code.JPGImage Enlarger

Run-time-error-1004.JPGImage Enlarger

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)

sp_PlupAttachments Attachments
  • sp_PlupImage Run-time-error-1004-code.JPG (34 KB)
  • sp_PlupImage Run-time-error-1004.JPG (45 KB)
Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 414
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
4
February 3, 2021 - 5:38 am
sp_Permalink sp_Print

it is much easier if you add a workbook with the code rather than pictures that we cant actually do anything with

Avatar
Doug Richards
Member
Members
Level 0
Forum Posts: 19
Member Since:
December 1, 2020
sp_UserOfflineSmall Offline
5
February 3, 2021 - 12:00 pm
sp_Permalink sp_Print

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.

Avatar
Alan Elston
Out of here
Member
Members
Level 0
Forum Posts: 21
Member Since:
December 29, 2020
sp_UserOfflineSmall Offline
6
February 3, 2021 - 6:31 pm
sp_Permalink sp_Print sp_EditHistory

Hi

From your screenshot it looks as if you may have missed one:

https://i.imgur.com/fJQCisx.jpgImage Enlarger

_.___________________________________________________________________________

 

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:

https://i.imgur.com/PIHNhYf.jpgImage Enlarger

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

sp_PlupAttachments Attachments
  • sp_PlupImage MissedOne.JPG (55 KB)
  • sp_PlupImage FindReplaceVBEditor-1.JPG (114 KB)
Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
7
February 3, 2021 - 8:24 pm
sp_Permalink sp_Print sp_EditHistory

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.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Sue Hammond, Malcolm Sawyer
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 216
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Melanie Ford
Isaac Felbah
Adele Glover
Hitesh Asrani
Rohan Abraham
Anthony van Riessen
Erlinda Eloriaga
Abisola Ogundele
MARTYN STERRY
Rahim Lakhani
Forum Stats:
Groups: 3
Forums: 24
Topics: 6355
Posts: 27792

 

Member Stats:
Guest Posters: 49
Members: 32324
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.