+ Reply to Thread
Results 1 to 10 of 10

Offset VBA to expand horizontally and vertically

  1. #1
    Forum Contributor
    Join Date
    03-09-2014
    Location
    Telford
    MS-Off Ver
    Microsoft 365
    Posts
    101

    Offset VBA to expand horizontally and vertically

    I'm trying to use a VBA to expand(horizontally and vertically) a data validation table which serves as a lookup for my Recs Table. The VBA seem to work alright however its using the column heading(highlighted in the attached) as the name, instead I want the row head which is in Column A(ExamCenter) be the range name. Also I want the range to include the range name/row head.(screenshot in the attached). Any help to tweak the macro will be much appreciated. Thanks
    Attached Files Attached Files
    Last edited by FredFitzgerald; 03-18-2019 at 03:42 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Offset VBA to expand horizontally and vertically

    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    03-09-2014
    Location
    Telford
    MS-Off Ver
    Microsoft 365
    Posts
    101

    Re: Offset VBA to expand horizontally and vertically

    Please where do I fit this in the code?

  4. #4
    Forum Contributor
    Join Date
    03-09-2014
    Location
    Telford
    MS-Off Ver
    Microsoft 365
    Posts
    101

    Re: Offset VBA to expand horizontally and vertically

    Gotcha! Thank you very much. Made my life easy.

  5. #5
    Forum Contributor
    Join Date
    03-09-2014
    Location
    Telford
    MS-Off Ver
    Microsoft 365
    Posts
    101

    Re: Offset VBA to expand horizontally and vertically

    When added new information to the rawdata, the name manager did not expand to include these. Do you reckon is due to the range set a1:a6?
    A few hickups
    1. when I delete info in the raw data table the macro doesn’t update( the range is still in the name manager).
    2.The name ranges in the name manager does not include the head row as I wanted it to be.

    Thanks for reviewing this for me .

    Please Login or Register  to view this content.
    Last edited by Leith Ross; 03-20-2019 at 08:05 PM. Reason: Added Code Tags

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Offset VBA to expand horizontally and vertically

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    03-09-2014
    Location
    Telford
    MS-Off Ver
    Microsoft 365
    Posts
    101

    Re: Offset VBA to expand horizontally and vertically

    Many thanks ProtonLeah. This works perfect.

  8. #8
    Forum Contributor
    Join Date
    03-09-2014
    Location
    Telford
    MS-Off Ver
    Microsoft 365
    Posts
    101

    Re: Offset VBA to expand horizontally and vertically

    When I assign the macro to button it doesn’t run. I’m not sure what I’m doing wrong but this is not first time I’ve used a macro. It’s quite a pain I can’t proceed because of a small button. I’ve checked the macro security settings on my excel and everything is alright.

  9. #9
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Offset VBA to expand horizontally and vertically

    In the attached file, I moved the code from the sheet module to general module, Module1, and added a form control button on the sheet rawdata
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    03-09-2014
    Location
    Telford
    MS-Off Ver
    Microsoft 365
    Posts
    101

    Re: Offset VBA to expand horizontally and vertically

    Many thanks BVJ. I've learnt a few lessons here. Just a thought.... do you reckon by keeping the control button on another sheet affected it from running. I noticed you put the control button on the rawdata sheet, when I did same it worked perfectly, however when I put it on another sheet where I have processing instructions for the Recs it fails to work. Quite interesting but shows the dynamism of excel.

    Thanks once again.

+ 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. Replies: 6
    Last Post: 11-13-2018, 04:54 AM
  2. Replies: 3
    Last Post: 06-01-2014, 10:26 AM
  3. Replies: 5
    Last Post: 11-13-2012, 12:37 PM
  4. function similar to offset to offset the offset
    By Jerseynjphillypa in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-03-2012, 03:07 PM
  5. Replies: 2
    Last Post: 06-14-2012, 01:44 PM
  6. Replies: 0
    Last Post: 03-08-2006, 01:15 PM
  7. [SOLVED] Using the balance sheet template, any way to expand?-way to expand
    By RABBITS in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-27-2005, 02:05 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