How to modify a cell based in a value of another cell in a different column

[Borntobebad]

Hello,
I am looking for a vba code to modify Colum "F" row (which ever has the value 'CANCELADA' in Colum "Q".
Attached is the sample spreadsheet.

Thank you.

[Eastw00d]

How about filtering row 7 and below? and then select in column Q "CANCELADA"
no need for VBA
B/R
Erwin

[Borntobebad]

How about filtering row 7 and below? and then select in column Q "CANCELADA"
no need for VBA
B/R
Erwin

Erwin,
What I would like to happen is that when the option “CANCELADA” is picked on column “Q” column “F” same row as “Q” the value changes to ‘0’ automatically.
Thank you.

[Borntobebad]

Hello,
This is the vba code I am trying to use for this, but I can not make it work..Any suggestions?
I have no idea what i am doing wrong.

Thank you

Sub Delete_Rows()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'I use the ActiveSheet

With ActiveSheet

'I select the sheet so we can change the window view
.Select

'If you are in Page Break Preview Or Page Layout view go
'back to normal view, I do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'Turn off Page Breaks, I do this for speed
.DisplayPageBreaks = False

'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'I loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1

'I check the values in the Q column
With .Cells(Lrow, "Q")

If Not IsError(.Value) Then

If .Value = "CANCELADA" Then .Cells(Lrow, "F").Value = 0
'I am tring to change row "F" and column found in "Q" to '0' if the value found is 'CANCELADA'
'DOES NOT WORK!! WHAT AM I DOING WRONG!! PLEASE HELP!!!
End If

End With

Next Lrow

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

[Eastw00d]

Hi Borntobebad,
please find attached the file with vba-macro
B/R
Erwin

[Eastw00d]

Sorry, I did not see until now that you have written also code. Let me know if I can help.
Erwin

[Eastw00d]

with a small adjustment it works:
leave out the "With"statement:

Please Login or Register  to view this content.

B/R
Erwin

[Borntobebad]

Erwin,
YOU ARE A GENIUS!!
Thank you very much....You have no idea how many hours I spent trying to make this work.
Thank you again!!!!

[Borntobebad]

Thank you very much.
This works too!!

Do you recommend to use this vba or the one I wrote with the mod?

[Eastw00d]

The short one uses less memory/time, so I would use that one.
If you want to thank me, please give me some rep.points :-)
B/R
Erwin

[Marc L]

Two ways :

• when an entry 'CANCELADA' is made in column Q : it needs a worksheet event procedure …
  
  
• Let's see first an ŕ posteriori procedure just using a beginner level formula,
  paste this VBA demonstration to the Sheet1 worksheet module :
  
  

Sub Demo1() 
    Dim Rg As Range 
    Set Rg = Cells(Rows.Count, 17).End(xlUp) 
     If Rg.Column > 7 Then 
        With Range("Q8", Rg) 
            .Offset(, -11).Value2 = Evaluate("IF(" & .Address & "=""CANCELADA"",0," & .Offset(, -11).Address & ")") 
        End With 
     End If 
    Set Rg = Nothing 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !

[Borntobebad]

Thank you!!