Problem with Selection find, offset, copy & paste Macro

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.

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:

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.

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.

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.

Originally Posted by 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.

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.

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.

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

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.

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.

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

Close - you need to transpose:

Please Login or Register  to view this content.

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!

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.

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.