+ Reply to Thread
Results 1 to 24 of 24

How to use RANGE Statment - Receiving Error 1004

  1. #1
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    Smile 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.
    Last edited by efisher; 12-20-2013 at 04:34 PM.

  2. #2
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    Re: New to Excel VBA

    Ugh! I apologize that my sample report did not format correctly. The data is for the column (Policy Number).

  3. #3
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: New to Excel VBA

    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
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    Re: New to Excel VBA

    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    Re: New to Excel VBA

    Thank you for your comments and I will review the rules.

    Thank you again!

  6. #6
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    Re: How to use RANGE Statment - Receiving Error 1004

    Thank you for your comments and I have reviewed the rules.

    I have changed my title and thank you FDibbins for marking my codes with the
    Please Login or Register  to view this content.
    tags.

    Abousetta - I made the changes as you suggested, but still receiving error 1004 - Method "Range of object_Global" failed.

    I will do some research on this and keep you posted if I am able to resolve this.

    If there are any other suggestions, I will welcome it.

    Thank you.

  7. #7
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: How to use RANGE Statment - Receiving Error 1004

    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

  8. #8
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    Re: How to use RANGE Statment - Receiving Error 1004

    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.
    Attached Files Attached Files
    Last edited by efisher; 12-20-2013 at 04:53 PM.

  9. #9
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    Re: How to use RANGE Statment - Receiving Error 1004


    I am looking for any new suggestion to my problem. I am still getting an error 1004. Any help will be greatly appreciated.

  10. #10
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: How to use RANGE Statment - Receiving Error 1004

    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

  11. #11
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    Re: How to use RANGE Statment - Receiving Error 1004

    Okay, I will give it a try. Thank you

  12. #12
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: How to use RANGE Statment - Receiving Error 1004

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

    Please Login or Register  to view this content.
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  13. #13
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    Re: How to use RANGE Statment - Receiving Error 1004

    Thank you abousetta and Izandol for your responses and suggestions. I apologize for not responding sooner, had problems in accessing this site from work.

    Anyway, in using your suggestions, it has helped alot and now I am dealing with one issue. As the program matches the policy number against the ADOBC record, in column K, it should write either "Yes" (that it contains the Block Code "1011") or "No" (that it does not). It works for the first row (row 7) but it does not continue after that. I am wondering what I have done wrong.

    Below are the codes that I am using along with my little "tweaks":

    Please Login or Register  to view this content.
    Any suggestions would be welcomed. Thank you.

  14. #14
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    Re: How to use RANGE Statment - Receiving Error 1004

    One more thing, I forgot to put in my previous post, I highlighted the code that I am using in red. I tried the Offset clause but it did not work for me properly.

  15. #15
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: How to use RANGE Statment - Receiving Error 1004

    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.

  16. #16
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    Re: How to use RANGE Statment - Receiving Error 1004

    Hi Abousetta,

    Happy New Year!
    I used the BCount for my range in Column D and set the count at 7 so that it can correspond with the row that I wished to use. As for the "Counter1", I am using that for Column K to correlate with Column D and post my response (Yes or No).

    I thought with this current code, that I was adding "1" to the counters and it will be used in the Range as the row counter.

    Where did I go wrong?

  17. #17
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: How to use RANGE Statment - Receiving Error 1004

    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

  18. #18
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    Re: How to use RANGE Statment - Receiving Error 1004

    Okay, I would like to set column D from D7 until the Last Row. The same goes for Column K.

    I will go back to research using this information and work on the code. Thanks.

  19. #19
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: How to use RANGE Statment - Receiving Error 1004

    Maybe try:

    Please Login or Register  to view this content.
    abousetta

  20. #20
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    Re: How to use RANGE Statment - Receiving Error 1004

    Hi Abousetta,

    Thank you again for your response and suggestion. What will happen with column K (for the Yes or No response)? Do I need to add a similar code for that column or just rely on the outcome from column D to post the response in column K?

  21. #21
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: How to use RANGE Statment - Receiving Error 1004

    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

  22. #22
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    Re: How to use RANGE Statment - Receiving Error 1004

    I am still having problems in getting "Yes" or "No" to appearing in column K. At first, the responses started to appearing in column N instead of K, so I added an offset clause and now it is appearing in the correct column but not starting on the correct row. Here is my updated code. I will highlight in red the updated code:

    Please Login or Register  to view this content.
    Whew! Excel VBA is harder than I thought.

    Any suggestion is welcome. Thanks.

  23. #23
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    Re: How to use RANGE Statment - Receiving Error 1004

    Well, I am getting closer in resolving my issue. I am able to get my response (Yes or No) in the correct column and row for each Policy Number. Now the problem is comparing the actual value of what the user enter versus the database table (ADO) in order to get the correct response. I am getting an error 3265 and I cannot figure it out but it has something to do with the Array coding. In using the debugging, I can see the values for each of the RPolicy fields but it is only capturing the first record and not including it in the Array.

    Here is my code:

    Please Login or Register  to view this content.
    Please let me know what I am doing wrong. Any suggestion is welcome. Thank you.

  24. #24
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    Re: How to use RANGE Statment - Receiving Error 1004

    Since the issue with error 1004 has been resolved, I will close out this request. Now that I have a new error 3265, I will create a new request. Thank you to all that responded and helped me with this problem.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1