Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Run time error 5 and Userform issue

0

Hello,

I'm having an issue with a 'Run time error 5, Invalid procedure call or argument ' pop-up.

My full program runs fine in a separate workbook from a module.

When I move it to a userform, it will work without the below section (no pop-up).

With the below section, it runs through this whole section of code and populates the values, but the 'Run time error 5' pop-up comes after it...and the debugger highlights  Part 6 " Strt2 = InStrRev(rll.Value, " ", Strt1, vbTextCompare) "

No matter which parts of the below code I remove, 'Run time error 5' pop-up will display, and the debugger will always highlight a different part of the code.

Any thoughts on why the below is causing issues inside a userform?

The goal of the program is to find a phrase in a cell of text, and then extract 5 words that come directly before it by using spaces as a means of locating each one.

I don't see any Public variable conflicts.

I would appreciate any assistance you can provide.

Thank you.

re = Range("B" & Rows.Count).End(xlUp).Row
Set rng = ActiveSheet.Range("B1:B" & re)
Cnt = 1

For Each rll In rng
' Data example: SAN / SAN 88004 SF 80 EA Item long text
'Strt2 is space to right of target data point
'Strt3 is space to left of target data point

' Part 6 extract Unit of Measure
    Strt1 = InStrRev(rll.Value, "Item long text", -1, vbTextCompare)
    Strt2 = InStrRev(rll.Value, " ", Strt1, vbTextCompare)
    Strt3 = InStrRev(rll.Value, " ", Strt1 - 2, vbTextCompare)
    Range(Key4 & Cnt).Value = Trim(Mid(rll.Value, Strt3, Strt2 - Strt3))

' Part 7 extract quantity
    Strt2 = Strt3
    Strt3 = InStrRev(rll.Value, " ", Strt2 - 2, vbTextCompare)
    Range(Key5 & Cnt).Value = Trim(Mid(rll.Value, Strt3, Strt2 - Strt3))

' Part 8 locate junk word (print nothing)
    Strt2 = Strt3
    Strt3 = InStrRev(rll.Value, " ", Strt2 - 1, vbTextCompare)
    ' DO NOT print anywhere

' Part 9 extract part number
    Strt2 = Strt3
    Strt3 = InStrRev(rll.Value, "/", Strt2 - 1, vbTextCompare) + 1
    Range(Key8 & Cnt).Value = Trim(Mid(rll.Value, Strt3, Strt2 - Strt3))

' Part 10 extract brand abbreviation
    Strt2 = Strt3 - 2
    Strt3 = InStrRev(rll.Value, " ", Strt2 - 1, vbTextCompare)
    Range(Key7 & Cnt).Value = Trim(Mid(rll.Value, Strt3, Strt2 - Strt3))

    Cnt = Cnt + 1
Next rll
excel
MS Office Version: 2010
posted by: Kaitlyn_VBA (rep: 6) on Tue Oct 31, 2023 at 1:18 am
Answer
Discuss

Answers

0
Selected Answer

Hi again Kaitlyn

You didn't attach a file but your code will invoke a Run time error 5 if range rll is a blank cell or does not contain the exact search string "Item long text" (e.g. it contains an extra space beftween Item and long, say "SAN / SAN 88004 SF 80 EA Item  long text"). In such cases, so Strt1 becomes 0 so the line:

Strt2 = InStrRev(rll.Value, " ", Strt1, vbTextCompare)
tries to looks backwards from position 0 (Strt1 above), i.e. before the start of the string.

You could test for that and adjust what happens, For example my suggestion below assumes the string is likely to be missing (so look from the end of rll instead):

' Part 6 extract Unit of Measure
    Strt1 = InStrRev(rll.Value, "Item long text", -1, vbTextCompare)
    ' change if string was not found
    If Strt1 = 0 Then Strt1 = Len(rll.Value)
    Strt2 = InStrRev(rll.Value, " ", Strt1, vbTextCompare)
    Strt3 = InStrRev(rll.Value, " ", Strt1 - 2, vbTextCompare)
    Range(Key4 & Cnt).Value = Trim(Mid(rll.Value, Strt3, Strt2 - Strt3))

Hope this helps. If so, please remember to mark this Answer as Selected. If not, please add a sample file (with UserForm)  to your original question and expand the text to explain more.

posted by: John_Ru (rep: 6142) on Tue Oct 31, 2023 at 4:34 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login