Replace cell value with zero base of cell value

[VbaHell]

Hello all

I hope you can help on this please

I want to change the cell values based on another cell value and below is the code I have been trying to modify for this to work
This macro completes the changes but leaves the value in each cell as "FALSE", I need this to be either blank or 0.00 in each cell.

[
With ThisWorkbook.Sheets("Raw Data")
For r = 1 To .Cells(Rows.Count, "AC").End(xlUp).Row
If Cells(r, "AC") = "RS" Then
If .Cells(r, "R") > 0 Then .Cells(r, "R") = .Cells(r, "R") = ""
If .Cells(r, "S") > 0 Then .Cells(r, "S") = .Cells(r, "S") = ""
If .Cells(r, "T") > 0 Then .Cells(r, "T") = .Cells(r, "T") = ""
If .Cells(r, "AA") > 0 Then .Cells(r, "AA") = .Cells(r, "AA") = ""
If .Cells(r, "AB") > 0 Then .Cells(r, "AB") = .Cells(r, "AB") = ""
End If
Next r
End With]

 

[vmjan02]

Kindly try this

With ThisWorkbook.Sheets("Raw Data")
For r = 1 To .Cells(Rows.Count, "AC").End(xlUp).Row
If Cells(r, "AC") = "RS" Then
If .Cells(r, "R") > 0 Then .Cells(r, "R") = .Cells(r, "R") = """"
If .Cells(r, "S") > 0 Then .Cells(r, "S") = .Cells(r, "S") = """"
If .Cells(r, "T") > 0 Then .Cells(r, "T") = .Cells(r, "T") = """"
If .Cells(r, "AA") > 0 Then .Cells(r, "AA") = .Cells(r, "AA") = """"
If .Cells(r, "AB") > 0 Then .Cells(r, "AB") = .Cells(r, "AB") = """"
End If
Next r

 

[jmacleary]

Hi there. Remove the duplicated part of each formula like this:

With ThisWorkbook.Sheets("Raw Data")
For r = 1 To .Cells(Rows.Count, "AC").End(xlUp).Row
If Cells(r, "AC") = "RS" Then
If .Cells(r, "R") > 0 Then  .Cells(r, "R") = ""
If .Cells(r, "S") > 0 Then  .Cells(r, "S") = ""
If .Cells(r, "T") > 0 Then  .Cells(r, "T") = ""
If .Cells(r, "AA") > 0 Then  .Cells(r, "AA") = ""
If .Cells(r, "AB") > 0 Then  .Cells(r, "AB") = ""
End If
Next r
End With

 

[vmjan02]

Use this code.

Sub text()


With ThisWorkbook.Sheets("Raw Data")
For r = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If Cells(r, "A") = "RS" Then
If .Cells(r, "R") > 0 Then .Cells(r, "R") = ""
If .Cells(r, "S") > 0 Then .Cells(r, "S") = ""
If .Cells(r, "T") > 0 Then .Cells(r, "T") = ""
If .Cells(r, "AA") > 0 Then .Cells(r, "AA") = ""
If .Cells(r, "AB") > 0 Then .Cells(r, "AB") = ""
End If
Next r
End With

End Sub

 

[VbaHell]

Thanks everyone for your replies, much appreciated

 

[VbaHell]

Hi All

Can I ask another question please

I want to change this to be able to copy all values in column "T" to column "Q" based on the same criteria

This works at the start but disappears when the loop finishes


[With ThisWorkbook.Sheets("Raw Data")
For s = 1 To .Cells(Rows.Count, "AC").End(xlUp).Row
If Cells(s, "AC") = "RS" Then
If .Cells(s, "T") > 0 Then .Cells(s, "Q") = .Cells(i, "T")
If .Cells(s, "R") > 0 Then .Cells(s, "R") = ""
If .Cells(s, "S") > 0 Then .Cells(s, "S") = ""
If .Cells(s, "T") > 0 Then .Cells(s, "T") = ""
If .Cells(s, "AA") > 0 Then .Cells(s, "AA") = ""
If .Cells(s, "AB") > 0 Then .Cells(s, "AB") = ""
End If
Next s
End With]

 

[VbaHell]

Sorted now, thanks everyone

 

[vmjan02]

Great