PDA

View Full Version : Searching for cell value in another spreadsheet



allison
04-18-2008, 11:31 AM
I'm trying to accomplish the following:

Spreadsheet A is a subset of the Master. There is a single unique identifier in column A.
I want to update the master from spreadsheet A.

I can not figure out how to find the correct row to update in the master.
I need to get the value in A1 from spreadsheet A and find the same value in the Master. Update the master and then go to the next row in spreadsheet A until there are no more rows in spreadsheet A.

Any suggestions?

tstav
04-18-2008, 11:53 AM
Hi allison,

When you say Spreadsheet, could it be that you mean Worksheet?

What data does the Master already contain? Is Master a sheet, too? (or a spreadsheet)
With what data do you want to update the Master?

Maybe attaching a file with sample data would be better.
Regards,
tstav

allison
04-18-2008, 12:00 PM
Sorry....yes - I mean worksheets.

The Master is in a different workbook too.

I'm copying all of the columns that are in the individual worksheet into the master worksheet when the script # is the same.

I'm attaching a sample workbook - biggest difference is that the individual and masters are in separate workbooks.

tstav
04-18-2008, 01:00 PM
So allison...

If this code is run from a separate file (not from within the source or the target file) then leave the code as is.
Otherwise, check my comments before running the code. You must make a minor change to it.

Sub FindCellValueAndCopyRange()
Dim indivR As Range, masterR As Range, cell As Range
Dim masterRow As Long
Dim shtIndiv As Worksheet, shtMaster As Worksheet

'''If this code is placed in the source file, you need to comment out the 'Workbooks.Open' line
'''and in the 'Set shtIndiv' line, change the Activeworkbook to ThisWorkbook.
'''Otherwise leave them as they are
Workbooks.Open "C:\source.xls" '<-- Change with source filename
Set shtIndiv = ActiveWorkbook.Worksheets("Individual")

'''If this code is placed in the target file, you need to comment out the 'Workbooks.Open' line
'''and in the 'Set shtMaster' line, change the Activeworkbook to ThisWorkbook.
'''Otherwise leave them as they are
Workbooks.Open "C:\Target.xls" '<-- Change with target filename
Set shtMaster = ActiveWorkbook.Worksheets("Master")

'Find the range that contains all the cells in col "A" of source sheet
With shtIndiv
Set indivR = .Range(.Range("A5"), .Range("A" & .Rows.count).End(xlUp))
End With

'Find the range that contains all the cells in col "A" of target sheet
With shtMaster
Set masterR = .Range(.Range("A5"), .Range("A" & .Rows.count).End(xlUp))
End With

On Error Resume Next
'Try to find each cell of this range, in Master
For Each cell In indivR
masterRow = WorksheetFunction.Match(cell.Value, masterR, 0)
If Err Then
Err.Clear
Else '<--- if found, write to master
cell.Resize(, 6).Copy
masterR.cells(masterRow).Resize(, 6).PasteSpecial
Application.CutCopyMode = False
End If
Next
End Sub


Edit: minor changes to the comments lines