Worksheet_Change /ActiveCell.CurrentRegion ?
 

I'm developing a workbook which needs to return
information of variable size from a source sheet to a
response sheet after the user inputs a parameter.

The data on SheetA is laid out with a partnumber and then
supporting information about that partnumber in following
rows. There are always 2 blank rows between partnumber
entries.

On a test macro, I was able to select all of the
information about a partnumber by using
ActiveCell.CurrentRegion.Select That result works fine
for me.

In general terms my approach is to create a
worksheet_change event macro tied to SheetB which will
clear an area, then go and find the appropriate info on
SheetA, copy it, and then paste it on SheetB in the
cleared area.

What I don't know is how to programmatically find the
right info on SheetA. I know how to select it once I find
it. But how do I find it in the first place?

Or to put it another way, how do you program the
equivalent of vlookups and match functions?

Thanks for your help
Drabbacs

P.S. I'm also willing to consider other approaches. This
was just my first design.

Worksheet_Change /ActiveCell.CurrentRegion ?
 

Basically, you have to do these steps:
Set the range of the data in Sheet1
Search for the part number in that range
Set a variable to that cell range.

For the code, I'll assume that you have the part number text and that you
call the following macro and pass that part number to that macro in the
call. I'll also assume that the part number is in Column A of the first
sheet.

Sub FindPartNumber(PartNumber As Range)
Dim FoundCell as Range
Dim TheRange as Range
Set TheRange=Nothing
Set TheRange=Range("A1", Range("A" & Rows.Count).End(xlUp))
On Error Resume Next 'In case the part number is not there
Set FoundCell=TheRange.Find(What:=PartNumber, LookAt:=xlWhole)
If TheRange Is Nothing Then _
MsgBox "The Part Number could not be found."
On Error GoTo 0
End Sub

Untested. HTH Otto

FindIt = TheRange.Find(What:=PartNumber, LookAt:=xlWhole)

"Drabbacs" wrote in message
...
I'm developing a workbook which needs to return
information of variable size from a source sheet to a
response sheet after the user inputs a parameter.

The data on SheetA is laid out with a partnumber and then
supporting information about that partnumber in following
rows. There are always 2 blank rows between partnumber
entries.

On a test macro, I was able to select all of the
information about a partnumber by using
ActiveCell.CurrentRegion.Select That result works fine
for me.

In general terms my approach is to create a
worksheet_change event macro tied to SheetB which will
clear an area, then go and find the appropriate info on
SheetA, copy it, and then paste it on SheetB in the
cleared area.

What I don't know is how to programmatically find the
right info on SheetA. I know how to select it once I find
it. But how do I find it in the first place?

Or to put it another way, how do you program the
equivalent of vlookups and match functions?

Thanks for your help
Drabbacs

P.S. I'm also willing to consider other approaches. This
was just my first design.

Worksheet_Change /ActiveCell.CurrentRegion ?
 

I forgot to mention that Sheet Two, with the list, is the active sheet and
that the list is in Column 1 starting in A1. Also I found an error in the
code. Here it is with the correction. HTH Otto
Sub FindPartNumber(PartNumber As Range)
Dim FoundCell as Range
Dim TheRange as Range
Set FoundCell=Nothing
Set TheRange=Range("A1", Range("A" & Rows.Count).End(xlUp))
On Error Resume Next 'In case the part number is not there
Set FoundCell=TheRange.Find(What:=PartNumber, LookAt:=xlWhole)
If TheRange Is Nothing Then _
MsgBox "The Part Number could not be found."
On Error GoTo 0
End Sub


"Otto Moehrbach" wrote in message
...
Basically, you have to do these steps:
Set the range of the data in Sheet1
Search for the part number in that range
Set a variable to that cell range.

For the code, I'll assume that you have the part number text and that you
call the following macro and pass that part number to that macro in the
call. I'll also assume that the part number is in Column A of the first
sheet.

Sub FindPartNumber(PartNumber As Range)
Dim FoundCell as Range
Dim TheRange as Range
Set TheRange=Nothing
Set TheRange=Range("A1", Range("A" & Rows.Count).End(xlUp))
On Error Resume Next 'In case the part number is not there
Set FoundCell=TheRange.Find(What:=PartNumber, LookAt:=xlWhole)
If TheRange Is Nothing Then _
MsgBox "The Part Number could not be found."
On Error GoTo 0
End Sub

Untested. HTH Otto

FindIt = TheRange.Find(What:=PartNumber, LookAt:=xlWhole)

"Drabbacs" wrote in message
...
I'm developing a workbook which needs to return
information of variable size from a source sheet to a
response sheet after the user inputs a parameter.

The data on SheetA is laid out with a partnumber and then
supporting information about that partnumber in following
rows. There are always 2 blank rows between partnumber
entries.

On a test macro, I was able to select all of the
information about a partnumber by using
ActiveCell.CurrentRegion.Select That result works fine
for me.

In general terms my approach is to create a
worksheet_change event macro tied to SheetB which will
clear an area, then go and find the appropriate info on
SheetA, copy it, and then paste it on SheetB in the
cleared area.

What I don't know is how to programmatically find the
right info on SheetA. I know how to select it once I find
it. But how do I find it in the first place?

Or to put it another way, how do you program the
equivalent of vlookups and match functions?

Thanks for your help
Drabbacs

P.S. I'm also willing to consider other approaches. This
was just my first design.