How to enter Value "skip" in same row at column AA when TargetCell was found

[tuongtu3]

Hello All,
I'm trying to enter value to a certain Column to each TargetCell but don't know how:
The TargetCell could be any Cell at difference Row/Column., whenever TargetCell was found then I want to enter value "Skip" at same row but column AA.

Please Login or Register  to view this content.

Regards,
tt3

[Solus Rankin]

How do you declare the variable?

Can you post the rest of the code?

[wenqq3]

No sure is this what you mean.

Please Login or Register  to view this content.

[tuongtu3]

@wenqq3: Thank you for your time but it's not what i'm looking for or I may not understand your code.
@Solus: Please see sample in below:

Please Login or Register  to view this content.

*Red text in code is for REF. only.

Let says "Passed" is in Cell K8 then I want enter "Skip" in cell AA8
"Passed" is in Cell M10 then I want enter "Skip" in cell AA10
Regards,
tt3

[wenqq3]

Maybe this.
My understanding of your code and your comment is, whenever there is a "Passed" in any cell of K100:P100, then AA100 is "Skip"

Please Login or Register  to view this content.

[OllieB]

Please try

Please Login or Register  to view this content.

[tuongtu3]

Both codes works and now I'm facing another problem:

I have a varies of Passed-1 through Passed-9 and how to solve this.

This line does not work

Please Login or Register  to view this content.

Regards,
tt3

[OllieB]

This should work

Please Login or Register  to view this content.

[Solus Rankin]

You can also try:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[tuongtu3]

@OllieB: It does not work & can you check it or I did it wrong?

Please Login or Register  to view this content.

@Solus: Both lines work and can you explain why below line work? (Try to learn)

Please Login or Register  to view this content.

Regards,
tt3

[OllieB]

The statement should work unless the value in the cell does not equal "Passed-"; but for example " Passed-" or "Passed -". The left statement takes the first seven positions from the cell value so if the cell contains anything else it will not result in True

[Solus Rankin]

InStr is a built in function that checks if 1 string is present in the string of the next ( In + String = InStr). This function returns a number value representing the digit the beginning of the string is found. InStr("IsItHere", "It") would return 3.

The reason I suggested this method, I find its sometimes troublesome to include special characters inside a string you're searching with. Such as the - in "Passed-". Its probably not a real issue, just a personal opinion.

[tuongtu3]

@OlliieB: It work when I change to:

Please Login or Register  to view this content.

@Solus: Thank for your explanation but not sure why > 0

Same issue but different situation and how to modify "First" => "First-*" (Code provided by member in this forum)

Please Login or Register  to view this content.

Regards,
tt3

[Solus Rankin]

Because if the 2nd string is NOT found in the first string then InStr will return a 0. Such as InStr("IsItHere", "Not"). Not isn't included in the string so this would return 0. You want to affect the rows where it IS found. Therefore you only want to affect rows where it is > (greater than) 0.


e/ In the effort of being complete with the explanation I suppose I should add: You don't NEED the > 0. You can leave it simply

Please Login or Register  to view this content.

I like to include the > 0 to be explicit. To me personally, it makes the code easier to read and easier to edit later.

[tuongtu3]

and how to modify "First" => "First-*" because code does not recognized "First-1" (Code provided by member in this forum)

Please Login or Register  to view this content.

Regards,
tt3

[Solus Rankin]

You'd have to put that code in context. Its a constant declaration but I don't know what you're doing with it now or what you'd like to do with it.

[tuongtu3]

The code was provided by member in this forum but some how does not work because code does not recognized the text "First" since the true text is "First-1". My question is there anyway how to modify that line to detect "First-1"

Regards,
tt3

[Solus Rankin]

I don't know how its being used so this is best guess

Please Login or Register  to view this content.

[tuongtu3]

Hi Solus,
Your guess is corect but that does not work with

"First-2"

or

"First-3"

and I'm looking for code how to work with all "First-n"
Regards,
tt3

[Solus Rankin]

I can't figure that out unless you post the whole code. all the line you posted does is declare a constant. It doesn't take any action. Without posting the whole code I don't know what s supposed to happen to the constant.

[tuongtu3]

Here you go:

Please Login or Register  to view this content.

Regards,
tt3

[Solus Rankin]

Okay, now I'm getting the picture. Can you describe the idea of what needs to be achieved with this code?

[tuongtu3]

The code work when text "First" was found but I would like to modify the code to work with any text from "First-1" thru "First-9".

Regards,
tt3

[Solus Rankin]

I won't know how to edit it without fully understanding what you're trying to achieve. You would like to look through every cell in the used range and check for the string "First-"? What would you like to happen if its found?