Look for a specific cell from a workbook to another workbook

Question

I need you all to help me with this problem.

The context :

I'm trying (struggling) to create a counter of every proposition the enterprise make to know every date of signature of each proposition. So I create a Worksheet that will serve as a base for everyone, every time they create one they just need to type a number to identify the proposition. I've already created a macro to write the number and the amount of a new proposition in the last free row of the counter Worksheet.

Note : The 2 Worksheet are in different Workbooks.

Now the difficulty is that on the counter sheet there are multiple signatures. Signature A, B, C, D and they never happened on the same day.

Note: I will create a different macro for each signature based on the eventual answer we will find. ( A macro for A, a macro for B, etc…)

Exemple :

ChronoView

Charles creates a Proposition, number: 101 and only do the signature A.

John creates a Proposition, number: 102 and only do the signature B.

I need to be sure that it will enter the date of signature A in the row of Proposition 101 and also enter the date of signature B in the row of Proposition 102

My actual position:

I've made 2 Workbooks :

—Contract (This workbook contains a sheet named “Proposition”)

—Chrono (This workbook contains a sheet named “Counter”)

PropositionView

So if I want my date of signature A for the Proposition 101 in the right row, I need to find the text/value of G6 that is in “Proposition”, and look for it in “Counter” A column. If it functions correctly, it must find the A9 cell then write the formula : TODAY() in H9

What I did (don't laugh, I started macro 3 weeks ago haha):

Sub DateSignatureA()
Dim Chrono As Workbook
Dim Contract As Workbook
Dim refCell As Range
Dim refRow As Range


Set Contract = ActiveWorkbook
Set refCell = Range("G6")
Workbooks.Open Filename:="C:\Users\a.leboedec\Documents\S-T_Experimental\Chrono_Experimental"
Set refRow = Range("A:A").Find(G6, [A8], xlValues, , , xlNext)
    refRow(0,7).Interior.Color = RGB(200, 200, 500)
    refRow(0,7).Formula = "TODAY()"


End Sub

It tells me now “Run-time Error 438”

Note : * * Text * * is where there is the error

Thanks to the one that read it all, and I wait for you all answers.

`Range("A,A")` is not a valid range. `Formula` needs to include the "=". Always helps to tell us exactly which line is causing the error, and what the text of the error is. — Tim Williams, Jan 18 '22 at 17:56
Even if `Range("A:A")` was correct, you aren't going to find it in `refCell`. You may want to look at how to use the `Range.Find` method https://learn.microsoft.com/en-us/office/vba/api/Excel.Range.Find. — Darrell H, Jan 18 '22 at 18:01
Just updated @TimWilliams, and u was right, this is the line where the problem is. — salifgotem, Jan 18 '22 at 18:01
Also if `refRow` is a cell then `refRow(0,7)` is the row *above*, seven cells over. — Tim Williams, Jan 18 '22 at 18:04
@TimWilliams ```refRow(0,7)``` means H9 because ```refRow``` means A9… or I'm wrong ? — salifgotem, Jan 18 '22 at 18:12
Cell indexing is 1-based, not zero-based. Try entering this in the immediate pane and pressing enter: `Selection(0,7).Select` Watch what happens to the selection when you do that. — Tim Williams, Jan 18 '22 at 18:13
@DarrellH I just read so I changed the line with ```Set refRow = Range("A:A").Find(G6, [A8], xlValues, , , xlNext)``` it runs but nothing happened. I think it's because G6 is in “Proposition”, but the actual ```.Find``` is looking in “Counter”. — salifgotem, Jan 18 '22 at 18:15
*Every time* you use `Range`/`Cells` etc you should qualify it with a specific worksheet, otherwise your code risks breaking when the active sheet is not what you expect. See (eg) https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range/28439984#28439984 — Tim Williams, Jan 18 '22 at 18:18
@TimWilliams @Toddleson just resolve the enigma. I tried ```Selection(0,7).Select``` that's what makes the “Run-time Error 438”. — salifgotem, Jan 18 '22 at 18:47

Toddleson · Accepted Answer · 2022-01-18T18:16:06.360

When you do .Find, put the larger search area on the outside like SearchArea.Find( "SearchForThisValue", ... )

When working with multiple workbooks, you should write the whole object path when using Range objects. like

Application.Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1")
'Or
Dim WB As Workbook
Set WB = Application.Workbooks("Book1.xlsx")
Dim WS As Worksheet
Set WS = WB.Worksheets(1)
Dim Target As Range
Set Target = WS.Range("A1")

Doing this is clearer to you and whoever else needs to read the code. This also avoids grabbing values from the wrong sheet or book.

Here is your code, rewritten with the full object paths and the fixed Find statement.

Sub DateSignatureA()
    Dim Chrono As Workbook
    Dim ChronoWS As Worksheet
    Dim Contract As Workbook
    Dim ContractWS As Worksheet
    Dim refCell As Range
    Dim refRow As Range
    
    
    Set Contract = ActiveWorkbook
    Set ContractWS = Contract.ActiveSheet
    Set refCell = ContractWS.Range("G6")
    
    Set Chrono = Workbooks.Open(Filename:="C:\Users\a.leboedec\Documents\S-T_Experimental\Chrono_Experimental")
    Set ChronoWS = Chrono.ActiveSheet
    Set refRow = ChronoWS.Range("A:A").Find( _
                                        refCell.Value, _
                                        After:=ChronoWS.Range("A8"), _
                                        SearchDirection:=xlNext _
                                        )
    
    If Not refRow Is Nothing Then
        refRow.Cells(1, 7).Interior.Color = RGB(200, 200, 500)
        refRow.Cells(1, 7).Value = Date
    End If

End Sub

Also, when working with Find, there is always the case that nothing is found. It is good to test for that case to avoid errors. Not refRow Is Nothing tests to make sure that something was returned from Find before trying to access its properties and methods.

Thank you, incredible, you help me make a big step at work. Thhhhhhhhhx. — salifgotem, Jan 18 '22 at 18:46

Look for a specific cell from a workbook to another workbook

1 Answers1