Searching exact 4 values in any cells in same row then change 4th value to 5th value

[tuongtu3]

Hello All,
I'm doing manually to update all reports. Is it possible to searching the exact 4 values in same row to the last row within a worksheet, if 4 exact values was found then change the fourth value to 5th values (Please note that the values could be at any cells in the same row)

First Second Third Fourth

Let says: if 4 values was found then change "Fourth" to "Fifth"


Regards,
tt3

[:) Sixthsense :)]

Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

[tuongtu3]

Hi Sixthsense:
Thank you for looking. Please take a look at the sample iin attached.

Regards,
tt3

[nilem]

maybe so

Please Login or Register  to view this content.

[tuongtu3]

Hi Nilem,
How to modify the code to do/searching the same in all Worksheets in Workbook?

Regards,
tt3

[tuongtu3]

I tried to modify code with below lines to search all ws but doesn't work. Does anyone know how to make it work?

Please Login or Register  to view this content.

Regards,
tt3

[Trebor76]

Hi tt3,

try this:

Please Login or Register  to view this content.

HTH

Robert

[tuongtu3]

Hi Robert,
I got an error message : Method "Union" of Object '_Global' Failed at below line

Please Login or Register  to view this content.

Regards,
tt3

[Trebor76]

Try putting this line...

Please Login or Register  to view this content.

...immediately underneath this line:

Please Login or Register  to view this content.

Regards,

Robert

[tuongtu3]

Hi Robert,
It changed value on the first ws then same error message pop up again.

Updated:
It works when I change from

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

and now I got error "Object variable or With block variable not set" when I try to get the filename:

Please Login or Register  to view this content.

Please help me to fix it.

Regards,
tt3






Regards,
tt3

[Trebor76]

Setting range variables to nothing clears them so you then cannot use them to return anything. You need to to clear them after you've completely finished with them.

If the problem persists, try this way where the original code is not touched in any way but run on each sheet via the RunOnAllSheets macro:

Please Login or Register  to view this content.

HTH

Robert

[tuongtu3]

Hi Robert,
I have no problem with run on all ws but now I'm facing another error when I try to copy the new value and paste to other ws.

and now I got error "Object variable or With block variable not set" when I try to get the filename:

[Select Code] copy to clipboard

If Not rChange Is Nothing Then rChange.Value = "Fifth"
'''Set rng = Nothing: Set rChange = Nothing put here still same error happen
rChange.Offset(0, 8).Value = strFileFullName
rChange.EntireRow.Copy Destination:=Sheets("Sheet9").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Set rng = Nothing: Set rChange = Nothing
Next wstSheet

Please help me to fix it.

Regards,
tt3

Updated: I got it to work by add this line:

Please Login or Register  to view this content.

[Trebor76]

On Error Resume Next doesn't fix anything - it just stops the error message from appearing. Whatever the underlying problem was, it still remains.

[tuongtu3]

Hi Robert,
Thank you very much for your clarification.

I just found out that one of the value was a result of formula of =Vlookup(......), and the code does not recognize the text from the value of formula. Is there anyway to fix this issue?

Regards,
tt3

[Trebor76]

the code does not recognize the text from the value of formula

What is the value? If it's an error like #N/A you could wrap that part of the code with the ISERROR function to ignore it like so:

Please Login or Register  to view this content.

Robert

[tuongtu3]

Hi Robert,
No, it's not in that way. Let says one of the value "Fourth" is result of "=VLOOKUP(H6,DataReport!A:E,5,FALSE)". You can see text "Fourth" there but code does not recognize the text from the formula. I also attached a sample for more clear.

Regards,
tt3

[Trebor76]

Ah - you just need to add the LookIn optional argument, i.e. change this line of code...

Please Login or Register  to view this content.

...to this:

Please Login or Register  to view this content.

Bear in mind that the formula will be replaced by just text.

Regards,

Robert

[nilem]

Hi tt
Re your PM, try it

Please Login or Register  to view this content.

[tuongtu3]

Hi Nilem,
The code works but there's another scenario:
1. How to change 2x of Fourth in same row to Fifth (current code does change 1x Fourth only:

First-1 Second Third Fourth Fourth

Regards,
tt3

[nilem]

try

Please Login or Register  to view this content.

[tuongtu3]

Hi Nilem,
The code did change 2x of Fourth with "First" , but did not change any Fourth with "First-n"

Regards,
tt3

[nilem]

What does "First-n"?
Set an example in a file with all possible input data and the desired result

[tuongtu3]

Hi Nilem,
"First-n" is varies of "First-1", "First-2", "First-3" thru "First-9"

Regatds,
tt3

[tuongtu3]

Hi Nilem,
Please see sample in attached.

Regards,
tt3

[nilem]

Hi tt,
try

Please Login or Register  to view this content.

[tuongtu3]

Hi Nilem,
The code is really perfect as I expected. Thank you very much for your time and help.

Regards,
tt3

[tuongtu3]

Hi Nilem,
Sorry I got the error:

Please Login or Register  to view this content.

[nilem]

Can you attach file in which the error occurred?

[tuongtu3]

Hi Nilem,
I Found out the code does not work bacause of #N/A returned by VLookup Formula. Can you modify code to skip #N/A or how to kill these #N/A. (I tried Find & Replace to kill #N/A, but not sure why does not work ).

Regards,
tt3

[nilem]

Hi tt,
try so

Please Login or Register  to view this content.

[tuongtu3]

Hi Nilem,
I can got filename with old code but now I don't know how to modify to get the file name per your new code. Please help

Please Login or Register  to view this content.

Regards,
tt3

[tuongtu3]

Hi Nilem,
I'm trying to modify your code to enter filename at each cell which replaced "Fourth" by "Fifth" at cell AB and copy entire row & paste to Sheet9 but it didn't work. Please help:
(Text in RED is what I'm trying to add to modify in your code)

Please Login or Register  to view this content.

Regards,
tt3

[nilem]

try

Please Login or Register  to view this content.