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.

Replace the line

Please Login or Register  to view this content.

by the line

Please Login or Register  to view this content.

Hi huuthang_bd

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

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

Next time please attach a workbook so we don't need to recreate it.

Please Login or Register  to view this content.

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.

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.

Originally Posted by scott1945

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

Hi huuthang_bd

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