CODEWITHSUNDEEP
vbaexcelexcel-2010
Sam
Sam

Reputation: 65

Using VBA to multiply all cells that are format(%) in a worksheet by 100 and then convert to format(Number)

I'm trying my best with this, but am a newbie to VBA and am getting no-where. I know the etiquette seems to be to post what you're trying to work with, but it's a useless mess of snippets taken from SE.

I scrapped it all and then thought I'd ask for help before starting again.

Any advice - in the form of code or pointers to resources - would be much appreciated.

I have a worksheet with text cells and % cells. I just need a macro to scan through and convert all the %ages to real numbers, which basically involves multiplying by 100 and then changing the cell format.

Thank you all,

Sam

Upvotes: 0

Views: 901

Answers (2)

Gary's Student
Gary's Student

Reputation: 96771

Try this small macro:

Sub FixFormat()
    For Each r In ActiveSheet.UsedRange
        If InStr(1, r.NumberFormat, "%") > 0 Then
            v = r.Value
            r.Clear
            r.Value = 100 * v
            r.NumberFormat = "General"
        End If
    Next r
End Sub

Upvotes: 1

liorsolomon
liorsolomon

Reputation: 1883

Sub convertToNumber()
    For Counter = 1 To 20
        Set curCell = Worksheets("Sheet1").Cells(Counter, 1)
        curCell.NumberFormat = "#,##0.00"
    Next Counter
End Sub

I hope that helps

Upvotes: 0

Related Questions