Macro to search for value contained in a cell

Hi, ScabbyDog,

leaves open to find out: copy where? Have a look at Find in VBA-Help to build a range-object from the row found and use the column from the bottom up to find the last row.

Ciao,
Holger

Hi, ScabbyDog,

maybe we can start with this code:

Please Login or Register  to view this content.

This will copy, if you need paste of formulas please come back.

Ciao,
Holger

Do you really need a macro for this?

Enter the string to find in A2

In say, C2, Drag/Fill Down

Formula:

Please Login or Register  to view this content.

See this workbook

[EDIT]
Added Sheet3 to example

Add (e.g) this to your code

Please Login or Register  to view this content.

Hi, ScabbyDog,

maybe like this:

Please Login or Register  to view this content.

Ciao,
Holger

change

Please Login or Register  to view this content.

in

Please Login or Register  to view this content.

and give it a try on a copy of your workbook.

Hi, oeldere,

with your sniplet the first free line would be searched on the ActiveSheet and not on Sheet3.

Ciao,
Holger

@HaHoBe,

Hi, Holger,

I'm no VBA expert and are willing to learn.

1) I don't know what the word sniplet means (my main language is Dutch instead of English).

Please Login or Register  to view this content.

I don't understand.

Please explain it to me, already said, i'm willing to learn.

Hi, oeldere,

at least we can agree that English is not our native language.

In the Code I used a With Construct for the ease and easiness of understanding. So anything inside that code which is preceded by a dot is relying on the actual With-Construct (not the proper expression for that - I know, call it lack of proper knowledge).

I referenced With ActiveSheet so the line you suggested could be written like

Please Login or Register  to view this content.

If a dot goes ahead the part of

Please Login or Register  to view this content.

will rely on that, if no dot is there it will automatically relate to the active sheet in Excel.

Your line of code will work when you amend it to read

Please Login or Register  to view this content.

Using a With-Construct will reduce the time for VBA to get through the Application-model to find the object to use - itīs of no great use here with the sniplets but has caused some headaches for me in other codes which go beyond the scope of Forums.

I hope I could explain somehow a little better here what I meant with that line. If not feel free to ask here or PM me about it.

Ciao,
Holger

my code

Please Login or Register  to view this content.

Holgers code

Please Login or Register  to view this content.

I'm glad you pointed me on this "problem".

I'm trying to use it next time.

Thanks Holger,

(Danke Holger)

Hi, oeldere,

by looking at the code again I must apologize because your code should work flawlessly - thatīs an effect if you do more than one thing at the same time and mix different things up (and think you saw something in the code you believed to be there - sad but true).

Sorry for not paying as much attention as I should have had - I doubt that the Rows.Count will be any different on any of the sheets (should be 2^20 for Excel from 2007 on).

Again, my mistake - I think Iīll take a break for today.

Ciao,
Holger

Hi Holger,

No apologize needed.

Normaly I test my code in an file, so i can see if it's working.

But the Question Starter didn't add an file, so I couldn't test it.

With my "weak" knowledge of VBA I believe from the start what you're telling me is right.

I'm glad you pointed me on my code and correct it yourself.

Thanks for the reply Holger.

Have a nice weekend..