Search Range, find criteria, offset, copy and paste to blank cell in new specified range

Hi all,

After some help. I'm wanting to search a column for a specific word (in my case "hol"). When I've found that word I would like to offset my column by one, cut the contents from that cell and then paste it as a value to the first available blank cell in a new range. I need this to do for every instance of my criteria.

Now I believe i'm fine in finding the word, offsetting and copying all in a loop. its the looking for a free cell where i'm stuck.

My range is E4:70 and I want to paste to range o42:o51

Originally Posted by WasWodge

Can we see your macro so we know where you are please?

As a basic method to paste in the next row in your range then

Please Login or Register  to view this content.

but if there is any chance you could have more than would fit down to O51 then you need to put something in the code to handle it.

i'm roughly here at the moment.

Sub Format()
'
' Format Macro
'

'

Set Rng = Range("e4:e70")
For Each cl In Rng
If cl.Value = "hol" Or cl.Value = "sick" Then cl.Offset(, -1).Select
Selection.Copy


Next cl

End Sub

Originally Posted by WasWodge

Personally I would use Find for the initial search but as you are dealing with a small range then it won't be an issue so

Please Login or Register  to view this content.

But the below could still be an issue...

have tried this however I keep getting a run-time error 91: object variable or with block variable not set message. Any ideas?

BTW your help is much appreciated.

Originally Posted by WasWodge

Not really if your workbook is the same as what you uploaded as I don't get the error (see the workbook uploaded below, click the button).
All I can suggest is a) if you have any protection on the worksheet turn it off as that will produce an error b) upload the workbook that you are getting the error on with the code as you are running it so I can see it.

Unfortunately i'm not able to give you the spreadsheet i'm using due to confidentiality and all that...

however on the debugger it highlights cl.Offset(, -1).Copy Range("O42:O51").Find("*", , xlValues, , xlByRows, xlPrevious)(2)

could it be anything to do with the names actually being formulas on the original sheet?

all sorted. In my new sheet I had no name at the start of where it was going to paste so it couldn't find a value to start with. I've just adjusted the range. Thankyou ever so much for all your help.