Run-time error: '1004': Method 'Range' of object '_Global' failed

[LearningExcel2024]

Hi,

As the title says, I am very new to Excel, more specifically VBA, and I'm working on a test project to help familiarise myself with the various concepts involved. The test project is namely a UserForm that enables the user to select a cell, or range of cells, to input a desired term or text in their selection. I am pulling the ranges using the Refedit control, as it is dynamic and seems, to me anyway, to be user-friendly.

The positive is that I have got it to work i.e. if the user has selected a range and included a term in the provided text box, it will populate the Worksheet as designed. However, there is a behaviour that is prompting the aforementioned error: the user doesn't select a range and clicks 'Input.'

I am obviously looking for a solution - and I have spent the past two days reading various resources in an attempt to resolve it myself - but more so to understand the reason(s) for the error.

I have attached related screenshots of the UserForm, Worksheet, Error dialog and of course the code.

Any guidance would be greatly appreciated.

Thank you.

 

[Micron]

I'd say your problem is that you can't use a string variable value as a range reference without concatenating quotation marks and that can be tricky (and probably isn't necessary in this case). You might try changing the variable to a range object instead. Can't say for sure because you didn't reveal the value of the variable. Perhaps you don't know that you should troubleshoot code by stepping through it (F8) and check your values and references as you go. Note that you usually have to execute a line (move off of it onto the next line) otherwise a variable won't get a value from a line that has not "run".

 

[LearningExcel2024]

I'd say your problem is that you can't use a string variable value as a range reference without concatenating quotation marks and that can be tricky (and probably isn't necessary in this case). You might try changing the variable to a range object instead. Can't say for sure because you didn't reveal the value of the variable. Perhaps you don't know that you should troubleshoot code by stepping through it (F8) and check your values and references as you go. Note that you usually have to execute a line (move off of it onto the next line) otherwise a variable won't get a value from a line that has not "run".

Hi, Micron.

Thank you for taking the time to review my post and offer your thoughts.

I studied your post for a few good hours last night, prompting further research, which ultimately lead me to starting from scratch, the result of which being a much simpler, actually functioning, set of code, as shown below:

So thank you again for helping me to better understand some of the principles involved.

 

[RoryA]

Just FYI, str is actually a built-in VBA function, so not a great name for a variable.

 

[LearningExcel2024]

Just FYI, str is actually a built-in VBA function, so not a great name for a variable.

Ah. Thank you for the heads up, Rory. I have now changed it.

 

[Micron]

Good point. It's fine as a prefix (e.g. strMyString) and many use such prefixes so that anywhere in the code you should be able to read and know what the variable type is. Here's a couple of approaches you might want to consider

- General: Commonly used naming conventions
- MS Access Naming Conventions

 

[RoryA]

And for the counterpoint to the common (in VBA anyway) conventions, go here: Making Wrong Code Look Wrong

You can skip to the section about 2/3 of the way down titled I'm Hungary if you just want the salient bit (as far as this thread is concerned) in a hurry.