Code to write numbers to cells

  • Hi


    I’m trying to get some code to write numbers when a set of numbers are found. I have copied an Excel sheet (just a very small example of what I use so I need specific cell locations to alter the code). To make it as clear as possible if I could draw your attention to the Excel sheet. I have a step by step process of what I’m trying to do.



    Below I’m using the found number in cell B4.


    1) The code must first search the range B4:B10, B13:B19 (need to be able to change) for any sets of numbers (1-1, 20-5, 7-7 etc).


    - For each set of numbers found (B4 would be the first) the code must match cell A3 the “1-1” by searching specific cells H3 and H12 (cell locations in the code so it can be changed).


    - The code also has to match 5.45 in cell C3 and search cells J3 and J12 (again need to be able to change in the code) for a match.


    - It would find the 1-1 in cell H12 and 5.45 in cell J12.


    - The last thing to do is match the line number which is in cell A4 which is 1 with line number 1 in cell H13. Write the contents of cells I13 to cell C4, cell J13 to cell D4 and cell K13 to cell E4.



    2) The code would then move on to the next found set of numbers (cell B16 in this example) and repeat the process.



    I really hope this is clear, not always easy to write it down. If it’s not clear please let me know so I can clarify it some more. Thanks so much for all your help.



    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"WRITE NUMBERS TO CELLS.png","data-attachmentid":1216316}[/ATTACH]

  • Thank-you for your reply


    I will attach my Excel workbook. The first expected write result for the found set of number 7-1 (located in cell B4 is cells C4/D4/E4). The second expected write result for the found set of number 7-8 (located in cell B16 is cells C16/D16/E16). Please let me know if I can add more details.


    Thanks again.

  • Hi Carim


    I got your code and this looks really like a lot of work which I greatly appreciate. It definitely works in my sample workbook but not so great in my day-to-day Excel sheet. In your code you’re using column references which I can’t do because I have data, formulas and other code running in theses columns so as a result your code doesn’t work properly.


    That’s a lot of work in your code. Is it possible to change all references to specific cells? I have to be able to change the cell locations to use any code in my workbook. If its way too much work I would understand if you can’t help on this one.


    It’s not always easy to write this stuff up. Using my sample image the main thing to accomplish is to match cell A3 (1-1), C3 (5.45) with matching 1-1 in cell H12 and the 5.45 in J12 and then the matching line A4 with H13. Maybe you have another way to get there.


    Thanks again for your help.


    Karen

  • Hi,


    Quote

    It definitely works in my sample workbook BUT not so great in my day-to-day Excel sheet.


    Since I cannot guess what is actually in front of your eyes ...


    Could you post a sample of what you call your ' day-to-day ' Excel sheet ... ??? :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hi


    I understand where you are coming from but even if we get it to work I keep making changes to my sheet and so next week or next year then it won't work again. That's why I have to have specific cells for all my code so it can be changed as I make changes.

  • Making changes to data is never a problem ...


    What needs to be built into the macro is the underlying structural logic of your worksheet ...


    Hope this clarifies


    :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • By the way ... when you say


    Quote

    That's why I have to have specific cells for all my code so it can be changed as I make changes.


    Could you indeed be more specific ... and describe the changes you are making ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • "Making changes to data is never a problem ..." - the data is not the issues it's which cell or cell range to find, divide etc and what cells to write the data to is the issue. That's why using a row can't work because I may have data/formulas (that can be changed with some code so that's a big problem, has happened to me).


    "describe the changes you are making" Using my sample data Excel sheet would be moving cells from H3:H10, I3:10,J3:J10,K3:K10 toL20:l27, M20:M27,N20:N27,O20:O:27 with data in cells right up to the top of the sheet.

  • From your explanation, my understanding is the following :


    1. There are 2 Data Blocks : A3:E10 and A12:E19


    2. There are 2 Reference Blocks : H3:K10 and H12:K19


    And each one of these 4 Blocks can be located anywhere in the worksheet ...


    Is that right ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hello again,


    Attached is your Version 2 which allows you to have the Data Block located wherever you want ... and the Reference Block as well ...


    Currently the two ranges within the Data Block must go together to their new location ... Same principle holds true for the Reference Block ...


    Comments have been added to the Macro to make your maintenance easier ...


    Let me know the results of your tests ...


    Hope this will help

  • Hello,


    Hope you have not decided to give up ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hi Carim



    Sorry for the delay in getting back to you but I’m having a debugging problem and I always have to triple check to make sure it’s not something I doing wrong. This is really impressive code. It’s highlighting the following line:


    last1 = ActiveSheet.Cells(Application.Rows.Count, ColLette


    I really hope it's not me causing the problem.

  • Hello,


    As explained inside the macro with all the comments shown in Green ...


    The four ranges have to be adjusted according to the specifics of your Worksheet ...


    Hope this clarifies


    :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hello,


    As an illustration of the Data and Reference Blocks moved to other locations ...


    Attached is Version 3 for your review ...


    To make things easier ... have added 4 Named ranges ...


    and the formulas in cells T1:U4 do produce the exact result you need to use in order to modify the ranges in your macro ... :wink:

  • Hi Carim


    It was me, of course. It works but I need to make a small change if that'’s okay. To start, using your version 3 Excel sheet, see image below, the range Set rng1 = Range("C9:G16") and Set rng2 = Range("C18:G25") is what the data would look like prior to running the macro. I’ve inserted, in green highlight, the expected result after the running the macro. The data comes from cells P27/Q27/R27 and for the second range cells P19/Q19/R19 .


    In the code there is a header with “Clean Output” and then “clears contents” that is not needed by me (I’m just asking, not sure what it’s clearing or if it’s required by the code) but my cells don’t need to be cleared before running the code or after.


    As always a massive thank-for for your patience and experience. Your the best.


    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tVERSION 3.png Views:\t1 Size:\t73.6 KB ID:\t1216738","data-align":"none","data-attachmentid":"1216738","data-size":"full","title":"VERSION 3.png"}[/ATTACH]

  • Hi,


    Glad to hear we are heading in the right direction ... :wink: ( despite your initial fears ...)


    If you do not want the output area to be cleaned ...


    Just insert a apostrophe ... the instruction turns Green ... and will not be executed ...


    Code
    ' Clean Output '''''''''''''''''''''''''''''
    'rng1.Offset(1, 2).Resize(7, 3).ClearContents
    'rng2.Offset(1, 2).Resize(7, 3).ClearContents


    Hope this clarifies

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hi


    I’'ve changed the clear contents issue, great. Uh, I was wondering if you have any time to inset a new column (column D below) in the code for me. The green highlighted area is the result of running the code. The code is very extensive and I do try to understand it but I don’t always understand the spacing or column references it uses to perform it’s task. If you don’t have time I would understand and I really appreciate the code.


    Angela


    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tVERSION 5.png Views:\t1 Size:\t64.6 KB ID:\t1216766","data-align":"none","data-attachmentid":"1216766","data-size":"full","title":"VERSION 5.png"}[/ATTACH]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!