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
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