How can I adapt this code to run in an automatic way without me entering hundreds of lines inside manually ?

  • Hello,


    I will add 2 images of my actual excel file to be able to explain exactly what I need. Please look at images 1 and 2 (which represent 2 sheets in the same excel file):


    What I need the code to do is this:


    If 'Proces verbal de predare'!B14:B25 matches the value of any of the cells(so not in the same order) from Predare!B4:B500, then whatever value I write inside 'Proces verbal de predare'!H14:H25, do :

    Code
    1. Worksheets("Predare").Range("G4").Offset(0, i - 7) = Worksheets("Predare").Range("G4").Offset(0, i - 7).Value + Target.Value

    But Instead of G4, I need it to be Gn, with n = the matching row of the value written inside 'Proces verbal de predare'!B14:B25



    Here is the full code:



    This code has 3 main IF statements for each value of B14 which I introduced manually. I want the B14 to be automatic, instead of me introducing 400 If statements for each one.


    If you have any solutions to do this, I will be grateful. Also, if you know the solution, please answer with the modified vba code because I'm a complete noob in coding and I don't know how to adapt it myself. Thank you very much.

  • Hi Victor,


    Again, I think this is what you are after however I am confused by having the entry cell in H now and the changed cell in G since this means the loop to look for hidden columns is redundant?


  • Hello Justin,


    I've put your code in excel and it almost does what I need.

    The hidden cells loop is not redundant. This loop works perfectly, please let it in the code.

    The only problem is with the location of the answer.


    I don't need the answer of the vba code to be shown in the same row as the one it was imputed (the row of ws3 H column).


    I need the answer to be like this: IF in ws3 B20 for example is cod AAA, match this code with code AAA from ws2 (which is somewhere between B4:B500) and show the result of the vba code in ws2, column G( with the hidden cells loop ) at the row of ws2 AAA code.


    Example:

    WS3: B14 = 32849, and H14 = 50,

    Then show the result of the vba code in WS2, G4 ( 4 being the matching row of the 32849 code inside WS2.)


    But also, if I change the B14 with B14 = 12375, and H14 = 100,

    Then show the result of the vba code in WS2, G98 ( 98 being the matching row of the 12375 code inside WS2).


    This is what I need the code to do.


    Can it be done like that ?

  • Okay, I think we got there. It makes sense now.



    If this has solved it, can you mark it as the answer on stack exchange as well. Cheers Justin

  • Hello Justin, I'm not in front of a computer anymore, but I will try the code tonight when I get home. (4-5 hours from now).

    If it's solved I will mark it as solved everywhere I posted it. If it doesn't work still, I will message you. Thank you so much for your work so far.

  • Hello Justin,


    This code you wrote here does everything I need it to do. Thank you from the bottom of my heart. You really saved my day. If you didn't do this, I would have had to enter each line manually. Thank you again for this

    Okay, I think we got there. It makes sense now.



    If this has solved it, can you mark it as the answer on stack exchange as well. Cheers Justin