View Full Version : [SOLVED:] Search all open workbook and if find the match data , clean the cell on Column A
parscon
02-15-2014, 02:22 AM
I have about 10 workbooks and each workbook have about 5 sheets and I have another workbook Main that have only one sheet and have a data only in one column A .
Now i need a VBA code that search data on Workbook main on sheet 1 column A with all other sheets on all workbook with range A to P if does not find any data on all workbooks and sheets , delete that cell on column A .
Note : after complete search on all workbook and sheets will delete that data on cell column A on workbook main .
Hope you understand what i need .
westconn1
02-15-2014, 03:28 AM
you can try like
Set mn = Workbooks("main.xls")
lastrow = mn.Sheets(1).Cells(mn.Rows.Count, 1).End(xlUp).Row
arr = mn.Sheets(1).Range("a1:a" & lastrow)
For i = 1 To UBound(arr)
For Each wb In Workbooks
If Not wb.Name = "Main.xls" Then
For Each ws In wb.Worksheets
Set fnd = ws.Range("A:P").Find(a(i, 1)) 'xlwhole if required
If Not fnd Is Nothing Then Exit For
Next
If Not fnd Is Nothing Then Exit For
End If
Next
If fnd Is Nothing Then
Set delrow = mn.Sheets(1).Range("a:a").Find(arr(i, 1))
If Not delrow Is Nothing Then delrow.EntireRow.Delete
End If
Nextuntested, will check all open workbooks, except main, assumes that all workbooks to search are already open, but easy to change if not
parscon
02-15-2014, 03:38 AM
Thank you very much bit when run the VBA code show me the error : Sub Or Function not defined
and also i have a question : is this VBA code first check all open workbooks after that delete ?
And also i need to clean the cell not delete the row on workbook main . only clean data on the cell in column A Sheet1
Thank you again .
Note: i use Excel 2013
westconn1
02-15-2014, 04:52 AM
And also i need to clean the cell not delete the row on workbook mainif not delrow is nothing the delrow.clear
Sub Or Function not definedon which line?
is this VBA code first check all open workbooks after that delete ?all sheets in all open workbooks, then delete if not found
Note: i use Excel 2013change workbook names to suit
parscon
02-15-2014, 05:08 AM
on which line?
Set fnd = ws.Range("A:P").Find(a(i, 1)) 'xlwhole if required
Complie Error
Sub Or Function not defined
and yellow mark the first line Sub Datacheck()
and blue mark the latest a (a
Sub Datacheck()
Set mn = Workbooks("main.xls")
LastRow = mn.Sheets(1).Cells(mn.Rows.Count, 1).End(xlUp).Row
arr = mn.Sheets(1).Range("a1:a" & LastRow)
For i = 1 To UBound(arr)
For Each wb In Workbooks
If Not wb.Name = "main.xls" Then
For Each ws In wb.Worksheets
Set fnd = ws.Range("A:P").Find(a(i, 1)) 'xlwhole if required
If Not fnd Is Nothing Then Exit For
Next
If Not fnd Is Nothing Then Exit For
End If
Next
If fnd Is Nothing Then
Set delrow = mn.Sheets(1).Range("a:a").Find(arr(i, 1))
If Not delrow Is Nothing Then delrow.EntireRow.Delete
End If
Next
End Sub
westconn1
02-15-2014, 02:13 PM
typo
Set fnd = ws.Range("A:P").Find(arr(i, 1))
parscon
02-16-2014, 04:05 AM
Dear westconn1 ,Thank you but another error :
on line :
LastRow = mn.Sheets(1).Cells(mn.Rows.Count, 1).End(xlUp).Row
And the error is :
Run-time error '438': Object doesn't support this property or method
westconn1
02-16-2014, 04:14 AM
try
LastRow = mn.Sheets(1).Cells(mn.sheets(1).Rows.Count, 1).End(xlUp).Row
parscon
02-16-2014, 04:18 AM
Sorry , It is work i forget add .xlsx for main in your code.
Thank you very much for your big help
Sub Datacheck()
Set mn = Workbooks("main")
LastRow = mn.Sheets(1).Cells(mn.Sheets(1).Rows.Count, 1).End(xlUp).Row
arr = mn.Sheets(1).Range("a1:a" & LastRow)
For i = 1 To UBound(arr)
For Each wb In Workbooks
If Not wb.Name = "main" Then
For Each ws In wb.Worksheets
Set fnd = ws.Range("A:P").Find(arr(i, 1)) 'xlwhole if required
If Not fnd Is Nothing Then Exit For
Next
If Not fnd Is Nothing Then Exit For
End If
Next
If fnd Is Nothing Then
Set delrow = mn.Sheets(1).Range("a:a").Find(arr(i, 1))
If Not delrow Is Nothing Then delrow.EntireRow.Delete
End If
Next
End Sub
parscon
02-16-2014, 05:10 AM
Just there is a problem:
In column A on sheet 1 workbook main , i have a number 2323 and also in other workbook i have data like 22202323 and when run VBA code it will not delete the 2323 on column A on sheet 1 workbook main .i think it must be check the exact item .
I tested this but same result , it will not check exact .
Set fnd = ws.Range("A:P").Find(arr(i, xlWhole))
Please help me on this also
westconn1
02-16-2014, 01:01 PM
Set fnd = ws.Range("A:P").Find(arr(i, 1),,, xlwhole)
this was in some other thread of yours, it is the lookat parameter of the find method
parscon
02-16-2014, 02:53 PM
Dear westconn1
Thank you very much , and it is work very fine .
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.