Copy a cell from one workbook to another if value searched found

AS1

New Member
Joined
Jan 23, 2012
Messages
2
I have 2 workbooks. WB1 has phone #s in column N-P, WB2 has phone #s in column J. For each phone # in column J if found in WB1 I want to copy column A of the row found (first instance) from WB1 to Column A in WB2 (it contains ID #) . I was able to accomplish it by using offset. However, I would like to be able to use Range("A" & Rowfound).value but get error-13 type mismatch. Below is the version (minus reductions that are not relevant to the issue at hand) that works. After End Sub I have few lines that I tried replacing the section enclosed in ********** towards the end of the macro

Sub FindDonorID()
' phones are in column L & M. We first search the phone from column L. If found we do not search phone from column M
' If Column L is blank or not found in donors file we check to see if there is a phone in column M and if yes, we search that phone
' If phone found in donors file we take the donorID from column A and put it in column A of donations file

Const w1 As String = "Book1.xlsx"
Const w2 As String = "Book2.xlsm"

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim cl As Range 'phone #
Dim ws1Rng As Range ' All 3 phone columns
Dim ws1RngA As Range 'Define column A in Book1 as a range (copy from
Dim ws2Rng As Range ' Column J
Dim ws2RngA As Range Define column A in Book2 as a range (copy to
Dim aCell As Range ' Will contain the match in the master file
Dim lRowW1 As Long ' Last row to check in Master file
Dim lRowW2 As Long ' last row to check in donations file
Dim ColumnOffset As Long ' to determine how many columns between the phone & the donorID
Dim ColumnFound As Long ' Which of the 3 column the phone was found
Dim RowFound As Long
Dim DonorId As Long ' the ID from column A
Dim RowNumber As Long ' Row # of the phone we're looking for Book2
Dim Phone As String


Set wb1 = Workbooks(w1)
Set ws1 = wb1.Sheets(1)
Set wb2 = ThisWorkbook
Set ws2 = wb2.Sheets(1)

'DonorId = " "
' Work with First workbook to get last row and define the range
With ws1
lRowW1 = .Range("A" & .Rows.Count).End(xlUp).Row
Set ws1Rng = .Range("N2:P" & lRowW1)
Set ws1RngA = .Range("A2:A" & lRowW1) ' for copy cell Annn
End With
' Work with second workbook to get last row and define the range
With ws2
lRowW2 = .Range("L" & .Rows.Count).End(xlUp).Row
Set ws2Rng = .Range("J2:J" & lRowW2)
Set ws2RngA = .Range("A2:A" & lRowW2) ' for pasting into cell Annn
For Each cl In ws2Rng
RowNumber = cl.Row
If Range("A" & RowNumber).Value <> "" And Range("A" & RowNumber).Value <> " " Then ' If there is an ID in column A skip this Row
GoTo NextCl
End If
Found = "no"
Phone = cl ' cl contain the phone # from column J
If (Phone = "" Or Left(Phone, 1) = " ") Then ' If there is no phone # in J skip this row
GoTo NextCl
End If

' Do the find
FindPhone:
Set aCell = ws1Rng.Find(what:=Phone, LookIn:=xlValues)
If Not aCell Is Nothing Then
ColumnFound = aCell.Column
RowFound = aCell.Row
Found = "yes"
Else
Found = "no"
End If

NoPhone:
If Found = "no" Then if phone not found
Range("A" & RowNumber).Value = " " 'put space in column A
GoTo NextCl
End If

' Found phone in master list, set the offset to get donorID deppends on which column the phone was found
'***********************************************************************************************************************************************************************
ColumnOffset = -(ColumnFound - 1) ' we need to know in which column the phone was found to determine how far it's from column A
DonorId = aCell.Offset(, ColumnOffset).Value 'Take the ID from Column A
Range("A" & RowNumber).Value = DonorId
'*******************************************************************************************************************************************************************
NextCl:
Next
End With

EndSub:
wb2.Save
End Sub

' 1st option of replacing the section above: This gives error-13 type mismatch
DonorId = Workbooks(wb1).Worksheets(ws1).Range("A" & aCell.Row).Value
wb2.Sheets(ws2).Range("A" & RowNumber) = DonorID

' 2nd option of replacing the section above: this gives the value of "A & rowfound" but from Book2 instead of Book1 although the range is in Book1
With ws1RngA
DonorId = Range("A" & aCell.Row).Value
End With
With ws2RngA
Range("A" & RowNumber).Value = DonorId
End With

' 3rd option using copy & Paste also gives error-13
Workbooks(wb1).Worksheets(ws1).Range("A" & aCell.Row).Copy Workbooks(wb2).Worksheets(ws2).Range("A" & RowNumber).Paste


Any help will be greatly appreciated.
Thanks
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Forum statistics

Threads
1,214,978
Messages
6,122,549
Members
449,089
Latest member
davidcom

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top