This is just a sample macro i found somewhere and just edited (I know this is wrong).
What I want is:
Please Login or Register to view this content.
1. If I put CANCELLED on column A, the values on column N,O,P,Q,R becomes "," + their current value.
2. Skip or ignore blank cells in N,O,P,Q,R.
I will be using from A5 to A1500, so with NOPQR.
Thank You so much.
Last edited by domz3669; 04-11-2018 at 08:34 PM.
Please check forum rule 3. Use code tags around code.
You will likely want to change the thread title as well ... to something more descriptive ... before a Mod/Admin stops others from posting.
Dave
I'm sure there are better/faster ways but this works with little modification.
Please Login or Register to view this content.
Try this ‼
A formula way :
PHP Code:Sub Demo1()
Const F = "TRANSPOSE(IF(#=""CANCELLED"",ROW(#)))"
For Each V In Filter(Evaluate(Replace(F, "#", ActiveSheet.UsedRange.Columns(1).Address)), False, False)
With Cells(V, 14).Resize(, 5)
.Value = Evaluate(Replace("IF(#>"""","",""&#,"""")", "#", .Address))
End With
Next
End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 04-11-2018 at 09:50 PM.
-bradl822
Thanks for the code. I tested it, works fine if there is only one row used. When i move to A2 and put cancelled, it adds another comma to any cell with cancelled. In short, every time i use the code, all rows with cancelled adds additional comma to their values. Hope someone can improve your code.
-Marc L
For some reason, when I use the code, all the base values from N,O,P,Q,R becomes empty.
Here I will upload the file. I will use the code on the first sheet.
On my side it rocks at a glance, so something weird with your workbook …
Anyway, try this
(and you can try the easy Find method for a faster execution maybe, as you can see within VBA inner help) :
PHP Code:Sub Demo1a()
Const F = "TRANSPOSE(IF(#=""CANCELLED"",ROW(#)))"
Application.EnableEvents = False
For Each V In Filter(Evaluate(Replace(F, "#", Range("A5", [A4].End(xlDown)).Address)), False, False)
With Cells(V, 14).Resize(, 5)
.Value = Evaluate(Replace("IF(#="""","""","",""&#)", "#", .Address))
End With
Next
Application.EnableEvents = True
End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
VBA option that does not add multiple commas:
Please Login or Register to view this content.
Please help by:
Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know
There are 10 kinds of people in this world... those who understand binary, and those who don't.
- Marc L
Thank you for the code but still it adds another comma to the previous cancelled cells...
- Arkadi
Perfect pal, just what I needed. If you could edit the code to something like, if its not cancelled, then remove the existing commas, I thank you so much. But the code alone is enough for me.. Again Thank You!
I am not at a computer right now but will post an update in the morning to remove a leading comma if the value of column A is not "CANCELLED"
Thank you, I will wait.
This should do
Please Login or Register to view this content.
Hi Jin,
The formula removes commas on non cancelled room, however it add commas to the blank cells on a current cancelled room.
Then change
toPlease Login or Register to view this content.
Please Login or Register to view this content.
Works perfectly now!!! Thank you so much Jin and all those who help!!!
Originally Posted by domz3669
Yes in case if you run it twice but as a demonstration you can tweak the formula.
Anyway, thanks for the rep' !
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks