VBA- Recognize periods (.) as 0s??

[Barklie]

Hi all,

---

Is there anyway to make VBA recognize periods as 0s. I have a macro that color codes an automatically generated report, but sometimes 0's show up normally and sometimes as periods (.). This makes rows that should appear green (CC: 3407718) not do so.

---

Here is my current code:

---

    i = 2
    Do While Cells(i, 1) <> ""
        If Cells(i, 13) = Cells(i, 14) And Cells(i, 11) = Cells(i, 12) Then
            Range("A" & i, "W" & i).Interior.Color = 3407718
        ElseIf Cells(i, 21) > 70 And Cells(i, 21) < 150 Then
            Range("A" & i, "W" & i).Interior.Color = 65535
        End If
    i = i + 1
    Loop

---

This only happens in Cells(i, 13) and Cells(i, 14), so only these would need modification. Thanks!

 

[Rick Rothstein]

Hi all,

---

Is there anyway to make VBA recognize periods as 0s. I have a macro that color codes an automatically generated report, but sometimes 0's show up normally and sometimes as periods (.). This makes rows that should appear green (CC: 3407718) not do so.

---

Here is my current code:

---

    i = 2
    Do While Cells(i, 1) <> ""
        If Cells(i, 13) = Cells(i, 14) And Cells(i, 11) = Cells(i, 12) Then
            Range("A" & i, "W" & i).Interior.Color = 3407718
        ElseIf Cells(i, 21) > 70 And Cells(i, 21) < 150 Then
            Range("A" & i, "W" & i).Interior.Color = 65535
        End If
    i = i + 1
    Loop

---

Thanks!

Where in your current code are these "periods that should be zeroes" at?

 

[Andrew Poulsom]

Maybe:

If Replace(Cells(i, 13), ".", "0") = Replace(Cells(i, 14), ".", "0") And Replace(Cells(i, 11), ".", "0") = Replace(Cells(i, 12), ".", "0") Then

 

[Barklie]

It was columns 11 and 12. My final code is this:

    i = 2
    Do While Cells(i, 1) <> ""
        If Replace(Cells(i, 11), ".", "0") = Replace(Cells(i, 12), ".", "0") And _
            Cells(i, 13) = Cells(i, 14) Then
            Range("A" & i, "W" & i).Interior.Color = 3407718
        ElseIf Cells(i, 21) > 70 And Cells(i, 21) < 150 Then
            Range("A" & i, "W" & i).Interior.Color = 65535
        End If
    i = i + 1
    Loop

Thanks!

 

[Rick Rothstein]

It was columns 11 and 12. My final code is this:

    i = 2
    Do While Cells(i, 1) <> ""
        If Replace(Cells(i, 11), ".", "0") = Replace(Cells(i, 12), ".", "0") And _
            Cells(i, 13) = Cells(i, 14) Then
            Range("A" & i, "W" & i).Interior.Color = 3407718
        ElseIf Cells(i, 21) > 70 And Cells(i, 21) < 150 Then
            Range("A" & i, "W" & i).Interior.Color = 65535
        End If
    i = i + 1
    Loop

Thanks!

Did you want to correct the date in those two columns instead, then you could use your existing code as well as any new code involving those two columns without having to remember to include the Replace function calls that Andrew gave you? If so, just run this macro and your original data will be corrected in place...

Sub ChangePeriodsToZeroesInColumnsKandL()
  Columns("K:L").Replace ".", 0, xlpart
End Sub

Note that a macro is not necessary to do this... you could select Columns K:L, press CTRL+H to bring up the Replace dialog box, put a single period in the "Find what" field, a 0 in the "Replace with" field, click the "Options>>" button to make sure there is no checkmark in the "Match entire cell contents" checkbox, then click the "Replace All" button.

 

[Barklie]

I decided to replace them instead and return my original code. I'd prefer not to do a find/replace everytime a report is generated, so this is my most helpful option. Thanks again!

    Columns("K:L").Replace ".", 0, xlPart
'Color Code Data
   i = 2
    Do While Cells(i, 1) <> ""
        If Cells(i, 11) = Cells(i, 12) And Cells(i, 13) = Cells(i, 14) Then
            Range("A" & i, "W" & i).Interior.Color = 3407718
        ElseIf Cells(i, 21) > 70 And Cells(i, 21) < 150 Then
            Range("A" & i, "W" & i).Interior.Color = 65535
        End If
    i = i + 1
    Loop