Find and Select Rows

[pwill]

Hi can anyone help with a macro that I could use with a drop down list that will find and select rows based on Name from column A and the Ref from column C (Sheet7)

IE if I wanted to find 1 as the name from column A and 10 as the Ref in column C in this example A14:J16 would be selected or if I wanted to find 0 as the Name and 15 as the Ref A8:J10 would be selected and so on... and then copy the selected range to Sheet3 A3:J

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Range	Ref	A	B	C	D	E	F	G
2	0	1 - 5	5	0	0	0	0	0	0	0
3	0	6 - 10	5	1	1	1	1	1	1	1
4	0	11 - 15	5	2	2	2	2	2	2	2
5	0	1 - 10	10	3	3	3	3	3	3	3
6	0	11 - 20	10	4	4	4	4	4	4	4
7	0	21 - 30	10	5	5	5	5	5	5	5
8	0	1 - 15	15	6	6	6	6	6	6	6
9	0	16 - 30	15	7	7	7	7	7	7	7
10	0	31 - 45	15	8	8	8	8	8	8	8
11	1	1 - 5	5	0	0	0	0	0	0	0
12	1	6 - 10	5	1	1	1	1	1	1	1
13	1	11 - 15	5	2	2	2	2	2	2	2
14	1	1 - 10	10	3	3	3	3	3	3	3
15	1	11 - 20	10	4	4	4	4	4	4	4
16	1	21 - 30	10	5	5	5	5	5	5	5
17	1	1 - 15	15	6	6	6	6	6	6	6
18	1	16 - 30	15	7	7	7	7	7	7	7
19	1	31 - 45	15	8	8	8	8	8	8	8
20
21

<tbody>
</tbody>

Any help would be appreciated

Regards

pwill

 

[pwill]

Hi,

Its saying you can't paste here because the copy area and paste area arn't the same size?

 

[mrshl9898]

That is strange, can you see the area it's copying?

 

[pwill]

Yes it goes to sheet3 but then stops with the debug message

I've just reopened my workbook and going to try again from scratch

 

[pwill]

Hi mrshl9898

just tried again but it just keeps stopping at sheet3 it selects cell A3

regards

 

[pwill]

Hi could it be that I have the same Ref value for other names and its trying to select the too?

 

[mrshl9898]

I've been able to get the error working on a fix

 

[pwill]

ok thanks

with the drop down I was thinking I could use one column for the name say K1 and L1 for the Ref? but I will try that another day lol

 

[mrshl9898]

Try this, for some reason using the index number makes cells(2,1) = "".....

That gave me the error you see with the print area.

Naming the sheets fixed it on my end.

(I must point out I don't usually use index numbers so am a bit unfamiliar with the ins and outs.)

Sub selectrows()



Dim findcolA As Long
Dim findcolC As Long




findcolA = InputBox("Enter Value for Column A")
findcolC = InputBox("Enter Value for Column C")




rownum = 2
Do Until Sheets("PrdRes").Cells(rownum, 1).Value = ""
If Sheets("PrdRes").Cells(rownum, 1) = findcolA And Sheets("PrdRes").Cells(rownum, 3) = findcolC Then
Startrow = rownum
GoTo FindLastRow
End If
rownum = rownum + 1
Loop








FindLastRow:
Do Until Sheets("PrdRes").Cells(rownum, 1) = ""
If Sheets("PrdRes").Cells(rownum, 1) = findcolA And Sheets("PrdRes").Cells(rownum, 3) = findcolC Then
Lastrow = rownum
End If
rownum = rownum + 1
Loop








Let Copyrange = "A" & Startrow & ":" & "J" & Lastrow
Sheets("PrdRes").Range(Copyrange).Copy
Sheets("Data").Range("A3").PasteSpecial xlPasteValues




End Sub

 

[pwill]

Hi, yes that did the trick

thank you very much you are a star

Kind Regards
pwill

 

[mrshl9898]

Phew lol.

Glad that helped