Show % of current rows in total rows.

[redbull]

I was having a problem displaying a %, but it was because I am an idiot and spent half the morning calling "CurrentRow" something else. But now I have that part working. I still am having a problem displaying the number in the correct format. I'd like it to show the % upto 2 places after the decimal. But It's not working yet.



I want the % to be shown to the user as 25.74% but I can't get it to work? Any ideas?

ws.Cells(1, 4) = "Percent Complete: %" & Format(maths, "##.##") ' Working but the format is all messed up.

This doesn't work, it just puts everything on the right side of the decimal place. And if I leave it without the formatting it seems to go 10 places ( didn't actually count them) past the decimal.

[JoeM]

Where exactly are you trying to return this value? In Excel?

What does the unformatted result look like?
25.74...
or
.2574...

[redbull]

.2574 is what's being filled out now.

25.74 is what I want to see.

[JoeM]

OK, what about the other question?
Are you writing the result out to an Excel spreadsheet directly (it might be helpful to see the rest of the relevant code).
If so, you probably want to format the cell, and not the result, as formatting the result will change it to a Text value and not a numeric one.

[redbull]

Alright, here is a bigger picture.

Sub Name()


Dim xlApp As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

Set ws = wb.Sheets("Sheet1")

Set wb = xlApp.Workbooks.Open("MySpreadsheet.xlsx")

With ws
  .Range("A1:D1").Interior.Color = RGB(216, 228, 188)
  .Cells.Font.Name = "Calibri"
  .Cells.Font.Size = 8
  .Cells(1, 4).NumberFormat = "0.00%"   ' This is the cell I want to show as a %,
  .Columns("C").HorizontalAlignment = xlLeft
End With

Do Until Cows_Come_Home = True 

'Code does a lot of stuff here. 

'Code checks for confirmation here

'Code then updates excel here
   
    mathS = RowNum / LastRow
    ws.Cells(1, 4) = "Percent Complete: %" & mathS

'Code does tons of other stuff here

Loop

msgbox "Moo"

End Sub

As you can see I don't really want to set the cell .numberformat, because I also want to pass text into the cell.

I've tried doing this.

  .Cells(1, 4).NumberFormat = "0.00%"

    mathS = RowNum / LastRow
    CleanPerc = Format(mathS "@.@@") 
    ws.Cells(1, 4) = "Percent Complete: %" & CleanPerc

And a host of other things that I've found online.

This is the list of outputs I'm getting from the code at the top of this post.

Percent Complete: %9.04977375565611E-03
Percent Complete: %1.35746606334842E-02
Percent Complete: %1.80995475113122E-02
Percent Complete: %2.26244343891403E-02
Percent Complete: %2.71493212669683E-02
Percent Complete: %3.16742081447964E-02
Percent Complete: %3.61990950226244E-02
Percent Complete: %4.07239819004525E-02
Percent Complete: %4.52488687782805E-02
Percent Complete: %4.97737556561086E-02
Percent Complete: %5.42986425339367E-02
Percent Complete: %5.88235294117647E-02
Percent Complete: %6.33484162895928E-02
Percent Complete: %6.78733031674208E-02
Percent Complete: %7.23981900452489E-02
Percent Complete: %7.69230769230769E-02
Percent Complete: %0.081447963800905
Percent Complete: %0.085972850678733
Percent Complete: %9.04977375565611E-02
Percent Complete: %9.50226244343891E-02
Percent Complete: %9.95475113122172E-02
Percent Complete: %0.104072398190045  ' It should be around 10% here, 200ish totalrows on this attempt
Percent Complete: %0.108597285067873
Percent Complete: %0.113122171945701
Percent Complete: %0.117647058823529
Percent Complete: %0.122171945701357
Percent Complete: %0.126696832579186
Percent Complete: %0.131221719457014
Percent Complete: %0.135746606334842
Percent Complete: %0.14027149321267
Percent Complete: %0.144796380090498
Percent Complete: %0.149321266968326
Percent Complete: %0.153846153846154
Percent Complete: %0.158371040723982
Percent Complete: %0.16289592760181
Percent Complete: %0.167420814479638
Percent Complete: %0.171945701357466
Percent Complete: %0.176470588235294
Percent Complete: %0.180995475113122
Percent Complete: %0.18552036199095
Percent Complete: %0.190045248868778
Percent Complete: %0.194570135746606
Percent Complete: %0.199095022624434
Percent Complete: %0.203619909502262
Percent Complete: %0.208144796380091
Percent Complete: %0.212669683257919
Percent Complete: %0.217194570135747
Percent Complete: %0.221719457013575
Percent Complete: %0.226244343891403
Percent Complete: %0.230769230769231
Percent Complete: %0.235294117647059
Percent Complete: %0.239819004524887
Percent Complete: %0.244343891402715
Percent Complete: %0.248868778280543

I'm at a loss.

[JoeM]

OK, right, since you are also returning text in that cell, formatting the cell will not have any effect. We need to format the result.

In your original code, try changing this:

mathS = RowNum / LastRow

to this:

mathS = Round((RowNum / LastRow) * 100,2)

[redbull]

I appreciate it. I don't know why this works but it is working fantastically now. Added to rep and marking as solved.

[JoeM]

You are welcome!

All I did was multiple your result by 100, and then round it off to two decimal places.
So, when you drop it into the rest of your verbiage, it is already looking the way you want it.

Conceivably, you should also be able to do the same thing withe FORMAT function (maybe something like: Format(maths, "0.00")), but there is often more than one way to skin a cat!

[redbull]

Yea, I did try the format function, but it didn't change the output either. It was the first thing I tried. Hmm, either way. Thanks again.