+ Reply to Thread
Results 1 to 10 of 10

Change code to select different reference cell

  1. #1
    Registered User
    Join Date
    09-09-2018
    Location
    Vancouver, canada
    MS-Off Ver
    2010
    Posts
    76

    Change code to select different reference cell

    I have a macro that works (and I donít think I need any new code) but need help with a change: I need the code to change the cell location where itís currently getting a reference number (Iím sure Iím using the wrong term but bear with me).

    Using my Excel sheet the macro searches for a 15 (as a last number 20-15, 1-15 etc.) in cells B30, F30 and J30. When it has a positive result it references the cell below it and uses that number to search cells E1:E12 for a match and when it finds one write the data from cells cells B30, F30 and J30, plus one (see example below) in the adjacent cell to the right of it.

    Example: The code has found a 7-15 in cell B30, so a positive result, it then searches the cell below for a number, B31 which has a 1 in it. 1 is the search number in the search range E1:E12. Cell E1 has the number 1 so the code would write 7-15, from cell B30, in the adjacent cell to the right (F2) and increase the last number by 1 so it becomes 7-16.

    The change I would like to make is instead of using the cell below B31 in above example it would use cell A31 (cell E31 and cell I31 for the other rows) instead. Thatís it.

    I also have a general question about different versions of Excel. I been using this code on Excel 2003, XP Pro and now I switched to Win 7 Office 2007 (I know still way behind, hold the jokes) but the code does nothing. I have changed the security setting for macros to allow everything and still nothing. Is it the code or any advice sure would be appreciated?

    small change.png

    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    439

    Re: Change code to select different reference cell

    Replace the line
    Please Login or Register  to view this content.
    by the line
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-09-2018
    Location
    Vancouver, canada
    MS-Off Ver
    2010
    Posts
    76

    Re: Change code to select different reference cell

    Hi huuthang_bd

    Thank-you so much for your reply. I will test and get back to you.

  4. #4
    Registered User
    Join Date
    09-09-2018
    Location
    Vancouver, canada
    MS-Off Ver
    2010
    Posts
    76

    Re: Change code to select different reference cell

    Hi huuthang_bd

    Sorry for the delay in getting back to you. The code does nothing when the change is done. Just to review my Excel sheet included in the post right now when it finds a 15 (cell B30) it then searches cell B31 (which has a 1 in it) and then searches the range E1:E12 to find a 1 and then write the cell contents (from cell B30) in the adjacent cell to the right of it and increase the number by 1, 7-16 would be written to cell F1. Same would happen to cell J30.

    What I would like the code to do is instead of searching cell B31 search cell A31 and do all the above as usual.

    Thanks so much for all your help.
    Scott

  5. #5
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,054

    Re: Change code to select different reference cell

    Next time please attach a workbook so we don't need to recreate it.
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    09-09-2018
    Location
    Vancouver, canada
    MS-Off Ver
    2010
    Posts
    76

    Re: Change code to select different reference cell

    Hi jolivanes

    Thanks for your reply but I think huuthang_bd has been working on this one. The sample data is a very tiny example and so I would like to keep my code but just make a small change.

    I have uploaded the Excel workbook.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,054

    Re: Change code to select different reference cell

    Re: I think huuthang_bd has been working on this one.
    Excuse me being so bold trying to help.
    I hereby will let the people know not to get involved. You might get too upset.
    Last edited by jolivanes; 05-20-2019 at 05:09 PM.

  8. #8
    Registered User
    Join Date
    09-09-2018
    Location
    Vancouver, canada
    MS-Off Ver
    2010
    Posts
    76

    Re: Change code to select different reference cell

    I didn't mean to offend you it's just I don't want you wasting your time when he has been working on it. Sorry about that.

  9. #9
    Valued Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    439

    Re: Change code to select different reference cell

    Quote Originally Posted by scott1945 View Post
    Hi huuthang_bd

    Sorry for the delay in getting back to you. The code does nothing when the change is done. Just to review my Excel sheet included in the post right now when it finds a 15 (cell B30) it then searches cell B31 (which has a 1 in it) and then searches the range E1:E12 to find a 1 and then write the cell contents (from cell B30) in the adjacent cell to the right of it and increase the number by 1, 7-16 would be written to cell F1. Same would happen to cell J30.

    What I would like the code to do is instead of searching cell B31 search cell A31 and do all the above as usual.

    Thanks so much for all your help.
    Scott
    Hi Scott
    You code are missing a minus sign
    SearchVal = DashPair.Offset(RowOffset:=1, ColumnOffset:=-1).Value

  10. #10
    Registered User
    Join Date
    09-09-2018
    Location
    Vancouver, canada
    MS-Off Ver
    2010
    Posts
    76

    Re: Change code to select different reference cell

    Hi huuthang_bd

    At my age seeing is clearly not 100%. I really want to thank-you that did it. Have a great week.

+ 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] how co i use select case to change cell location in this formula in vba code
    By kevinu in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-16-2018, 10:12 AM
  2. Replies: 6
    Last Post: 01-23-2018, 04:49 AM
  3. Replies: 1
    Last Post: 02-11-2015, 01:56 PM
  4. change cell reference based on vba code
    By zak.horrocks in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-06-2014, 09:42 AM
  5. Replies: 4
    Last Post: 06-17-2013, 05:21 AM
  6. [SOLVED] Change Year in Macro with reference to cell in Working code.
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-17-2013, 02:11 PM
  7. Dragging formula, change sheet reference, but not cell reference
    By Kalilaya0419 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-13-2013, 04:50 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