LOOKUP & Select Row

[DetroitDavid]

I’d like to select an entire row (or to the limit of a range) as show below in light blue, when a value (which is text) is supplied via an input.

Something like:

GetAgencyNumber = InputBox("Agency Number")
Application.Goto (ActiveWorkbook.Sheets("Header").Range("B1"))
Set cell = Selection.Find(GetAgencyNumber, ActiveCell)
Copy the range A2:M2 ‘’’ (this would change depending on the input value)


Header

*

A

B

C

D

E

F

G

H

I

J

K

L

M

header-type

provider-id

sample-year

survey-mode

sample-type

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 83px;"><col style="width: 103px;"><col style="width: 190px;"><col style="width: 68px;"><col style="width: 87px;"><col style="width: 49px;"><col style="width: 85px;"><col style="width: 88px;"><col style="width: 84px;"><col style="width: 137px;"><col style="width: 95px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="align: left"]client_number[/TD]
[TD="align: left"]provider-name[/TD]

[TD="align: center"]npi[/TD]
[TD="align: left"]sample-month[/TD]

[TD="align: center"]total patients served[/TD]
[TD="align: center"]number-vendor-submitted[/TD]
[TD="align: center"]TEP[/TD]
[TD="align: center"]number-surveyed[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="bgcolor: #99CCFF, align: right"]1[/TD]
[TD="bgcolor: #339966, align: center"]001[/TD]
[TD="bgcolor: #99CCFF"]Client 1[/TD]
[TD="bgcolor: #99CCFF, align: right"]237492[/TD]
[TD="bgcolor: #99CCFF, align: center"]1679515068[/TD]
[TD="bgcolor: #99CCFF, align: left"]4[/TD]
[TD="bgcolor: #99CCFF, align: right"]2013[/TD]
[TD="bgcolor: #99CCFF, align: right"]1[/TD]
[TD="bgcolor: #99CCFF, align: right"]1[/TD]
[TD="bgcolor: #99CCFF, align: center"]8[/TD]
[TD="bgcolor: #99CCFF, align: center"]8[/TD]
[TD="bgcolor: #99CCFF, align: center"]5[/TD]
[TD="bgcolor: #99CCFF, align: center"]4[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[sdsurzh]

Hi,

As per your request i assume you have to search in B Column hence it will select in B Column and find the input number and select the entire row.

GetAgencyNumber = InputBox("Agency Number")
Application.Goto (ActiveWorkbook.Sheets("Header").Range("B1"))
Columns("b:b").Select
Selection.Find(what:=GetAgencyNumber, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.EntireRow.Select

Thanks,
SK

 

[DetroitDavid]

Awesome – works perfectly & I'm learning a lot! Now I need to take it another level. After getting the above (which is to be an XML header from the Header tab, I need to move to the Data tab and grab the body portion to go with that header in a separate worksheet. So, using the same Agency Number from the input, I need to select a range (cols hidden to show entire range). In the case of “001” it would be B2:CE3. In the case of “002” it would be B4:CE7. Initial screen of tab “Date” 2nd screen of tab “XML WS” below it. Note, the 001 in col A of data does not get copied, and is only needed for sorting. The client value comes from =MID() the 3rd octet of the SID number. The "XML WS" gets saved to an XML document for each client. I'd eventually like to rip through 140+ non-sequential client numbers. Thank you so much!!!

Thoughts?

Excel Workbook
	A	B	C	D	E	F	G	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
1	Do NOT copy col A - Select Bx:Cey					SID
2	001	237492	1679515068	4	2013	D-13-001-75432	10	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	350	M	1
3	001	237492	1679515068	4	2013	D-13-001-75433	14	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	350	M	1
4	002	237603	1275630543	4	2013	D-13-002-78749	11	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	350	M	1
5	002	237603	1275630543	4	2013	D-13-002-78750	14	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	350	M	1
6	002	237603	1275630543	4	2013	D-13-002-78752	14	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	350	M	1
7	002	237603	1275630543	4	2013	D-13-002-78753	11	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	M	350	M	1
Data

Excel Workbook
	A	B	C	D	E	F
1	header-type	number	provider-name	provider-id	npi	sample-month
2	1	001	Client 1	237492	1679515068	4
3	providerid	npi	sample-month	sample-yr	sample-id	patient-age
4	237492	1679515068	4	2013	D-13-001-75432	10
5	237492	1679515068	4	2013	D-13-001-75433	14
XML WS