Problem with Selection find, offset, copy & paste Macro

[MDResearcher]

I have limited to no VBA experience, so I was wondering if someone could help me out fixing the code to do a Selection find, offset, copy & paste type action

The selection I want to find will be MR# (these are all different numbers for example: 48226810) and this value is in cell C3 on sheet "Pt.InfoWksheet".

I want the macro to then search for this selection in the range B4 to B1101 on sheet "Required Patient Info". Once that value is found I want to copy cells B2 to B155 on sheet "Pt. Long Sheet", offset by 1 column and paste the specified range (so the pasted content would be directly next to the found MR #).

I would really appreciate any help.

Below is the code I am working with. It returns the error "Object variable or With Block variable not set" (Run-time Error '91')

Please Login or Register  to view this content.

[Mordred]

Hi MDResearcher and welcome to the forum. Please take a moment to read the forum rules located here, specifically rule #3 and wrap your code with code tags. Once you do that, we will be able to help.

Regards:

[MDResearcher]

Thank you for the etiquette suggestion, the requested correction has been made.

[Mordred]

Not tested at run-time but try:

Please Login or Register  to view this content.

Since I didn't test at run-time, I would suggest trying this on a copy of your original workbook.

EDIT: forgot to set the sheets with

Please Login or Register  to view this content.

but have since changed it.

[MDResearcher]

Thank you for your help Mordred! Unfortunately ,it is returning an error message of Out of Memory (Run-time error '7')

It has this line highlighted:

Please Login or Register  to view this content.

[Mordred]

Sorry MDResearcher, the reason that happen is because I forgot to declar the range on sheet ("Pt. Long Sheet"). Try this code

Please Login or Register  to view this content.

[Mordred]

If that doesn't work, it may be worth your while and my time if you upload a mock workbook with mock data in it.

[MDResearcher]

If that doesn't work, it may be worth your while and my time if you upload a mock workbook with mock data in it.

The code ran fine, except for two things:
I need the paste to be transposed from vertical to horizontal
The code is double pasting

Attached is a sample of my workbook. These are not the real MR#s since that is protected information, but I have produced fake ones for the purposes of this sample. Please understand this is a study on gunshot patients so some of the headings are kind of morbid.

Thank you for you efforts thus far.

[Mordred]

Unfortunately, I cannot open that here at work because I run 2003 here. However, I can do it at home but I won't be there for another couple of hours.

[Mordred]

If you are running 2007 or higher, your description should say as much. As it is, your description says you are using 2003.

[Mordred]

Maybe?

Please Login or Register  to view this content.

The following line may or may not need the +1

Please Login or Register  to view this content.

It is hard to tell right now.

[MDResearcher]

It pasted 1's all the way across the cells.

Here is potentially a '07 friendly version, feel free to wait the few hours if it helps.

[Mordred]

LoL! Mayhap I need to call in the gurus for this!

[romperstomper]

Close - you need to transpose:

Please Login or Register  to view this content.

[MDResearcher]

Oh so close!!! It is not finding the right MR# it just pastes (all the correct data though) on the first row with a MR#. Thank you romperstomper for you efforts as well!

[tigeravatar]

MDResearcher,

Give this a try:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

[romperstomper]

It appears the search cell is wrong:

Please Login or Register  to view this content.

should be:

Please Login or Register  to view this content.

[MDResearcher]

Excellent work all three of you! You have literally saved me hours of work on an already tough project. I want to sincerely thank all of you for helping a VBA newbie out.

Both tigeravatar and romperstompers codes are functional and do the the same thing.

For thread completeness here are the functional codes:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Mordred]

If you are happy with the results, please hit the scales and give those that you feel have helped you a scales tap. Also, don't forget to mark your thread as solved.