none
Macro to clear content of cells in one sheet that matches cells content of another RRS feed

  • Question

  • i am trying to create a macro that will delete two cells in my database that matches the same content from another sheet.  I have a database and a dashboard. i want to the macro to look for the same information in cell range C12:C15 on sheet "TOC" and then look for the same information in sheet Raw Data in column B. Once found clear the contents of the cells in column G and H of Raw Data sheet. So for example i have data in C12, the macro will look in column B for the same data. If the data is in B56 the macro will clear the content of G56 and H56.  If that is too confusing i can send you my worksheet as an example of what i want it to do.
    Saturday, September 9, 2017 4:58 PM

Answers

  • Hi RCSmart01,

    you can try to refer code below.

    Private Sub CommandButton1_Click()
    Call demo
    End Sub
    


    Sub demo()
    Dim rng, rng1 As Range
    Dim row, row1 As Range
    Dim cell, cell1 As Range
    Dim sht1, sht2 As Worksheet
    Set sht1 = Sheets(1)
    Set sht2 = Sheets(2)
    Set rng = sht1.Range("C12:C15")
    Set rng1 = sht2.Range("B1:B" & Range("B1").End(xlDown))
    For Each row In rng.Rows
      For Each cell In row.Cells
       ' Debug.Print (cell.Value)
            For Each row1 In rng1.Rows
                For Each cell1 In row1.Cells
                    'Debug.Print (cell1.Value)
                    If cell.Value = cell1.Value Then
                        Debug.Print ("matched")
                        cell1.Offset(0, 5).Value = ""
                        cell1.Offset(0, 6).Value = ""
                    End If
                Next cell1
            Next row1
      Next cell
    Next row
    End Sub

    Output:

    this will work for comparing the value from 2 sheets and take action according to that.

    you said that there is one database and dashboard.

    do you mean you have a separate 2 workbook with these names and TOC and Raw Data worksheets are in these 2 workbooks.

    then you need to add this code to the workbook which have Raw data sheet.

    then you need to create 2 objects for workbook.

    you need to open other workbook at the stating of the sub.

    assign both workbooks to workbook objects and use it to assign value of worksheet object to refer the worksheet properly.

    if you have any questions then let me know about that.

    I will try to provide further suggestion.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, September 11, 2017 7:58 AM
    Moderator

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, September 11, 2017 2:15 AM
  • Hi RCSmart01,

    you can try to refer code below.

    Private Sub CommandButton1_Click()
    Call demo
    End Sub
    


    Sub demo()
    Dim rng, rng1 As Range
    Dim row, row1 As Range
    Dim cell, cell1 As Range
    Dim sht1, sht2 As Worksheet
    Set sht1 = Sheets(1)
    Set sht2 = Sheets(2)
    Set rng = sht1.Range("C12:C15")
    Set rng1 = sht2.Range("B1:B" & Range("B1").End(xlDown))
    For Each row In rng.Rows
      For Each cell In row.Cells
       ' Debug.Print (cell.Value)
            For Each row1 In rng1.Rows
                For Each cell1 In row1.Cells
                    'Debug.Print (cell1.Value)
                    If cell.Value = cell1.Value Then
                        Debug.Print ("matched")
                        cell1.Offset(0, 5).Value = ""
                        cell1.Offset(0, 6).Value = ""
                    End If
                Next cell1
            Next row1
      Next cell
    Next row
    End Sub

    Output:

    this will work for comparing the value from 2 sheets and take action according to that.

    you said that there is one database and dashboard.

    do you mean you have a separate 2 workbook with these names and TOC and Raw Data worksheets are in these 2 workbooks.

    then you need to add this code to the workbook which have Raw data sheet.

    then you need to create 2 objects for workbook.

    you need to open other workbook at the stating of the sub.

    assign both workbooks to workbook objects and use it to assign value of worksheet object to refer the worksheet properly.

    if you have any questions then let me know about that.

    I will try to provide further suggestion.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, September 11, 2017 7:58 AM
    Moderator
  • Hi RCSmart01,

    I can see that after creating this thread, you did not follow up this thread.

    I can see that the solution given by me can solve your issue.

    I suggest you to check and test it on your side.

    if you think it can solve your issue then mark this suggestion as an answer.

    so that we can close this thread, if you do not mark the answer then this thread will remain open.

    if you have any further question regarding the same issue then let me know about that.

    I will try to provide you further suggestion to solve the issue.

    I also want to suggest you to check the other thread that you had created.

    I also provided a solution in that.

    Macro to move data from one sheet to table and clear contents

    so check it and if you think the suggestion can solve your issue then mark  the answer.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 15, 2017 7:46 AM
    Moderator
  • Excel 2010/2013/2016 Power Query (aka Get & Transform)
    No formulas, no VBA macro.
    Cleared items will be restored if needed if "TOC" input is edited later.
    http://www.mediafire.com/file/0dibym7w4whzyme/09_15_17.xlsx


    Friday, September 15, 2017 2:20 PM