Oggetto richiesto Errore di run-time VBA durante la ricerca di cartelle di lavoro diverse

Sono abbastanza nuovo per VBA quindi un sacco di codice è quello che ho ricercato su Internet e messo insieme. Un po 'di fondo di quello che sto cercando di raggiungere:

Ho due libri di lavoro che hanno un layout identico. Un libro di lavoro è il mio originale where si tiene il codice VBA e l'altro è un tipo di documento sovrapposto. Ho una colonna con i codici nel Sovrapposizione e devo cercare la stessa colonna del libro di lavoro originale per questo codice se la sua ricerca trova copia intera row da sovrapposizione nell'originale e cancella la row trovata nell'originale, se non trovata nella originale per copiare solo fila.

La row del codice che riceve l'errore di runtime è:

Set rngFound = Workbooks("OverLay").Worksheets("Overlay").Range("G:G").Find(What:=r.Value, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) 

Di seguito è un estratto del codice che sto usando.

 Dim sht1 As Worksheet 'Current active worksheet (original version) Dim sht2 As Worksheet 'Worksheet in OverLay Dim rngFound As Range Set sht2 = Workbooks("Overlay").Worksheets("Overlay") With Workbooks("Original").Worksheets("Formatted") lastRow = .Range("G" & .Rows.Count).End(xlUp).Row End With With sht2 For Each Row In .Range("G:G") Set rngFound = Workbooks("OverLay").Worksheets("Overlay").Range("G:G").Find(What:=r.Value, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not rngFound Is Nothing Then rngFound.Copy Workbooks("Original").Worksheets("Formatted").Range(rngFound).PasteSpecial End If Next End With Dim rngData come gamma Dim sht1 As Worksheet 'Current active worksheet (original version) Dim sht2 As Worksheet 'Worksheet in OverLay Dim rngFound As Range Set sht2 = Workbooks("Overlay").Worksheets("Overlay") With Workbooks("Original").Worksheets("Formatted") lastRow = .Range("G" & .Rows.Count).End(xlUp).Row End With With sht2 For Each Row In .Range("G:G") Set rngFound = Workbooks("OverLay").Worksheets("Overlay").Range("G:G").Find(What:=r.Value, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not rngFound Is Nothing Then rngFound.Copy Workbooks("Original").Worksheets("Formatted").Range(rngFound).PasteSpecial End If Next End With Fine con Dim sht1 As Worksheet 'Current active worksheet (original version) Dim sht2 As Worksheet 'Worksheet in OverLay Dim rngFound As Range Set sht2 = Workbooks("Overlay").Worksheets("Overlay") With Workbooks("Original").Worksheets("Formatted") lastRow = .Range("G" & .Rows.Count).End(xlUp).Row End With With sht2 For Each Row In .Range("G:G") Set rngFound = Workbooks("OverLay").Worksheets("Overlay").Range("G:G").Find(What:=r.Value, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not rngFound Is Nothing Then rngFound.Copy Workbooks("Original").Worksheets("Formatted").Range(rngFound).PasteSpecial End If Next End With Con sht2 Dim sht1 As Worksheet 'Current active worksheet (original version) Dim sht2 As Worksheet 'Worksheet in OverLay Dim rngFound As Range Set sht2 = Workbooks("Overlay").Worksheets("Overlay") With Workbooks("Original").Worksheets("Formatted") lastRow = .Range("G" & .Rows.Count).End(xlUp).Row End With With sht2 For Each Row In .Range("G:G") Set rngFound = Workbooks("OverLay").Worksheets("Overlay").Range("G:G").Find(What:=r.Value, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not rngFound Is Nothing Then rngFound.Copy Workbooks("Original").Worksheets("Formatted").Range(rngFound).PasteSpecial End If Next End With 

Inizierò mostrando ciò che è sbagliato:

 Dim sht1 As Worksheet '// <~~ This never gets used? Dim sht2 As Worksheet 'Worksheet in OverLay Dim rngFound As Range Set sht2 = Workbooks("Overlay").Worksheets("Overlay") With Workbooks("Original").Worksheets("Formatted") lastRow = .Range("G" & .Rows.Count).End(xlUp).Row End With With sht2 For Each Row In .Range("G:G") '// 'Row' in the above line will be treated as a variant as it hasn't been declared. '// As such, it will most likely default to a Range object, which means you are '// actually looping through each cell in that column. The lesson here is "be explicit" '// and make sure the code is looking at exactly what you want it to look at. Set rngFound = Workbooks("OverLay").Worksheets("Overlay").Range("G:G").Find(What:=r.Value, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) '// You've already set this sheet to 'sht2' so just use that instead. Also, as '// we know - 'r' has not been set and so causes an error. If Not rngFound Is Nothing Then rngFound.Copy Workbooks("Original").Worksheets("Formatted").Range(rngFound).PasteSpecial '// 'rngFound' is already a range object, no need to wrap it in a Range() method. End If Next End With Dim rngData come gamma Dim sht1 As Worksheet '// <~~ This never gets used? Dim sht2 As Worksheet 'Worksheet in OverLay Dim rngFound As Range Set sht2 = Workbooks("Overlay").Worksheets("Overlay") With Workbooks("Original").Worksheets("Formatted") lastRow = .Range("G" & .Rows.Count).End(xlUp).Row End With With sht2 For Each Row In .Range("G:G") '// 'Row' in the above line will be treated as a variant as it hasn't been declared. '// As such, it will most likely default to a Range object, which means you are '// actually looping through each cell in that column. The lesson here is "be explicit" '// and make sure the code is looking at exactly what you want it to look at. Set rngFound = Workbooks("OverLay").Worksheets("Overlay").Range("G:G").Find(What:=r.Value, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) '// You've already set this sheet to 'sht2' so just use that instead. Also, as '// we know - 'r' has not been set and so causes an error. If Not rngFound Is Nothing Then rngFound.Copy Workbooks("Original").Worksheets("Formatted").Range(rngFound).PasteSpecial '// 'rngFound' is already a range object, no need to wrap it in a Range() method. End If Next End With Fine con Dim sht1 As Worksheet '// <~~ This never gets used? Dim sht2 As Worksheet 'Worksheet in OverLay Dim rngFound As Range Set sht2 = Workbooks("Overlay").Worksheets("Overlay") With Workbooks("Original").Worksheets("Formatted") lastRow = .Range("G" & .Rows.Count).End(xlUp).Row End With With sht2 For Each Row In .Range("G:G") '// 'Row' in the above line will be treated as a variant as it hasn't been declared. '// As such, it will most likely default to a Range object, which means you are '// actually looping through each cell in that column. The lesson here is "be explicit" '// and make sure the code is looking at exactly what you want it to look at. Set rngFound = Workbooks("OverLay").Worksheets("Overlay").Range("G:G").Find(What:=r.Value, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) '// You've already set this sheet to 'sht2' so just use that instead. Also, as '// we know - 'r' has not been set and so causes an error. If Not rngFound Is Nothing Then rngFound.Copy Workbooks("Original").Worksheets("Formatted").Range(rngFound).PasteSpecial '// 'rngFound' is already a range object, no need to wrap it in a Range() method. End If Next End With Con sht2 Dim sht1 As Worksheet '// <~~ This never gets used? Dim sht2 As Worksheet 'Worksheet in OverLay Dim rngFound As Range Set sht2 = Workbooks("Overlay").Worksheets("Overlay") With Workbooks("Original").Worksheets("Formatted") lastRow = .Range("G" & .Rows.Count).End(xlUp).Row End With With sht2 For Each Row In .Range("G:G") '// 'Row' in the above line will be treated as a variant as it hasn't been declared. '// As such, it will most likely default to a Range object, which means you are '// actually looping through each cell in that column. The lesson here is "be explicit" '// and make sure the code is looking at exactly what you want it to look at. Set rngFound = Workbooks("OverLay").Worksheets("Overlay").Range("G:G").Find(What:=r.Value, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) '// You've already set this sheet to 'sht2' so just use that instead. Also, as '// we know - 'r' has not been set and so causes an error. If Not rngFound Is Nothing Then rngFound.Copy Workbooks("Original").Worksheets("Formatted").Range(rngFound).PasteSpecial '// 'rngFound' is already a range object, no need to wrap it in a Range() method. End If Next End With 

Questo può essere scritto nuovamente come tale:

 Dim originalWS As Worksheet '// give your variables meaningful names! Dim overlayWS As Worksheet Dim rngSearchParam As Range Dim rngFound As Range Set originalWS = Workbooks("Original").Sheets("Formatted") Set overlayWS = Workbooks("Overlay").Sheets("Overlay") With overlayWS For Each rngSearchParam In Intersect(.Range("G:G"), .UsedRange) Set rngFound = .Range("G:G").Find(rngSearchParam.Value, LookIn:=xlValues, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not rngFound Is Nothing Then originalWS.Range(rngFound.Address).Value = rngFound.Value End If Next End With Dim rngData come gamma Dim originalWS As Worksheet '// give your variables meaningful names! Dim overlayWS As Worksheet Dim rngSearchParam As Range Dim rngFound As Range Set originalWS = Workbooks("Original").Sheets("Formatted") Set overlayWS = Workbooks("Overlay").Sheets("Overlay") With overlayWS For Each rngSearchParam In Intersect(.Range("G:G"), .UsedRange) Set rngFound = .Range("G:G").Find(rngSearchParam.Value, LookIn:=xlValues, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not rngFound Is Nothing Then originalWS.Range(rngFound.Address).Value = rngFound.Value End If Next End With SearchOrder: = xlByRows, SearchDirection: = xlNext, MatchCase: = Falso) Dim originalWS As Worksheet '// give your variables meaningful names! Dim overlayWS As Worksheet Dim rngSearchParam As Range Dim rngFound As Range Set originalWS = Workbooks("Original").Sheets("Formatted") Set overlayWS = Workbooks("Overlay").Sheets("Overlay") With overlayWS For Each rngSearchParam In Intersect(.Range("G:G"), .UsedRange) Set rngFound = .Range("G:G").Find(rngSearchParam.Value, LookIn:=xlValues, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not rngFound Is Nothing Then originalWS.Range(rngFound.Address).Value = rngFound.Value End If Next End With 

Anche se sembra la ricerca di una colonna, per un valore definito da una cella nella stessa colonna – quindi non sappiate cosa sia il "fine". Speriamo che chiarisca le questioni che hai avuto però