+ Reply to Thread
Results 1 to 9 of 9

Uneven records across columns, two values for LastRow

  1. #1
    Registered User
    Join Date
    05-26-2019
    Location
    UK
    MS-Off Ver
    2016
    Posts
    50

    Uneven records across columns, two values for LastRow

    Hi All, I've encountered a small issue along the way to do with the fact that some records have values under Col C whereas others don't and now what I want to do is run another loop through the cells to fill all the other columns (A:B & D:J) with some data but only as long as the value under Col C is not empty.
    I was trying to do this by setting various values for the LastRow var along the way but couldn't really get it to work properly, so I'd greatly appreciate help with this.

    Some sample code to better illustrate what I was trying to achieve:
    Please Login or Register  to view this content.
    Many thanks!
    Last edited by Frenzyy; 06-01-2020 at 04:21 AM. Reason: code added

  2. #2
    Registered User
    Join Date
    03-01-2020
    Location
    Melbourne, Australia
    MS-Off Ver
    10
    Posts
    8

    Re: Uneven records across columns, two values for LastRow

    I think this is what you are after:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-26-2019
    Location
    UK
    MS-Off Ver
    2016
    Posts
    50

    Re: Uneven records across columns, two values for LastRow

    Hi Justin, thanks for the reply. Please kindly note however that this just ends up pasting over the lastrow of data, instead of adding values to the . Perhaps if I share the last part of the loop and the code which pastes the values in column C, together with what you provided then it'll help to resolve this.

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    03-01-2020
    Location
    Melbourne, Australia
    MS-Off Ver
    10
    Posts
    8

    Re: Uneven records across columns, two values for LastRow

    Sorry Frenzy,

    The code I put up should insert the specified values to the cells adjacent to any C cell that has a value other than vbnullstring (""). If this is not what you want could you clarify as that appears to be what you asked for in your first question. An example sheet with your expected result might help.

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: Uneven records across columns, two values for LastRow

    Hi,

    Minor issue - as you are assigning formulas to ranges it's betrter to do it as a formula not a value, like:
    Please Login or Register  to view this content.
    MAjor - the code as you and JustinOz20 presented shall basically
    fill all the other columns (A:B & D:J) with some data but only as long as the value under Col C is not empty.
    as you mentioned in the first post.
    But then you describe
    ends up pasting over the lastrow of data, instead of adding values to the
    Sure, these are different words but with quite similar (at least for non-native) meaning.
    If you want to add something to current value in a cell use constructions similar to:
    Please Login or Register  to view this content.
    or if the content is a number and you want to add
    Please Login or Register  to view this content.
    or insert previous content in formula (say its numerical value)
    Please Login or Register  to view this content.
    etc.

    The hint in yellow banner near the top of the page (best if there are "before" and "afer" situations presented) is very useful

    edit: seems it's not clear also for native (well, I know this 2 versions of language are quite different, but anyway)
    Best Regards,

    Kaper

  6. #6
    Registered User
    Join Date
    05-26-2019
    Location
    UK
    MS-Off Ver
    2016
    Posts
    50

    Re: Uneven records across columns, two values for LastRow

    I appreciate your guys' help. Sorry for not being clear enough and please see the attached workbook to see the before (bold values) and after (blue). So in essence, I'd like to fill in all the 'blue' fields as per above but please keep in mind that the data in Col C can vary in length.
    Please let me know if something is still not clear enough.

    Thank you!
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-01-2020
    Location
    Melbourne, Australia
    MS-Off Ver
    10
    Posts
    8

    Re: Uneven records across columns, two values for LastRow

    I did not think he was trying to add values to the cells, just that the sentence is unfinished... nothing else in the code or the language indicates he wishes to combine the contents of the cells.

  8. #8
    Registered User
    Join Date
    03-01-2020
    Location
    Melbourne, Australia
    MS-Off Ver
    10
    Posts
    8

    Re: Uneven records across columns, two values for LastRow

    You can just add a series of if statements within the existing code:

    Please Login or Register  to view this content.
    I did not finish for all the lines but that is the gist of it, for the rest of your code you should be able to achieve it without doing any select on the sheet. Select will slow your code down a great deal, since you have so many formulas you should also start with the application.calculation = manual to prevent the formula from updating during execution, then turn it back to automatic at the end of the sub.

    HTH
    Justin

  9. #9
    Registered User
    Join Date
    05-26-2019
    Location
    UK
    MS-Off Ver
    2016
    Posts
    50

    Re: Uneven records across columns, two values for LastRow

    Works perfectly. Thank you so much Justin!

+ 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. [SOLVED] Compare uneven number of columns
    By asilocyra in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-27-2017, 09:59 PM
  2. R1C1 lastRow not lastRow when data combined
    By Spyderwoman in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-28-2016, 05:20 PM
  3. Split uneven data from a single columns to multiple columns
    By pfoz in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-13-2013, 08:24 AM
  4. Sort and Match Uneven Columns of data
    By MustardMan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-21-2012, 01:43 PM
  5. Mathematical differences between uneven columns?
    By bgfar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-02-2011, 09:17 AM
  6. advanced filter for two uneven columns
    By jrtaylor in forum Excel General
    Replies: 2
    Last Post: 06-09-2011, 01:49 PM
  7. DSUM with columns as records and rows as values
    By romeno in forum Excel General
    Replies: 2
    Last Post: 06-26-2009, 05:16 AM

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