Search Column for 'X' and copy 1 cell to the left.

[Scoobster_doo]

Hi All,

I'm getting there with my project, I just need this little bit of guidance form those much better than I

In the Code Bellow I can check if the cell is numeric however what I need to do next is ' If numeric then search a column for that number as copy what's to the left (Either copy/array).

I need to search Sheet2 Column B for the 'Ncell' value and copy the word to the left i.e. If Ncell is B12 then copy A12.

Please Login or Register  to view this content.

My assumption would be after the

Please Login or Register  to view this content.

bit I would add something like search sheet 2 ("B:B") - If found then copy with an offset of -1

Thanks in advance

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

Not sure if this does what you want. It goes down Column B and finds the numbers then copies the Column A value. However, it does nothing with it since you don't mention what you want (destination) after the cell in Column A is copied.

[Scoobster_doo]

Hey John,

Sorry I should have mentioned....I wanted to copy and overwrite the original number in cell A2 (With the word it's copied and picked up form sht2)

So Far with your code I've got:

Please Login or Register  to view this content.

I tried adding a sheet 1 activation and then - Range("A2").Value = Ncell however that doesn't work. I'm assuming because the Ncell value would be the original number it searched for?

Thanks

[JOHN H. DAVIS]

Can you attach a sample sheet showing how the data would look before the macro (Sheet1) and then after the macro (Sheet2).

[Scoobster_doo]

Hey John,

The website doesn't let me attach an excel doc form my computer (It never has done) so I always have to resort to screenshots sadly. I shall try and describe it as best I can below to perhaps help?


Sheet 1:

I'm importing arrays to this sheet.

On import, Cell A2 will either contain a number or a word. If its a word the fine, if its a number then I Have a list on sheet 2 which has numbers and their corresponding words.

Sheet 2.

Column A Column B

Dog 1
Cat 2
Owl 3
Pidgeon 4
Rat 5
Hawk 6
Zebra 7
Mouse 8
Lion 9
Cow 10



So if the imported array puts the word Hawk in sheet 1 - Cell A2 then That's fine, nothing is changed.
If the number 6 is imported and placed into to Sheet 1 Cell A2 then I want it to look for the number 6 on sheet 2 - Copy the word beside it i.e. hawk then go to sheet 1 and place Hawk in cell A2.

Hopefully that makes it clearer?

[Scoobster_doo]

Hey John,

The website doesn't let me attach an excel doc form my computer (It never has done) so I always have to resort to screenshots sadly. I shall try and describe it as best I can below to perhaps help?


Sheet 1:

I'm importing arrays to this sheet.

On import, Cell A2 will either contain a number or a word. If its a word the fine, if its a number then I Have a list on sheet 2 which has numbers and their corresponding words.

Sheet 2.

Column A Column B

Dog 1
Cat 2
Owl 3
Pidgeon 4
Rat 5
Hawk 6
Zebra 7
Mouse 8
Lion 9
Cow 10



So if the imported array puts the word Hawk in sheet 1 - Cell A2 then That's fine, nothing is changed.
If the number 6 is imported and placed into to Sheet 1 Cell A2 then I want it to look for the number 6 on sheet 2 - Copy the word beside it i.e. hawk then go to sheet 1 and place Hawk in cell A2.

Hopefully that makes it clearer?

[JOHN H. DAVIS]

Perhaps?

Please Login or Register  to view this content.

[Scoobster_doo]

You Sir, are a genius.

This is spot on
Thank you!

Its amazing how different it is form what I original started with!

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback.

[Scoobster_doo]

Hey John, Sorry to bother you again.

If Column A and Column B changed to Column N and Column P...which bit would a change to accommodate the offset of picking up the word from column N?

I've messed around and it collects the correct row but not correct column!

Thanks

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

I'm guessing this will work for you. Adjust as required.

[Scoobster_doo]

Hey John,

My Mistake - I mean the two letters on the second sheet - But from what you have highlighted, I have been able to figure it out

Please Login or Register  to view this content.

Again Thank you - It all works! Woop

[JOHN H. DAVIS]

Again welcome. Glad you figured it out.