View original (French)

EXCEL VBA modify the content of a cell in a table

Solved
patrick1870 Posted messages 65 Registration date   Status Member Last intervention   -  
patrick1870 Posted messages 65 Registration date   Status Member Last intervention   -

Hello everyone


I'm diving into writing a macro for Excel for the first time.

I'm facing a problem: how to modify a cell in a table by copying, via a macro, the content of another cell?
I can perform the reverse operation: extract data from a table and place it in another cell.

My data is located in two separate sheets within the same workbook: one sheet "saisie" and another sheet "compteurs".

Here is a simplified part of the macro that should help clarify my problem, I hope :)

' MACRO "VALIDATION"

Dim compteur1 As Integer
    
    
'   1 -  retrieving the counter
    
    Sheets("saisie").Select
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R6C3,Compteurs!R3C1:R50C7,2)"
 
'   2 -  various processing on the data from the "saisie" sheet
 
'   3 -  incrementing compteur1
    
     compteur1=compteur1 +1
     range("F5") = compteur1
'   
'    4 -  copy the content of F5 into the cell of the table on the sheet
'         "compteurs" (the same cell used for the retrieval
'          of point 1 above) ... and that's where I'm stuck ....

Thank you in advance to anyone who can help me.

6 answers

  1.
    yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   Ambassadeur 1 588
     

    Hello,

    I don't understand "the same cell as that used for the retrieval of 1- above."

    Maybe provide an example and explain this clearly in English.

    0
    1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
       

      I think you are trying to achieve this:

      Dim tabl As Range, trouve As Range Set tabl = Sheets("compteurs").[a3:a50] Set trouve = tabl.Find(Sheets("saisie").[c6]) If Not trouve Is Nothing Then trouve.Offset(, 2) = trouve.Offset(, 2) + 1 End If
      0
  2. patrick1870 Posted messages 65 Registration date   Status Member Last intervention   17
     

    Sorry for not being clear enough... I thought I had expressed myself in French. Perhaps this is due to the fact that I don't master VBA and might not have used the exact words.

    In the 1 - I will search in the table (which is in the 'Compteurs' sheet) for a data point, using

    VLOOKUP(the content of cell C6 from the "saisie" sheet; table A3:G50 from the "Compteurs" sheet; 2nd column) **

    and it is in this same cell of the table that I want to copy the content of cell F5 at the end of the macro.

    Is it clearer like that? If not, what kind of example would you like?

    ** transcription of "=VLOOKUP(R6C3,Compteurs!R3C1:R50C7,2)"

    0
    1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
       

      In fact, you are describing your code instead of explaining the result you want to achieve.

      An example of an example:

      Let there be an array:

       123 456 789 555 888 999

      cell C6 of the "saisie" sheet contains 555.

      the goal is to replace 999 with the content of cell F5 of the "saisie" sheet.

      .

      Suggestion, very close to the suggestion in #2:

      Set tabl = Sheets("compteurs").[a3:a50] Set trouve = tabl.Find(Sheets("saisie").[c6]) If Not trouve Is Nothing Then trouve.Offset(, 2) = Sheets("saisie").[f5] End If
      0
    2. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
       

      Have you tested the code I suggested on April 22, 2024, at 4:15 PM?

      1
  3. patrick1870 Posted messages 65 Registration date   Status Member Last intervention   17
     

    Hello,

    My apologies for the long silence (vacation with grandkids :)).

    You understood from the start what I wanted to do.
    Unfortunately, a blunder on my part made me think that I had explained my problem so poorly that you didn't understand it at all.
    I just got back to it and your first macro corresponds exactly to what I was looking for.

    I am incredibly grateful for your help because I probably would never have managed it on my own. I haven't fully understood the instructions contained in the macro but the main thing is that it works. As I mentioned, I am a novice (at over 75!) in VBA.
    Thanks again.

    0
    1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
       

      Great, feel free to reach out if you want to understand and/or modify the macro.

      1
  4. patrick1870 Posted messages 65 Registration date   Status Member Last intervention   17
     

    Hello yg_be

    I am reaching out to you again, risking abusing your patience, because I really don't understand what is happening.

    I have completely rewritten my project, changing the names of the sheets, variable names, cell locations, etc.

    I thought I had rewritten the macro identically, taking these changes into account. But now it is not working anymore. Moreover, I have overwritten the old version of the macro (the one that worked) without being able to recover it.

    The variable "trouve" stubbornly keeps the value "Nothing", which makes the macro inoperative.
    Could you tell me what might be the cause and how to fix it?

    Thank you in advance for any help you could provide.

    ---------

    Below is the new macro:

     Sub YY6_MAJ_compteurs() ' ' 6. Update counters ' ' 6.1 entry counter ' Sheets("YY_Paramètres").Select Range("J2").Select Range("J2") = Sheets("YY_Saisie").[G9] ' ' 6.2 monthly counter by operation type ' 'in the table a3:g50 of the sheet "YY_Paramètres" we find : '- the month in column A and '- in columns B to G the counters corresponding to the 6 types of operations ' Dim tabl As Range, trouve As Range Dim colonn As Integer colonn = Sheets("YY_Saisie").[i9] 'the cell i9 of the sheet YY_Saisie contains the column number of the table Set tabl = Sheets("YY_Paramètres").[a3:g50] Set trouve = tabl.Find(Sheets("YY_Saisie").[c9]) 'in the cell c9 of the sheet YY_Saisie is the month If Not trouve Is Nothing Then trouve.Offset(, colonn) = trouve.Offset(, colonn) + 1 End If End Sub
    0
    1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
       

      Hello,

      I assume, without seeing your spreadsheet, that the value in [c9] on the input sheet is not exactly the same as any of the values in the table. What matters is the value stored in a cell, not the value displayed in that cell.

      By the way, why are you searching the entire table and not just column A?

      0
  6. patrick1870 Posted messages 65 Registration date   Status Member Last intervention   17
     

    Hello yg_be

    Thank you very much for your reply.

    I searched for hours to find where the error could be, but nothing worked.

    However, I found an old version of the macro on a USB stick... which worked back then.

    I took this macro and modified it to account for the changes that have occurred and... it works!!!

    Yet the new line "set trouve..." (which one might suspect is responsible for my difficulties) is identical to the one in the macro that I copied in my previous message.

    I am starting to worry about the rest of my project as this is not the first time I have encountered difficulties that I do not understand the cause of. For example, I had formulas that appeared in the input sheet but did not execute... I searched for a very long time before resigning myself to rewriting the entire input page in a new sheet, with the formulas, formats, conditional formatting, data validation... I also had problems with available memory... yet I have 16 GB of RAM and had no other programs open except Excel.

    I am using Excel from the 365 subscription suite and I imagine that this version is not buggy.

    I am not very optimistic about the future. I had set myself a challenge to give my old brain a workout, but I am afraid that this is beyond my reach.

    In any case, I sincerely thank you for the interest and patience you have shown towards my beginner problems.

    Patrick.

    0
    1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
       

      "Available memory issues": I often encounter this in the VBA editor, so I close Excel and then reopen it.

      If you share your file, I could look into the cause of the problem with "set trouve".

      0
  7. patrick1870 Posted messages 65 Registration date   Status Member Last intervention   17
     

    Hello yg_be

    and thank you for your message.

    The macro is working now, even though I don't understand why it wasn't working before.

    So I'll continue to move forward ...

    See you

    Patrick

    0