Macro to search for input criteria and copy and paste selected cells to another sheet

[jennifercorp]

Hello,

I am very new to building macros and this is my first post (hopefully of many ). I have been able to patch together the below macro, but am not able to get it to work properly. The goal is to be able to click a button which will prompt for a loan number, once the loan number is received, it will check sheet "Retrieval Log" column "D" for a match. If a match is received I would like it to grab the data in that row and paste it to various cells on sheet "BCS Submition Form". The code that I have is below. I am currently getting a "Copy method of Range class failed error".

Thank you in advance to anyone who may be able to help!

Private Sub CommandButton9_Click()
Dim Prompt, LoanNumber As String
Prompt = "Loan Number"
LoanNumber = InputBox(Prompt)

If LoanNumber = "" Then Exit Sub

LastRow = Sheets("Retrieval Log").Range("D" & Rows.Count).End(xlUp).Row
For Each Cell In Sheets("Retrieval Log").Range("D1:D" & LastRow)
If Cell = LoanNumber Then
FoundRow = Cell.Row
Dim ws1 As Worksheet, ws2 As Worksheet
Dim r1 As Range, r2 As Range
Dim v1 As Variant, v2 As Variant

Set ws1 = Workbooks("BCS - TL - MASTER.xls").Sheets("BCS Submition Form")
Set ws2 = Workbooks("BCS - TL - MASTER.xls").Sheets("Retrieval Log")

' specify the cells that will contain information in sheet1
v1 = Array("C9", "C10", "C11", "C12", "C13", "I9", "I10", "I11", "I12", "I13", "C18", "G18", "M18", "C19", "E19", "I19", "M19", "D23", "H23", "K23", "M23", "H24", "K24", "M24", "D26", "H26", "K26", "M26", "H27", "K27", "M27", "D29", "H29", "K29", "M29", "D30", "H30", "K30", "M30", "D32", "H32", "K32", "M32", "D33", "H33", "K33", "M33", "D34", "H34", "K34", "M34", "D35", "H35", "K35", "M35", "D37", "H37", "K37", "M37", "D38", "H38", "K38", "M38", "D39", "H39", "K39", "M39", "D40", "H40", "K40", "M40", "D42", "H42", "K42", "M42", "D44", "H44", "K44", "M44", "C47", "E47", "I47", "L47", "C48", "E48", "I48", "L48", "D51", "I51", "D52", "D55", "D56", "B63", "C63", "F63", "I63", "K63", "B69", "B76")
' in the same order specify what columns that info would be
' placed in in sheet2
v2 = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU")
' find the next open row in sheet2 using column A

Debug.Print UBound(v1), UBound(v2)
rw = ws2.Cells([FoundRow], "A")
For i = LBound(v1) To UBound(v1)
Set r1 = ws1.Range(v1(i)) '<== added a change here
Set r2 = ws2.Cells([FoundRow], v2(i))

r2.Copy r1

r2.Value = ""

Next i
End If
Next Cell
If IsEmpty(FoundRow) Then
MsgBox "Sorry, loan number " _
& LoanNumber & " was not saved for retrieval."
End If
End Sub

 

[jennifercorp]

I know that this has to be where the problem is:

Debug.Print UBound(v1), UBound(v2)
rw = ws2.Cells([FoundRow], "A")
For i = LBound(v1) To UBound(v1)
Set r1 = ws1.Range(v1(i)) '<== added a change here
Set r2 = ws2.Cells([FoundRow], v2(i))

 

[xladept]

Hi jennifercorp,

I'm guessing that the brackets around FoundRow may be in violation as brackets indicate a range and FoundRow is probably a long integer????

 

[jennifercorp]

Thanks

I tried it without the brackets. Still no luck. I think it might be an incorrect usage of "i" but I am a bit too inexperienced to read it to make sure.

I changed it up a bit and am still getting the same error. Right now I am sitting at:

Debug.Print UBound(v2), UBound(v1)
rw = ws2.Cells(FoundRow, "A")
For i = LBound(v2) To UBound(v2)
Set r1 = ws1.Range(v1(i))(1) '<== added a change here
Set r2 = ws2.Cells(FoundRow, v2(i))

 

[jennifercorp]

