Do Until replay tree times

0 votes

To determine whether a cell is a number or not before deleting it, I'm using this code, however I have three columns to check. However, Do Until merely executes the loop once before ceasing all execution and changing the col to 5 or 8 as it is in the for loop.

Could you please explain to me what I'm doing incorrectly in this code?

Another issue I've run into is that when a cell is empty, VBA automatically fills it with the number 0. Is there a method to prevent this from happening?

Sub copy()

    Dim Row As Long
    Dim Col As Long

    Row = 1

    For Col = 2 To 8 Step 3

      Do Until Cells(Row, 1).Value = ""

        If IsNumeric(Cells(Row, Col)) = False Then
            Cells(Row, Col).Clear          

            Cells(Row, Col).Select

            If Cells(Row, Col).Value = 0 Then

               Cells(Row, Col).Value = (Cells(Row, Col).Value) * 1
               Cells(Row, Col).NumberFormat = "$ #,##0.00"


                Cells(Row, Col).Value = CDec((Cells(Row, Col).Value))
                Cells(Row, Col).NumberFormat = "$ #,##0.00"
            End If

        End If
        Row = Row + 1



End Sub
Feb 13, 2023 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

Clean Data: Apply Consistent Formatting in Columns

Option Explicit

Sub UpdateCurrency()

    ' Define constants.
    Const FIRST_ROW As Long = 2 ' adjust: you have headers, right?
    ' Reference the worksheet.
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    ' Hard to believe that you know the column numbers but not the worksheet name.
    ' Calculate the last row, the row of the bottom-most non-empty cell
    ' in the worksheet.
    Dim lCell As Range
    Set lCell = ws.UsedRange.Find("*", , xlFormulas, , xlByRows, xlPrevious)
    If lCell Is Nothing Then Exit Sub ' no data
    Dim LastRow As Long: LastRow = lCell.Row

    Dim crg As Range, cell As Range, cValue, Col As Long
    For Col = 2 To 8 Step 3 ' to not introduce further complications
        ' Reference the single-column range from the first to the last row.
        Set crg = ws.Range(ws.Cells(FIRST_ROW, Col), ws.Cells(LastRow, Col))
        ' Clear the undesired values (all except empty and numeric values).
        For Each cell In crg.Cells
            ' Write the cell value to a variant variable.
            cValue = cell.Value
            ' Check if the value is not numeric.
            If Not IsNumeric(cValue) Then cell.ClearContents
        Next cell
        ' Apply the formatting to the whole column range so it takes effect
        ' if you decide to add numbers to the empty cells.
        crg.NumberFormat = "$ #,##0.00" ' "\$ #,##0.00" if $ is not native
        ' Copy the values to memory, and copy them back to the range
        ' for the formatting to affect the remaining numerics
        ' (numbers and numbers formatted as text).
        crg.Value = crg.Value
    Next Col

    MsgBox "Currency updated.", vbInformation
End Sub
answered Feb 13, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
2 answers
0 votes
1 answer

How do I carry out functional testing?

Well, functional testing is pretty simple. it ...READ MORE

answered Feb 18, 2019 in Others by Jobin
0 votes
1 answer

How do I install Ruby on Rails?

Hi @Anvi, what @Pratibha has mentioned is ...READ MORE

answered Mar 1, 2019 in Others by Abha
• 28,140 points
0 votes
1 answer

How to do Installation of Ruby on Rails on Linux (Ubuntu)?

Hi Pratibha, installing ROR on ubuntu is ...READ MORE

answered Mar 1, 2019 in Others by Anvi
• 14,150 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

What are canonical URLs and how do they affect your SEO?

canonical URLs are distinct URL used to ...READ MORE

answered Feb 11, 2022 in Others by narikkadan
• 63,420 points
0 votes
1 answer

Do canonical links require a full domain?

actually its same for ordinary url and ...READ MORE

answered Feb 17, 2022 in Others by narikkadan
• 63,420 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP