Change one cell value based on another

[harvihovart]

Howdy!

I have probably a simple problem to solve but I have been around in circles and not getting anywhere!
Basically I have a column M ("Reject" - values are 0 or 1) and another column R called "Status". I would like a command button to scroll through each row and where Col M = "1" the code will check what is in Col R and write "Cancelled".

So where cell in Col M = "1" then cell in Col R = "Cancelled"

I have been able to get this working (sort of) but individual cells, but fail each time I try to get it to work on the entire data range

any help is appreciated!!!

thanks
Harvi

[Fotis1991]

Hi

Why do you need a macro for this, As you can do, using formula?

[harvihovart]

Hi Fotis,

basically I get the data as an export from another system, when I get the data it should already be as above (where Col = 1 then other Col = "Cancelled"), but the tool is not consistent in this, so I have to filter then flood fill, and I have to do this on many other cols (but based on other criteria). So i really want to create some automation where I can tody up the data in one click

thanks!
Harvi

[Fotis1991]

..OO. I understand! So, you are welcome

[harvihovart]

So I have this so far - works for one cell reference but not sure how to expand it to look through all rows in the file...

Sub Button4_Click()

Sheets("New").Activate

If Range("X2").Value = "1" Then
Range("R2").Value = "Cancelled"
Else
End If


End Sub

[Pichingualas]

Try this:

Please Login or Register  to view this content.

I hope that helps.

[Pichingualas]

By the way please check the forum rules.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Select your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window:

[code ]Please Login or Register to view this content.[/CODE] You can also type the code tags in manually if you prefer.

[harvihovart]

will keep to the rules
thanks for that code, worked a treat - and I think I understand it as well

I was trying to modify it to perform a similar function, but to delete rows with certain criteria (these are rows that come from the other system erroneously and I don't need them)

so the criteria was where the status was "Cancelled" or "Cancelled<24" (so I chose the LIKE statement, hope it works ;-) ) and where the value in column 8 is blank (again, not sure if just leaving it blank is valid?

then I know the "entirerow.delete" will work in there somewhere but can't seemt to work it out

I know I am a noob, but I am trying

Please Login or Register  to view this content.

[Pichingualas]

Try like this:

Please Login or Register  to view this content.

Or if you want to use Like then:

Please Login or Register  to view this content.

The problem with your code was that you didn't tell which line to delete. And if you dont add the * after cancelled in your like statement, it will only do it for the ones that are exactly cancelled, leaving behind the cancelled<24.

[harvihovart]

Thanks Pinchingualas!!

Thanks for the reply, understand where I went wrong - thanks!
but... (there's always a but, right? ;-) )

I stated my original intention incorrectly, but the point is the same - I need to actually delete all rows where the status is NOT like "Cancelled" (so includes "Cancelled<24")

I used the <> statement, but not sure if I can combine it with the "Like" statement as well?

Also, it's kinda strange but when I run the code, it does a couple of weird things -first of all it takes maybe 10 -15 seconds and the screen flashes like crazy (looks like it is cycling through the rows a lot, or something) and then it always leaves two records behind (which match the criteria - the status is "Cancelled" and the 8th Column is blank... weird...)

here's my code:

Please Login or Register  to view this content.

any ideas?

[Pichingualas]

Yeah I know whats wrong here. When you delete the i row, it is possible that the next one, which would be i+1, also meets the criteria but won't get deleted cause it will have turned into the i row, meaning the code won't check it again. So we have to change the current i when the row gets deleted. Also, as the total amount of written rows in your sheet will be changing, we need to update the end of the for accordingly. When this happens, I choose to use the Do Loop strategy instead.

Please Login or Register  to view this content.

If you want it not to be like "Cancelled*" then you ccan add exactly that into the code, the Not operator returns True when the statement inside is False. You could be using the <>, but you would have to change it to what I had shown you before:

Please Login or Register  to view this content.

because you need to make sure that what you are checking is exactly "Cancelled". I mean the <> does not work as the Like statement does, in which you can put in the * at the end to say "anything can go after this", but it actually checks for exact matches.

The screen flickering is caused by the fact that we didn't turn off the ScreenUpdating, so you are seeing it delete the rows.

I hope that helps. Please tell me if you have any other issues with this.