ADVERTISEMENT

If it helps, I have basically the same code running the opposite direction that works without a problem. The "BCS Submition Form" sheet is a form that users fill in information and then hit submit. The information that they entered is pasted into the next available row on "Retrieval Log" and cleared from the form. So, this code works:

Private Sub CommandButton8_Click()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim r1 As Range, r2 As Range
Dim v1 As Variant, v2 As Variant
Set ws1 = Workbooks("BCS - TL - MASTER.xls").Sheets("BCS Submition Form")
Set ws2 = Workbooks("BCS - TL - MASTER.xls").Sheets("Retrieval Log")
' specify the cells that will contain information in sheet1
v1 = Array("C9", "C10", "C11", "C12", "C13", "I9", "I10", "I11", "I12", "I13", "C18", "G18", "M18", "C19", "E19", "I19", "M19", "D23", "H23", "K23", "M23", "H24", "K24", "M24", "D26", "H26", "K26", "M26", "H27", "K27", "M27", "D29", "H29", "K29", "M29", "D30", "H30", "K30", "M30", "D32", "H32", "K32", "M32", "D33", "H33", "K33", "M33", "D34", "H34", "K34", "M34", "D35", "H35", "K35", "M35", "D37", "H37", "K37", "M37", "D38", "H38", "K38", "M38", "D39", "H39", "K39", "M39", "D40", "H40", "K40", "M40", "D42", "H42", "K42", "M42", "D44", "H44", "K44", "M44", "C47", "E47", "I47", "L47", "C48", "E48", "I48", "L48", "D51", "I51", "D52", "D55", "D56", "B63", "C63", "F63", "I63", "K63", "B69", "B76")
' in the same order specify what columns that info would be
' placed in in sheet2
v2 = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU")
' find the next open row in sheet2 using column A
Debug.Print UBound(v1), UBound(v2)
rw = ws2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
For i = LBound(v1) To UBound(v1)
Set r1 = ws1.Range(v1(i))(1) '<== added a change here
Set r2 = ws2.Cells(rw, v2(i))

r1.Copy r2
r1.Value = ""
Next i
End Sub

So I think that the error that I am getting on the above code when I am trying to retrieve information from the "Retrieval Log" to "BCS Submition Form" is in the code:

Debug.Print UBound(v2), UBound(v1)
rw = ws2.Cells(FoundRow, "A")
For i = LBound(v2) To UBound(v2)
Set r1 = ws1.Range(v1(i))(1) '<== added a change here
Set r2 = ws2.Cells(FoundRow, v2(i))

because I am no longer transferring from various cells a row, but now from a row to various cells. Any thoughts?

 

[xladept]

Hi jennifercorp,

Get rid of the (1)????

 

[jennifercorp]

ADVERTISEMENT

Thanks!

I removed the 1 but still get the same error "Copy Method of Range Class Failed" Right now I am at:

Debug.Print UBound(v2), UBound(v1)
rw = ws2.Cells(FoundRow, "A")
For i = LBound(v2) To UBound(v2)
Set r1 = ws1.Range(v1(i)) '<== Removed (1)
Set r2 = ws2.Cells(FoundRow, v2(i))

Thank you for the reply and please let me know if you have any other ideas.

 

[jennifercorp]

------bump

 

[jennifercorp]

The code giving the error is:

r2.Copy r1

 

[Mohammad Basem]

Hi,

I have few queries and comments.

1. Does loan number appear more than once in column D?

Why this question?

You have used For…Each…Next to go through every cell in column D and once the loan number is found data will be copied, and the loop continues to the last row.

If loan number appears only once then the loop shall be exited after finishing the copy as there is no need for extra unnecessary process, or you can use Range.Find. Otherwise, if loan number appears more than once, then first copied data is overwritten whenever loan number is found.

2. What is this line for?

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]rw = ws2.Cells(FoundRow, "A")[/COLOR][/SIZE][/FONT]

rw defined but never used.

3. Yom may replace v2(i) with i+1. You don’t need the v2 array.

4. What is the value of i when the macro breaks?

Other than the above I don’t see why the code is not working. It is difficult to judge without having sample data to trace the code. Is it possible to have sample file that produces the error?