How can I select a Specific Cell and execute a function in another cell if it is found

Question

I want to be able to check (This is the first With statement) if a certain cell value ("H4" in this example) in the open worksheet (I refer to this worksheet as Sheet1 as it is Sheet1 in my workbook) where the code is being written (what is the proper naming scheme for this worksheet where the code is being written, I would like to know) is present in cell A of another worksheet ("HE 171" is the sheet name, I refer to it as "HE171" in this example) in a different workbook ("Md" Main database in this example);

and then if it IS present in the Main Database, I would like to check if (this is the second With statement) the value of that certain cell (again "H4") is present in the Changes Database workbook ("Cd" in this example) in the worksheet "Changes" (The sheet is named "Changes" in this example)

 Option Explicit


Dim Cd As Workbook
Dim Md As Workbook

Dim Changes As Worksheet
Dim HE171 As Worksheet

Dim nConfirmation As Integer

'Actions for when the "Confirm Changes" button is clicked
Private Sub CommandButton1_Click()


    Set Cd = Workbooks.Open("\FILEPATH\Technology_Changes\Changes_Database_IRR_20-2S_New.xlsm")
    Set Md = Workbooks.Open("\FILEPATH\Database_IRR 20-2S New.xlsm")


    Set Changes = Cd.Sheets("Changes")

    On Error Resume Next

    Set HE171 = Md.Sheets("HE 171")


    'Creating the "Yes or No" message box displayed when operators click the "Confirm Changes" button on the Operator Sheet
    nConfirmation = MsgBox("Do you want to send a notification about the sheet update?", vbInformation + vbYesNo, "Sheet Updates")

    'Declares the variable for the string that we will be finding, which is the key in this case (for the With statement)
    Dim FindString As String

    'Declares the variable for the range in which we will be locating the string (for the With statement)
    Dim RNG As Range

    'Sets the string we need to find as the key value which is in cell "H4" of the Operator sheet (for the With Statement)
    FindString = Sheet1.Range("H4").Value

    'Actions if "YES" is clicked when the "Confirm Changes" button is clicked on the Operator Sheet
    If nConfirmation = vbYes Then

        'Opens and activates the Main Database workbook, with "HE 171" as the active sheet
        HE171.Activate

        'Temporarily unprotects the Main Database Workbook and Operator sheet (this is the sheet the code is in)
        ActiveSheet.Unprotect "Swrf"
        Sheet1.Unprotect "Swrf"

        'Searches all of column A in the Main Database in sheet "HE 171" for the string(key)
        With ActiveSheet.Range("A:A")            'searches all of column A
            Set RNG = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        'End With
            '////////////////////////////////////////////////////////////////////////////

            'Actions if the key is present in column A of the MAIN database
            If Not RNG Is Nothing Then

                'Since Key is present in main database, now opens and sets the Changes_Database "Changes" Sheet as active contents
                Changes.Activate

                'Temporarily unprotects the Changes_Database
                ActiveSheet.Unprotect "Swrf"

                'Declares the variable for the string that we will be finding, which is the key in this case (for the With statement)
                Dim FindString2 As String

                'Declares the variable for the range in which we will be locating the string (for the With statement)
                Dim RNG2 As Range

                'Sets the string we need to find as the key value which is in cell "H4" of the Operator sheet (for the With Statement)
                FindString2 = Sheet1.Range("H4").Value

                'Searches all of column A in the Changes_Database "Changes" sheet for the string(key)
                With ActiveSheet.Range("A:A")    'searches all of column A
                    Set RNG2 = .Find(What:=FindString, _
                                    After:=.Cells(.Cells.Count), _
                                    LookIn:=xlValues, _
                                    LookAt:=xlWhole, _
                                    SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext, _
                                    MatchCase:=False)


                    'Actions if the key is present in column A of the Changes_Database (So a change request was previously made for the key and it already has a row in the "Changes" sheet)
                    If Not RNG2 Is Nothing Then

                        'Calls module 13 to set the date and time of the requested change in the "Changes" sheet
                        Call TimeStamp

                        'Calls module 8 to send over the requested changes to the "Changes" sheet
                        Call SendChanges

                        'On Error Resume Next

                        'Protects the Changes_Database
                        ActiveSheet.Protect "Swrf"

                        '////////////////////////////////////////////////////////////////////////////

                        'Actions if the key DOES NOT exist in column A of the Changes_Database


                    Else

                        'Module 14: Adds a new row with the key to the Changes_Database
                        Call NewPart2

                        'Calls module 13 to set the date and time of the requested change in the "Changes" sheet
                        Call TimeStamp

                        'On Error Resume Next

                        'Calls module 8 to send over the requested changes to the "Changes" sheet
                        Call SendChanges

                    End If

               End With

            Else

                        'Module 7:  Adds a new row with the key to the MAIN Database
                        Call NewPart

                        'Module 14: Adds a new row with the key to the Changes_Database
                        Call NewPart2

                        'Module 13: to set the date and time of the requested change in the "Changes" sheet
                        Call TimeStamp

                        'Module 10: Fills in the date and time the key was created for the "HE 171" sheet
                        Call TimeStamp2

                        'On Error Resume Next

                        'Calls module 8 to send over the requested changes to the "Changes" sheet
                        Call SendChanges

            End If

        End With


            'Actions if "No" is clicked when the "Confirm Changes" button is clicked on the Operator Sheet
    Else

            '''''''If nConfirmation = vbNo Then


            'Protects Changes_Database (as it was activated after the Main Database and is therefore the active contents and saves/closes it
            Changes.Activate
            ActiveSheet.Protect "Swrf"
            ActiveWorkbook.Save
            ActiveWorkbook.Close SaveChanges:=True

            'Sets Main Database as active contents to protect it, save it and close it
            HE171.Activate
            ActiveSheet.Protect "Swrf"
            ActiveWorkbook.Save
            ActiveWorkbook.Close SaveChanges:=True

            'Protects Operator Sheet and saves it
            Sheet1.Protect "Swrf"
            'Workbook.Close SaveChanges:=True

    End If

