Hi Expert,
I am trying to fix the code, the sheet has three columns, the first one is serial and the second one is equivalent text and the third one is the value, the value of column C is wrong, we have to find it.
Column B has a unique range reference that will check the value in column C. The value of column C is accidentally pulled but still a value is correct.
What are the values in column C that make it different, we have to persist and fill it to the limit unique range to column B.
I hope you understand.
Thank you
Last edited by jessica0007; 11-24-2019 at 09:07 PM.
According to your attachment a starter demonstration :
PHP Code:
Sub Demo1() Dim Rg As Range, V Application.ScreenUpdating = False With [A1].CurrentRegion.Columns(2) Set Rg = .Cells(1, 2) While Not IsEmpty(Rg(Rg.Count)(2, 0)) Set Rg = Range(Rg(Rg.Count)(2), .Find(Rg(Rg.Count)(2, 0), , xlValues, xlWhole, , xlPrevious)(1, 2)) V = Filter(Application.Transpose(Rg), Rg(0), False) If LBound(V) = 0 Then Rg.Value2 = V(0) Wend End With Application.ScreenUpdating = True Set Rg = Nothing End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 11-24-2019 at 03:09 PM.
Reason: optimization …
We would very much like to help you with your query, however the thread title does not really convey what your request is about.
Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).
Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
@Marc L, your code is working perfectly as per requested.
I have changed the column space. The unique range of column B has been set in column E, and the value of column C has been set in column M.
Will you change the vba code again, because I do not know much about vba code.
Thank you
Last edited by jessica0007; 11-24-2019 at 08:43 PM.
@alansidman, From next time I will make sure that my title is correct. I sent it in a hurry, I should have taken care that after reading the title the experts will be able to help me properly.
According to your last attachment my demonstration revamped :
PHP Code:
Sub Demo1r() Dim Rg As Range, V Application.ScreenUpdating = False With ActiveSheet.UsedRange.Columns(5) Set Rg = .Cells(1, 9) While Not IsEmpty(Rg(Rg.Count)(2, -7)) Set Rg = Range(Rg(Rg.Count)(2), .Find(Rg(Rg.Count)(2, -7), , xlValues, xlWhole, , xlPrevious)(1, 9)) V = Filter(Application.Transpose(Rg), Rg(0), False) If LBound(V) = 0 Then Rg.Value2 = V(0) Wend End With Application.ScreenUpdating = True Set Rg = Nothing End Sub
I have changed the column space. The unique range of column B has been set in column E, and the value of column C has been set in column M.
… the question is why in the last attachment the column M is empty ?‼
With column M filled if the result is not the expected one
that means the data does not respect the original logic, so you have to correct cells manually
or to create a new thread with this time the best logic explanation and an attachment accordingly …
Hi Mark L,
Sorry, one column is deleted, and the other thing is that if it does not find any unique value in the column M, it will skip and check the next row.
Last edited by jessica0007; 11-29-2019 at 11:26 AM.
Just add a column in order data are within column M …
As both codes do not work with 'unique value' or according to your both first attachments they couldn't work as expected !
So from the initial post & post #6 this thread is solved …
For new rules, I think it's better you create a new thread with this time a smart explanation of the expected logic to follow
- missing in the initial post of this thread - and a before / after attachment accordingly
in a well defined context, never changing each time you add a new post …
Yes I agree that this Thread has been resolved as per my request, but I do not think that another Thread should be made, for a small problem so far you have helped me a lot of time. I request you one more time. please do it.
I can try to explain logic,
From the unique range in column E, we were looking for the correct value of column M, but sometimes the value of column M is also correct, as if we could not find a different value from column m then it means that Correct, keep it that way. Do not make any changes in it and try to find it from the next unique range.
I think I have given the wrong excel file, I have attached another file, you will understand by looking at it.
Otherwise I will request a new Thread this time.
Sorry for trouble...
Last edited by jessica0007; 11-30-2019 at 09:07 AM.
Bookmarks