VBA- to scan for a particular code off a data sheet print result on Front Sheet

Hi all

ON THE Front Sheet

In cell "N6" I need a formulae/VBA where I have a cell D6 will have a number in it,lets say( 5 )
( between 1 & 100 this is not critical ) what I need is a If/Vlookup/or/ or VBA statement
to check if cell a cell in the "Open Sheet ", " has either a " V or W " in it that is associated
to the rider in D6, the database is in the "OPEN sheet" the block of cells are named
"Open_1 " (A8-N107) and the if particular cell has a V or W then print what is in that
cell back into " Front Sheet" N6 be it : V or W or blank cell.

I have highlighted the row in YELLOW

Att Ali

Am I allowed to edit the description for this thread I think I maybe make it sound too complicated,
I am not getting impatience I know someone may find the thread, just make it clearer.

Pete

Yes, but don't make it generic in the process. Post what you are thinking of first here and let me advise. I think the word 'print' might be misleading.

Scan the " Open sheet " from (A8:N107) AKA "Open1" the number in cell D6 " Front Sheet " to see
if between columns E:N if there exists a " V" or "W" if yes place the V or W answer in cell N6 " Front Sheet ".

That's far too long and complex for a thread title (wouldn't fit, anyway).

To be honest, if I have understood correctly (and the use of terms such as 'scan', 'print', etc. make it harder to decipher than it should be), this could be done with a formula.

I asked you in an earlier thread whether the purpose of this was to WRITE data to the workbook or to READ data from the workbook and show it somewhere. I now think it's the latter - am I correct?

You are still overcomplicating your descriptions - they are a bit like a bowl of spaghetti.

Hi Ali

This is NOT the name of the thread, it is what a formulae to do, Sorry I confused you,

The purpose to scan the database and report findings on the Front sheet.

Scan the " Open sheet " from (A8:N107) AKA "Open1" the number in cell D6 " Front Sheet " to see
if between columns E:N if there exists a " V" or "W" if yes place the V or W answer in cell N6 " Front Sheet ".


OHHH

Did you send me a Notification if so I lost it.

I sent you a response to your PM yesterday - shall I send it again?

So the Open sheet will already be populated with data - yes?

If so, an INDEX MATCH combination should work.

Please Login or Register  to view this content.

Originally Posted by AliGW

I sent you a response to your PM yesterday - shall I send it again?

So the Open sheet will already be populated with data - yes?

If so, an INDEX MATCH combination should work.

NO to the PM, I was only trying to make you aware of my limitations.

Thank You " Boss Lady "

Hi Rudi ( I think)

When I enter any number in D6 I am getting a ambiguous name detected
my worksheet is up on post 1 if that will help.


Private Sub Worksheet_Change(ByVal Target As Range)

Can be done with a formula:

=IF(SUMPRODUCT((Open!$E$8:$N$107="V")*(Open!$B$8:$B$107="Rider "&D6))>0,"V",IF(SUMPRODUCT((Open!$E$8:$N$107="W")*(Open!$B$8:$B$107="Rider "&D6))>0,"W",""))

I was only trying to make you aware of my limitations.

I hope you understood my reply.

Does that mean this is solved?

Please my posting on page #10

OK - so you don't want a formula? Did I waste my time in post #11?

I am looking at your coding trying to workout why the reference to column B, all of our references are to the Rider number eg :- 3, 5, 99, etc
It found it for number 5 but that is all.

1. It's a formula (NOT code - two different things, and when you are working with both, you need to get the terminology right to avoid confusion).

2. I used the rider number in column B because I was not sure that it would be the same as column A. To use column A, change it to this:
=IF(SUMPRODUCT((Open!$E$8:$N$107="V")*(Open!$A$8:$A$107="D6))>0,"V",IF(SUMPRODUCT((Open!$E$8:$N$107="W")*(Open!$A$8:$A$107=D6))>0,"W",""))

3. Why would it return anything for any other rider? Only rider 5 has either V or W in their row on the Open tab. The tab is otherwise completely devoid of data.

Thank you for your reply.

With endurance horse rides a around the bush here there are times that either a rider or their mount will
call in at any of the checkpoints that they are either needing a VET or are With drawing from the ride for
any reason so the person entering the data will on the front page enter the rider number/ checkpoint/
time (at the CP)/ then either " V or W" that will be entered against E8-N107,

Now the purpose for the entry at " Front Sheet" N6 is that if they enter a riders number and it has already
been vetted out or withdrawn as a warning. Once they have either V or W that is it for the day, they CANNOT
go out again that day, must have a 12 hour rest and another medical check.

A bit long winded that is how they are run.

OK - now that's clearer. You will need VBA for this because you need to be able to read AND write if necessary to the workbook.

It takes such a long time to tease basic bits of information out of you, Pete. In answer to my question in post #5, you said this:

The purpose to scan the database and report findings on the Front sheet.

Scan the " Open sheet " from (A8:N107) AKA "Open1" the number in cell D6 " Front Sheet " to see
if between columns E:N if there exists a " V" or "W" if yes place the V or W answer in cell N6 " Front Sheet ".

So, in summary, when I asked if anything needed writing to the workbook you said 'no', it needs to look up the data and return what it finds.

Now, in post #18, the goalposts have shifted again: apparently we DO need to write back to the workbook if the value does not already exist.

Forgive me, but I don't have the energy or the patience for this today. I'm really sorry, but I am going to have to leave it at that. I really hope that somebody can help you to untangle the spaghettit to get one coherent and logical explanation of what you want to do.

Delete the old Change-event and replace it with mine. The code was tested in your example file in Post#1 so I know it works.

Hi Bakerman2 Rudi ( I think)

When I enter any number in D6 I am getting a ambiguous name detected

Please Login or Register  to view this content.

>>>>>>

I will attach my working file, please look at the coding page

There appears to conflict with the code for UPPERCASE code seems to be affecting your code.
Your code works fine as is sort of.

On the open sheets I have 2 riders 1 with V and 1 with W,

When I select a rider and cell N6 returns the V or W it stays there even when the next NON sick rider number is entered.


Peter

When I opened your file I found 2 WorksheetChange-event codes.

PLEASE READ MY POST#20 AGAIN AND COMPLY. ESPECIALLY THE REPLACE PART.

Following code clears the Rider field before checking .

Please Login or Register  to view this content.

Hi Rudi

I did use you code, I was getting a conflict with other code with the same

Please Login or Register  to view this content.

Sintek is looking at his code with the same heading for something on another thread

Pete

Maybe third time is the good one.

REMOVE the Uppercase Change-event and REPLACE it with the code I provided in Post#22.

I've done it for you in the example file so you can see for yourself that it works WITHOUT any conflicts.

Hi

Your code works a treat, it is the code below that it is clashing with
many I got to nter a rider number I get a error message

Please Login or Register  to view this content.

Many Thanks

Pete

That code is beyond the scope of this thread.

Please mark this thread as Solved and continue in your other thread regarding the above code.

Rudi is right - this thread has now been marked as solved (by me) and any further discussions should take place here: https://www.excelforum.com/excel-pro...s-a-value.html

This will avoid duplication of effort and confusion.

Ali

Should I start a new thread re the clash of the 2 sets of code. ??

NOOOOOOOOOO!!!!!!!!!

It's being dealt with in the other thread to which I linked above. Just stick to that thread until this clashing issue is resolved, then you can perhaps start a new thread for the next issue.

Deal with one thing at a time, please, otherwise it gets confusing for everyone.