Formatting Active Cells

  • Thread starter Thread starter Paul Black
  • Start date Start date
P

Paul Black

Hi everyone,

How can I format the following please :-

Worksheets("Test").Select
With ActiveCell
.Offset(1, 0).Value = cmb & " If " & pik <--- Format Right
.Offset(1, 1).Value = Tested <--- Accept Thousands & NO
Decimal Places
.Offset(1, 2).Value = tly <--- Accept Thousands & NO Decimal
Places
.Offset(1, 3).Value = 100 / Tested * tly <--- 5 Decimal Places
.Offset(1, 4).Value = Tested - tly <--- Accept Thousands & NO
Decimal Places
.Offset(1, 5).Value = (100 / Tested) * (Tested - tly) <--- 5
Decimal Places
.Offset(1, 6).Value = tly + Tested - tly <--- Accept
Thousands & NO Decimal Places
.Offset(1, 7).Value = (100 / Tested * tly) + ((100 / Tested) *
(Tested - tly)) <--- General & NO Decimal Places
.Offset(1, 0).Select
End With

Thanks in Advance.
All the Best.
Paul
 
Hi Paul,

I'm not sure what you mean by accept 1000's and no decimals. I have assumed
you mean use the comma to separate as per 1,000. However, here is an example
of formatting integrated into you code.

If the formats are not exactly as you want then simply record a macro as you
set the required formats and you will be able to get the syntax to edit the
example.

There is no general format with no decimals. General format will include
decimals if they are there or leave them out if not there.

Worksheets("Test").Select
With ActiveCell
.Offset(1, 0).Value = cmb & " If " & pik
.Offset(1, 0).HorizontalAlignment = xlRight
.Offset(1, 1).Value = Tested
.Offset(1, 1).NumberFormat = "#,##0"
.Offset(1, 2).Value = tly
.Offset(1, 2).NumberFormat = "#,##0"
.Offset(1, 3).Value = 100 / Tested * tly
.Offset(1, 3).NumberFormat = "0.00000"
.Offset(1, 4).Value = Tested - tly
.Offset(1, 4).NumberFormat = "#,##0"
.Offset(1, 5).Value = (100 / Tested) * (Tested - tly)
.Offset(1, 5).NumberFormat = "0.00000"
.Offset(1, 6).Value = tly + Tested - tly
.Offset(1, 6).NumberFormat = "#,##0"
.Offset(1, 7).Value = (100 / Tested * tly) + ((100 / Tested) * (Tested -
tly))
.Offset(1, 7).NumberFormat = "General"
.Offset(1, 0).Select
End With

Regards,

OssieMac
 
Hi OssieMac,

One last point please.
What about if I wanted to format ...

..Offset(0, 0).Value = "For 1 - " & P & " there are " & tly & "
different sets of " & cmb & " numbers. "

.... as "#,##0" for the & tly & output please?.

Thanks in Advance.
All the Best.
Paul
 
Hi again Paul,

If I understand correctly you are putting a string together by
concatenation. When you do this the whole lot becomes a string and while
formatting can be done in one line of code with nested formulas I think that
it is better to create new formatted string values before concatenating for
the output.

The following is an example only and you will have to work through it and
edit your code accordingly. I only did a couple of your variables as
examples. The format between the double quotes can be any format that you can
find in the dialog box including custom formatting in the usual manual
formatting of cells on the worksheet. You can even create a string value from
a date variable to return only month and year like this:-

dateString = format(datevariable,"mmm yyyy")

Give it a try and get back to me if still having a problem. However, if you
have to get back to me on it then give me an example of what each of your
variables contains and how you want the output to appear such as:-

P = 5.8674988574 to appear as 5.86750.


Dim P As Single 'Numeric value
Dim Pstr As String 'String value
Dim tly As Single 'Numeric value
Dim tlyStr As String 'String value


P = 1001.85473568 'In numeric format

Pstr = Format(numericValue, "0.00000") 'In string format

tly = 1000000 'In numeric format

tlyStr = Format(tly, "#,##0") 'In string format

'In your code replace the numeric variables with the
'formatted text variables

With ActiveCell
..Offset(0, 0).Value = "For 1 - " & Pstr & " there are " _
& tlyStr & " different sets of " & cmb & " numbers. "
End With

Regards,

OssieMac
 
Back
Top