How to use RANGE Statment - Receiving Error 1004

Hello,

I am new to Excel VBA and I would appreciate any advice (or help) given.

In the worksheet, an user will enter information (such as Policy Number). After the user finish entering the information, a command button can be pressed to begin the process. Using my code it will check the values of the Policy Number (located in Column D) against an ADODB table (CRMDB) to see if the policy number exist and if there is a Block_No's value. If the Block_No's value is blank, then a value of "No" will be entered in a Closed Block (Y/N)? (located in Column K) on the same row as the Policy Number. If the Block_No's value equals to "1011", then a value of "Yes" will be entered in that column.

Please note that my code did work based on the selected cell for Policy Number but now I wish perform a search based on values in column D (regardless of the row) and return with answers for each row based on the policy number given into column K.

I think an array may be needed for this code along with the usage of a RANGE statement, but I am getting an error " Run-time error '1004': Method 'Range of object'_Global' failed.

Here is layout of the report (using test data):

Control Company Check Policy Check Processor Process to Verfied in Check Identification / Closed
Number Number Number Number Amount to Susp Susp Date 110008 Resolution Comments Block (Y/N)?
030000892
030000856
030000888
030000892
030000924
030001159
030001231
030001350
030001351
030001352
030001400
030001558
030001570
030001626
030001801

---------------------------------------------------------------------

Sample of my table in ODBC:

Company Policy Block_No
0090 030000856 1011
0090 030000888 1011
0090 030000892 1011
0090 030000924 1011
0090 030001159 1011
0090 030001231 1011
0090 030001350 1011
0090 030001351 1011
0090 030001352 1011
0090 030001400 1011
0090 030001558 1011
0090 030001570 1011
0090 030001626 1011
0090 030001801 1011
0090 030001872 1011
0090 030002083 1011
0090 030002450 1011

---------------------------------

Here is my code:

S

Please Login or Register  to view this content.

Sorry for being too detail, just wanted to make sure that it is understandable.

Looking forward in hearing suggestions.

Thank you.

Hello and Welcome to the forum,

Declare FPolicy as a Range, and set the Range

Please Login or Register  to view this content.

Also you need to update your title (Rule #1) and use code tags (Rule #3).

Hope this helps.

abousetta

Hi and welcome ot the forum

1st please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

2nd, Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I will add them for you - this time

If you can upload a dummy copy of the workbook I can take a look at it tonight.

abousetta


Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Abousetta - I will work on it now and upload it for you.

Thank you!
----------------
Here is the report.

Please note: The original connection is to an ADODB but I place a sample data file in the first tab. As for the BEFORE sheet, I left the name as "EF" because it is used in my code.

In the AFTER sheet, the policy numbers (highlighted in pink) are the data entered and the results are highlighted in yellow.

PLEASE NOTE that I had made some changes to my code in hoping to make it work.

Please let me know if there is anything else you may need. Thank you again for your help.

I've asked for help, but off the bat, this line is not correct

Please Login or Register  to view this content.

Do you mean:

Please Login or Register  to view this content.

If so, then you need to add a dot before the Range so that it's .Range

abousetta

You must build criteria string from the range for the IN clause:

Please Login or Register  to view this content.

What is BCount? It looks like it's supposed to be the last used cell in column K, but now it is 7 and it doesn't seem that you indicate anywhere for this number to change.

Just is a quick suggestion because I am out the door, but here goes...

When you say:

Please Login or Register  to view this content.

do you mean anything more than the range D7:D7 because that's what the code will use. It will never increment the BCount = BCount + 1 that you have later in your code. Once you set For each something in something, Excel locks this range in an array and only looks at that array (even if you increase that upper limit of the range later in the code before the 'Next PN'. Therefore you have to set the full range from the beginning.

abousetta

Maybe try:

Please Login or Register  to view this content.

abousetta

Sorry I haven't looked at your file recently and so I will have to look at it later today. Simply put if the last row used in Column D is the same as Column K (which it should be), then you can just use the same BCount for both. If not, then you will need separate code for each column to determine the last used cell in that column.

Ahmed