+ Reply to Thread
Results 1 to 8 of 8

New to VBA and cannot solve this problem

  1. #1
    Registered User
    Join Date
    05-20-2020
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    3

    Question New to VBA and cannot solve this problem

    Hi!

    With all that is going on in the world I thought now is the best time to learn and am teaching myself VBA from scratch with the help of online tutorials.

    That said, I've come up with a code, but it isn't quite performing the way I want and I cannot figure out why.

    I have a spreadsheet that I input figures into on a weekly basis and then would normally copy formulas down from a previous row to update (another workbook looks for the latest figure in various columns which is why these are copied on a weekly basis and not pre-filled).

    The code is as follows:
    Please Login or Register  to view this content.
    However, if these columns have no data in row 21 they do not work and I get the following error:

    Run-time error '1004':
    Application-defined or object-defined error


    If row 21 does have a values, they work as intended.

    If anyone can point me in the right direction so that it is just pasting the formulas from row 20 without the need to have data in row 21 this would be greatly appreciated!

    Completely appreciate that I may also be running this process in a manner that is quite basic, but just started teaching myself today.

    Many thanks,

    Richard
    Last edited by alansidman; 05-20-2020 at 10:42 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: New to VBA and cannot solve this problem

    .Code Tags Added
    Your post does not comply with Rule 2 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 at http://www.excelforum.com/forum-rule...rum-rules.html



    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    05-14-2020
    Location
    Mauritius
    MS-Off Ver
    Office 365
    Posts
    97

    Re: New to VBA and cannot solve this problem

    Hi Richard,

    first 2 things you need to know. 1st, if you use variables already then you need to dimension them. You cannot forget if you use Option Explicit. Please read about this.
    2nd, Select and Selection is no use in 99% of the cases. This is what the macro recorder is doing, but unnecessary.
    Else the code should work so:
    Please Login or Register  to view this content.
    Greetings

    Tor


  4. #4
    Registered User
    Join Date
    05-20-2020
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    3

    Re: New to VBA and cannot solve this problem

    Many thanks, Tor.

    As I F8 through the code however it appears to go to each cell in row 20, copy it, but then doesn't go down to the next empty row and paste it.

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: New to VBA and cannot solve this problem

    The issue is that if there is no data in row 21, then the End(xlDown) part goes to the last row of the worksheet (it's like pressing Ctrl+Down arrow) and you then try and paste one row below that, which can't work. It's generally better to go up from the bottom of the sheet instead:

    Please Login or Register  to view this content.
    Rory

  6. #6
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: New to VBA and cannot solve this problem

    I wonder, where do you want to paste ?
    A. Always cell F21 ?
    B. The last blank row

    In point-B,
    if cell F21 has value but the rest (F22 to F1048576) is blank then with your code, the paste will happen in cell F22.
    if cell F21 is blank so the rest (F22 to F1048576) then your code gives an error, because it cannot offset one row below since it already reach the end of the row.

    The other way is using (XlUp).
    Something like this :
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-20-2020
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    3

    Re: New to VBA and cannot solve this problem

    That's great, thanks Rory!

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: New to VBA and cannot solve this problem

    Glad to help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to solve this problem?
    By Indra Rai in forum Excel General
    Replies: 2
    Last Post: 09-15-2013, 09:26 AM
  2. solve this sum problem
    By bobyjalali in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2009, 03:24 AM
  3. how to solve this problem?
    By WarrenRochant in forum Excel General
    Replies: 2
    Last Post: 01-05-2009, 03:03 AM
  4. Problem nobody can solve!!!!
    By geoffcol in forum Excel General
    Replies: 8
    Last Post: 08-22-2007, 12:53 PM
  5. Best way to solve new problem!
    By Spellbound in forum Excel General
    Replies: 5
    Last Post: 10-24-2006, 07:54 PM
  6. Can you solve my problem?
    By Keith Robinson in forum Excel General
    Replies: 2
    Last Post: 04-14-2005, 05:06 PM

Tags for this Thread

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