Find and Replace any values of 0 or more

[carefreeant88]

Good afternoon,

One last piece of help please!

I need to basically achieve the following via VBA/Macro:

1. Go to the sheet called 'Worksheet10'
2. Select the Range of Cells from C2 through to AB50000
3. Turn any negative values in that range, into 'Light red fill with Red Text' (ie the default option from Conditional Formatting for Negative Cells)
4. Find any values in that same range of 0 or more, and replace that value with a blank cell (ie delete the value)

Thanks in advance for any help!

 

[saboh12617]

Hello,

You can use .Find in VBA for this. Assuming you left the negatives values with a leading "-", the Macro below should work.

Note that the cell coloring is not dynamic. I think it could really slow down your worksheet on such a big range.

Sub findInRng()
  Dim myRng As Range, f As Range, startAdr As String
  Set myRng = ThisWorkbook.Worksheets("Worksheet10").Range("C2:AB50000")
  Application.ScreenUpdating = False
  
  ' erasing 0s
  With myRng
    Set f = .Find(0, LookIn:=xlValues, LookAt:=xlWhole)
    If Not f Is Nothing Then
      startAdr = f.Address
      Do
        f.Value = vbNullString
        Set f = .FindNext(f)
        If f Is Nothing Then Exit Do
      Loop While f.Address <> startAdr
    End If
  End With
  
  ' highlighting negative values
  With myRng
    Set f = .Find("-", LookIn:=xlValues, LookAt:=xlPart)
    If Not f Is Nothing Then
      startAdr = f.Address
      Do
        f.Interior.Color = 10525661
        f.Font.Color = vbRed
        Set f = .FindNext(f)
        If f Is Nothing Then Exit Do
      Loop While f.Address <> startAdr
    End If
  End With
  
  Application.ScreenUpdating = True

End Sub

 

[rpaulson]

another option:

Sub do_it()

Set Rng = Worksheets("Worksheet10").Range("C2:AB50000")

For Each cell In Rng
        If IsNumeric(cell.Value) Then
            Select Case cell.Value
                Case Is < 0
                    cell.Font.Color = RGB(255, 102, 102) ' Light red
                Case Is > 0
                    cell.Font.Color = vbBlack ' Black
                Case Is = 0
                    cell.ClearContents ' Delete the cell's content
            End Select
        End If
    Next cell

End Sub

 

[mumps]

Given the large number of cells, this may speed things up a bit:

Sub ReplaceValues()
    Application.ScreenUpdating = False
    Dim v As Variant, r As Long, c As Long, lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Sheets("Sheet1").Range("C2:AB" & lRow)
    For r = LBound(v) To UBound(v)
        For c = LBound(v, 2) To UBound(v, 2)
            If v(r, c) < 0 Then
                Cells(r + 1, c + 2).Font.Color = vbRed
                Cells(r + 1, c + 2).Interior.Color = 10525661
            Else
                Cells(r + 1, c + 2).ClearContents
            End If
        Next c
    Next r
    Application.ScreenUpdating = True
End Sub

 

[saboh12617]

Hello guys,

Just out of curiosity, is looping over the whole range faster than using Find? I thought it was faster.

If so you might as well just put the whole range values directly in a variant array, loop over it, stock the cells coordinates in a range incremented via union (for the 0 just replace them) and apply to this range the formatting at the end. It would be the fastest option imo?

Have a good day.

 

[carefreeant88]

Morning guys,

Thanks for your help.

Unfortunately, none of them have quite worked. In each case, they are only sorting 1 cell at a time for some reason.

So for instance if I run the Macro, it will find a Cell in C2 that needs clearing, and successfully clears it. Then I run it again, and it does the same in D2. Then again in E2 and so on and so forth. But instead of doing it all in one go, I am having to run the Macro for each cell I want it to check and amend.

Any ideas as to how I can make it apply the Macro to every cell in one go, instead of one at a time?

Cheers

 

[Fuji]

I need to basically achieve the following via VBA/Macro:

1. Go to the sheet called 'Worksheet10'
2. Select the Range of Cells from C2 through to AB50000
3. Turn any negative values in that range, into 'Light red fill with Red Text' (ie the default option from Conditional Formatting for Negative Cells)
4. Find any values in that same range of 0 or more, and replace that value with a blank cell (ie delete the value)

See if this works as you want.

Sub test()
    With Sheets("worksheet10").[c2:ab50000]
        .FormatConditions.Delete
        .FormatConditions.Add 2, , "=" & .Cells(1).Address(0, 0) & "<0"
        .FormatConditions(1).Interior.Color = vbRed
        .Value = .Parent.Evaluate(Replace("if((isnumber(#)*(#>=0)),"""",if(#<>"""",#,""""))", "#", .Address))
    End With
End Sub

 

[carefreeant88]

See if this works as you want.

Sub test()
    With Sheets("worksheet10").[c2:ab50000]
        .FormatConditions.Delete
        .FormatConditions.Add 2, , "=" & .Cells(1).Address(0, 0) & "<0"
        .FormatConditions(1).Interior.Color = vbRed
        .Value = .Parent.Evaluate(Replace("if((isnumber(#)*(#>=0)),"""",if(#<>"""",#,""""))", "#", .Address))
    End With
End Sub

Thank you! Works perfectly!

Thanks again

 

[Fuji]

You are welcome and thanks for the feedback.