+ Reply to Thread
Results 1 to 35 of 35

VBA solution to working formula used within table

  1. #1
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    VBA solution to working formula used within table

    Hello all,

    Bit of a tough one here which has me stumped - but there is an attached spreadsheet to help.

    I require a macro to automate the data to be populated in the main table in the attached file, populated with the data from the "Data to Import" sheet.

    I am looking at where the date in column A and the XXXXX number in G5 (OR H5, I5, J5, K5 etc - as it may be up to AJ5) matches the date in the data from the "Data to Import" sheet.

    The table will vary in size, as there could be anything from 2 dates to 365 dates - so it will need to be dynamic on columns A and then from Column G onwards, when looking in row 5 and then completing the table.

    If there are multiple matches, then to complete the cell with commas in between each value.

    In the "How I want my table to look" tab, it shows that where G5 matches the date in A6, there are 3 instances where there is a match so all three locations are shown in the cell in G6. If there are no matches then the cell is 'Greyed out' as shown in the "How I want my table to look" tab.

    Many thanks,

    Chris
    Attached Files Attached Files
    Last edited by chrisellis250; 09-10-2019 at 07:42 AM.

  2. #2
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: Amend table based on values and input data into table

    Anyone able to help at all?

    Many thanks,

  3. #3
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    VBA solution to working formula used within table

    Hello,

    I have the following Formula that does the job HOWEVER it will only return one matching value.

    =IFERROR(INDEX('Data to Import'!AB:AB,MATCH(1,INDEX(('Data to Import'!B:B='Original Table'!A6)*('Data to Import'!AD:AD='Original Table'!L$5),0),0))," ")

    Is there a way to do something similar via VBA, to loop through and return all matching values with a comma in between?

    Kind Regards,

    Chris

    I have attached an amended spreadsheet.
    Attached Files Attached Files
    Last edited by chrisellis250; 09-10-2019 at 07:12 AM.

  4. #4
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    VBA solution to working formula used within table

    Post header changed to more relevant heading.

  5. #5
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: VBA solution to working formula used within table

    Hi, chrisellis250
    I don't quite understand what the initial set up looks like.
    Is in row 5, colomn for URN & XXXXX filled with the numbers?
    Say there are 3 numbers, so URN will fill B4:D4, and XXXXX will fill E5:G5?

  6. #6
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: VBA solution to working formula used within table

    My apologies.

    I am just looking to populate the part of the table on the right, from L6:U30 - however this would have to be dynamic as the date on the left in Column A could be up to 365 rows - so the table to be filled would be L6:U371

    With L5 being the 1st cell to be populated, I am looking to see where the XXXX number in L5 and the date in A5 are present together in the "Data to Import" tab and then return to Location. There are instances where there will be multiple results.

    So ultimately in the part of the table from L6 onwards, it needs to be filled with data from the location column on the "Data to Import" sheet.

  7. #7
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: VBA solution to working formula used within table

    Now, I get more confused.
    In post 1 your example is the number in G5 onwards, now you say in L5?
    I understand that the row is dynamic, but I don't understand in what way the column is dynamic?
    And please answer my question in post 5.

  8. #8
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: VBA solution to working formula used within table

    I have since found part of the solution so ignore post one - admin wouldn't let me delete the post.

    If you look at my third post, it should make it clearer.

  9. #9
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA solution to working formula used within table

    Perhaps this can help...decided to make use of Post1 file...Code builds the table as required from scratch...No formulas needed...
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 09-10-2019 at 12:11 PM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  10. #10
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: VBA solution to working formula used within table

    Ok, try this:
    I use sheet1 as example, you need to change that to suit.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: VBA solution to working formula used within table

    Hi Sintek,

    Thank you for your solution, it is amazing.

    I have just tested it with real data and I am getting an error.

    I have attached the workbook I have just tested, so I don't know if it is me or the data I have put in?

    Many thanks,

    Chris
    Attached Files Attached Files
    Last edited by chrisellis250; 09-11-2019 at 08:09 AM.

  12. #12
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA solution to working formula used within table

    I have attached the workbook I have just tested
    ....Where....?

  13. #13
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: VBA solution to working formula used within table

    Just attached it, it was too big before.

  14. #14
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: VBA solution to working formula used within table

    Did you try my code at post 10?

  15. #15
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: VBA solution to working formula used within table

    Hi Akuini,

    I am testing both codes posted, will let you know how I get on!

  16. #16
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA solution to working formula used within table

    16822 occurrences of urn 9863...Really...

    Takes about 2 min...Perhaps someone else can simplify...Also exceeds space required...max column width is 255Char
    Attached Files Attached Files
    Last edited by sintek; 09-11-2019 at 08:39 AM.

  17. #17
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: VBA solution to working formula used within table

    Thank you Sintek! Just had a thought. When the table is being populated with the location data, is there a way to remove duplicates - so rather than 2 of the same postcodes being in one cell together there will only be one...

  18. #18
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA solution to working formula used within table

    Yes...but am only back tomorrow...Will have a look then

  19. #19
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: VBA solution to working formula used within table

    Thank you! Much appreciated.

  20. #20
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA solution to working formula used within table

    Change
    Please Login or Register  to view this content.
    To
    Please Login or Register  to view this content.

  21. #21
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: VBA solution to working formula used within table

    Hi Sintek,

    Thank you very much - perfect!

    Only thing I have noticed is re the red crosses, it only puts them in for the first 3 rows but nowhere else in the table. Not a major issue, but if there was a quick fix then that would be unbelievable.

    Much appreciated.

  22. #22
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA solution to working formula used within table

    Don't understand...please upload
    Last edited by sintek; 09-12-2019 at 10:33 AM.

  23. #23
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: VBA solution to working formula used within table

    Hi Sintek,

    Please see the attached.
    Attached Files Attached Files

  24. #24
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA solution to working formula used within table

    change red snippet to Dt
    Please Login or Register  to view this content.

  25. #25
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: VBA solution to working formula used within table

    Hi Akuini,

    I have tried your code, but it seems that If I made amendments anywhere else on the spreadsheet (no references changed anywhere btw) that the code doesn't work.

    I have added the sheet that I tried it on after I made some amendments (mainly to how the spreadsheet looked) and there is a button with the macro assigned.

    Let me know your thoughts
    Attached Files Attached Files

  26. #26
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: VBA solution to working formula used within table

    So what I mean is, your code works originally. But then when I change the data in the "Data to Import" sheet, it no longer works.

  27. #27
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA solution to working formula used within table

    I am assuming that all is working from my side...Thanks for rep +

  28. #28
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: VBA solution to working formula used within table

    Quote Originally Posted by chrisellis250 View Post
    Hi Akuini,

    I have tried your code, but it seems that If I made amendments anywhere else on the spreadsheet (no references changed anywhere btw) that the code doesn't work.

    I have added the sheet that I tried it on after I made some amendments (mainly to how the spreadsheet looked) and there is a button with the macro assigned.
    I'm still confused why you keep changing the data lay out in your sample files.
    When I asked you in what way the column is dynamic? You didn't answered it clearly.
    Anyway, you already got a working solution from Sintek.

  29. #29
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: VBA solution to working formula used within table

    Hi Sintek,

    No problem, your help was much appreciated.

    One final thing (sorry) is there a way to change the width of the columns? So the columns on the left hand side that have the crosses and ticks in to all be 8.43, with the columns that have the postcodes in to be 55?

  30. #30
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: VBA solution to working formula used within table

    Hi Akuini,

    The layout hasn't really changed, maybe the size of the columns etc but I tested your code with some real data (not test data) and the code wouldn't run.

    Wanted to test both codes as you had both been kind enough to have offered help.

  31. #31
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA solution to working formula used within table

    is there a way to change the width of the columns
    Going to assist you by directing you to a link for help...Google is your friend...We can easily write the code on your behalf but that is not always going to teach you...
    https://powerspreadsheets.com/excel-vba-column-width/

  32. #32
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: VBA solution to working formula used within table

    Hi Sintek,

    Thanks for that link - it is very helpful. I will have a look, I am able to write code to change the width of the columns - however the only issue I have is that the columns are dynamic based on the number of unique values of the URN numbers there are.

  33. #33
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA solution to working formula used within table

    Declare colwidth as long and then insert the red part in your code...
    Please Login or Register  to view this content.

  34. #34
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: VBA solution to working formula used within table

    Hi Sintek,

    Just been using the table you provided for me. What changes would I need to make to the code for it to work with time?

    So instead of the date being in column A (16/02/18 etc etc) it would be time periods (12:00 - 12:30, 12:30 - 13:00 etc etc) covering a 24 hour period.

    Would work the same, but look at time instead of date. Same location, everything else the same.

  35. #35
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2019
    Posts
    756

    Re: VBA solution to working formula used within table

    Hi Sintek,

    Have noticed an error pop up the last couple of days, I get the following:

    Run-time error '91':
    Object variable or With block variable not set


    When I press debug I have the following part of code highlighted in Yellow:

    Please Login or Register  to view this content.
    From the following section:

    Please Login or Register  to view this content.
    Any ideas why this has happened?

+ 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] VBA to Amend the Filter Selection on a Pivot Table Based on a Drop Down List
    By The_Snook in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-22-2017, 10:00 AM
  2. Get values from input table to add data in output table depending on header
    By SBBmaster09 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-16-2016, 03:25 AM
  3. [SOLVED] Display values based on input / output table
    By zdimitrov in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-11-2015, 09:12 PM
  4. Replies: 3
    Last Post: 08-13-2015, 10:07 PM
  5. Returning data in one table based on the values from another table
    By excelignorant in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-24-2013, 04:12 AM
  6. Formula to Populate table based on 3 input values
    By Senthilerp in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-18-2009, 01:19 AM
  7. How do I find data in a table based on an input cell?
    By wbeck5 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-13-2008, 06:47 PM

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