End Sub

Currently, when I run the code for the first time, it is creating a new row for me in both external worksheets ("Changes" from one workbook, "HE171" from another) because it is not present in either workbook, but it fails to copy over any values (in "K" cells from Sheet1 (the worksheet where the code is present) over to the respective cells in the "Changes" sheet (Module 8 is supposed to perform this, here it is below); Here is module 8

    'Module 8: Sends the requested changes over to the "Changes" sheet

Sub SendChanges()

Set Cd = Workbooks.Open("\FILEPATH\Technology_Changes\Changes_Database_IRR_20-2S_New.xlsm")
Set Changes = Cd.Sheets("Changes")

Changes.Activate
ActiveSheet.Unprotect "Swrf"

'////////////////////////////////////////////////////////////////////////////'

'Only executes this macro if the the new/change requested value in column "K" of the Operator sheet has a numerical value present
If Sheet1.Range("K30").Value <> "" Then


'Filters the Changes_Database for the part name & process (the key) which is in cell "H4" of the Operator sheet
ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=Sheet1.Range("H4")


'Copies the changed content in cell "K30" from the Operator Sheet
Sheet1.Range("K30").Copy

'Finds the row in the Changes_Database that has matched all filters and;
'Pastes the value of cell "K30" into the matching parameter cell in the Changes_Database,which is in column 6 in this case
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 6).PasteSpecial xlPasteValues


'Removes all filters and shows all data'
ActiveSheet.ShowAllData


End If
'////////////////////////////////////////////////////////////////////////////'


'Repeats the If and Else code bordered with slashes "////", for all parameter changes in the K column ("KXX")'
If Sheet1.Range("K31").Value <> "" Then
ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=Sheet1.Range("H4")
Sheet1.Range("K31").Copy
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 7).PasteSpecial xlPasteValues

ActiveSheet.ShowAllData
End If


If Sheet1.Range("K32").Value <> "" Then
ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=Sheet1.Range("H4")
Sheet1.Range("K32").Copy
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 8).PasteSpecial xlPasteValues

ActiveSheet.ShowAllData
End If

'On Error Resume Next

Sheet1.Range("K30:K115").ClearContents

'On Error Resume Next

ActiveSheet.Protect "Swrf"
            ActiveWorkbook.Save
            ActiveWorkbook.Close SaveChanges:=True

End Sub

So when I run the code the first time, it is creating a new row for me in the "Changes" sheet, but not copying over any values from column k (module 8 is supposed to do that) — Handreen, Nov 21 '19 at 16:23
How can I post a separate code to show my module as a separate code from this one? — Handreen, Nov 21 '19 at 16:24
Highly suggest you avoid using `Activate` and `ActiveSheet` - see [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and implement its suggestions. — BigBen, Nov 21 '19 at 16:24
I have tried reading it and am having a hard time trying to adjust my code to what that thread is explaining. All I could grasp from it is that I need to refer to "objects" (like a worksheet or workbook) as ranges as opposed to activating them. I am extremely new to VB so please bare with me — Handreen, Nov 21 '19 at 16:28
In my current code, is my "Sheet1" ALWAYS going to refer to the worksheet where the code is in? Even if I activate another workbook/worksheet, as long as I don't put ActiveSheet.Sheet1 for example, is my logic correct? — Handreen, Nov 21 '19 at 16:32
`Sheet1` (referring to the sheet by its codename) will never change. — BigBen, Nov 21 '19 at 16:32
Okay thanks, and how can I split my two codes on my thread? As you can see I tried separating My Private Sub and module with ~~~~~ but it isn't working (module 8 is below) can you please tell me how or edit my post — Handreen, Nov 21 '19 at 16:42
https://www.ozgrid.com/VBA/excel-vba-sheet-names.htm this link says that "The only times you cannot use a sheets CodeName is when you reference a Sheet that is in a different Workbook to the one that the code resides." so basically, even if I activate another workbook, the codename sheets still only refer to the workbook where the code is being written in, regardless of activating another workbook. Can you please confirm my understanding, I have learned plenty with this post! — Handreen, Nov 22 '19 at 02:19

How can I select a Specific Cell and execute a function in another cell if it is found

0 Answers0