#1
|
|||
|
|||
Excel Macro: Run-time Error '91'
I keep on having the same error on all my forms with the search button. Run-time Error '91' Object variable or With block variable not set and it shows that the error is on ListBox ( see attached image ) Can anyone help me? Thanks |
#2
|
||||
|
||||
This is my code and you deleted/missed out what to do if LBRS is nothing, something along the lines of:
If LBRS Is Nothing Then Set LBRS = .Range("A2") |
#3
|
|||
|
|||
yes it is your code, I've been finding out what is the problem and I don't know what it is.
I already added the code if nothing but the same error shows |
#4
|
||||
|
||||
When you debug, and that line is highlighted as in your picture, what's showing in the Value column of the Locals pane of the VBE for LBRS?
If it's not showing Nothing or Empty, then in the Immediate pane, type: ?LBRS.address and press Enter. What's the result? It would be easier to help you if you could provide a file where this error occurs - sure, desensitize the file and remove unnecessary sheets/code, but do ensure that the error still occurs before posting it here. |
#5
|
|||
|
|||
Here is the file
|
#6
|
||||
|
||||
See attached.
LBRS was Empty/Nothing at that stage in the code. I've reinstated that line. Headers in Report B, if present, should exactly match those in Sheet1 Headers should only be on one row. There should be no duplicate headers in Sheet1. I've copied the headers from Report B to Sheet1 but really I should have reduced the headers on Sheet1 to 1 row and re-written the code which puts the headers in Report B to match those in Sheet1. I was lazy. You have to get the Control names exactly right in the code. Corrected. At least now it works to a degree, over to you to add the ifs and buts, checks etc. For instance if someone chooses None from your dropdown for the Adhesive Type, are there entries in that column with the literal string None in them?, or are you hoping to show results for when there is nothing in those cells? There's a putative line in there which you can remove if I've got this wrong. There are a few commented-out lines of code which you might end up needing to use. Try to avoid On Error Resume Next too much unless you know an error might be thrown and why, otherwise it hides errors while debugging. The code: Code:
On Error Resume Next Set newsht = Sheets("Report B") On Error GoTo 0 |
#7
|
|||
|
|||
Thank you
if all the products show when its not supposed to, is it something wrong with the SceSht.Range(A1)? |
#8
|
||||
|
||||
Quote:
Under what circumstances? Attach your current file. |
#9
|
|||
|
|||
See attached file.
The Sheet 2 is the one I am talking about, when I enter "1" on Tag Size Width, it shows all the products instead of only showing product B. |
#10
|
||||
|
||||
Again,
Quote:
An example of how yours differ: On sheet Sheet2: and on Report C: Last edited by p45cal; 06-14-2018 at 05:43 AM. |
#11
|
|||
|
|||
I already checked, but still it shows all the products after clicking the search button. Also I keep on getting the same error (See attached image) on my other sheet. Excel Sheet is attached.
|
#12
|
||||
|
||||
The headers on the report sheet don't match the headers on the data sheet - e.g. misspellings of the word "without" on the data sheet, and extra spaces in some headers. I suggest you copy the first row of headers from the data sheet and paste them into the report sheet.
|
#13
|
||||
|
||||
The code doesn't expect to see multiple tables on Sheet1; for .currentregion to work properly here, every table needs to be separated from its neighbour by at least one completely blank/empty row. Deleting the text Table 4 in cell A22 satisfied that in this case.
Still, headers don't match: You have Product Number in cell A23 of Sheet1, but Product in cell A1 of the Report sheet. You have a trailing space in cell B23 of Sheet1, but none in cell C1 of the Report sheet. In the userform you have a textbox called txtResMinC, but you have in the same place on the userform, another textbox called ResMinC, worse, you have a variable in the code ResMinC! I deleted the textbox ResMinC from the Userform. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Word 2010 Run-Time error 4198 with Insert Picture with Caption and Fram Macro | jstills116 | Word VBA | 0 | 06-24-2016 07:46 AM |
Help Please: New VBA user trying to use a macro to split Mail Merge documents. Two Run-Time Error | zipit189 | Word VBA | 7 | 03-18-2015 01:13 PM |
Shift Excel Cell after executing a macro for second time | LearningMacro | Excel Programming | 2 | 01-08-2015 03:05 PM |
Run Time Error 4248 when opening Word Doc from Excel | CIF | Excel Programming | 12 | 02-21-2014 02:27 PM |
Run-time Error 5174: Open Word Documents from Excel | tinfanide | Excel Programming | 3 | 10-01-2013 07:35 AM |