Solved: Whole Numbers

[rob0923]

Hi,

I am adding sums with the code to get a total value even if the cell is empty it will return a value of "0" which is exactly what I want, but it will return a value in a bookmark to 0? Is there anyway to use the Replace function to on target "0", but not effect anything else such as 40 so it willl not return as 4



With Worksheets("Sheet1")
Figure = WorksheetFunction.Sum(.Range(.Range("I45"), .Range("J45")))
Figure1 = WorksheetFunction.Sum(.Range("A2"))
End With

With objDoc.Bookmarks
.Item("PlanNos1").Range.Insertafter (Figure * Figure1)

[nst1107]

Try testing the length of your string. If it's greater than 2, there's no need to use the Replace function, and so your innocent 0's won't be removed.

[Paul_Hossler]

Try putting your bookmark a field and formatting the field


{=[bookmarked text] \# “##0”}
of course using ctrl-F9 to insert the field

Paul

[rob0923]

Sorry i'm still fairly new the vba, how would you test the length..

If Len(Range.Text) > 2 Then Replace "0", ""?

[nst1107]

Yes, using Len() to test the length of your expression was what I was implying. However, on second thought, why not use a simpler statement likeIf Figure1 = 0 Then Figure1 = 1Wouldn't that accomplish the same thing?

[rob0923]

I can test it out, but wouldn't that convert all 0's to 1's

[nst1107]

It would only affect your variable Figure1 if it's value was 0.

[rob0923]

I have about 100 variables.Think it would be easier to use an if, but instead of using a replace on the cells. I think I will use it on the bookmarks.

[rob0923]

How can I place the code to tell a variable to replace the 0 only if the variable is less the 2 characters?

[mdmackillop]

Where do 100 variables come in? Your question relates to one value passed to a bookmark. Can you post the whole of your code, or possibly a sample workbook to demonstrate what you really need?

[rob0923]

Hi,

Basically I have the following:


Private Function Transfer

Dim A, B, C, D, E, F, G, H, I, J As Long

A = WorksheetFunctionSum(.Range("A1")
B = WorksheetFunctionSum(.Range("B1")
C = WorksheetFunctionSum(.Range("C1")
D = WorksheetFunctionSum(.Range("D1")
E = WorksheetFunctionSum(.Range("E1")

F = WorksheetFunctionSum(.Range("A2")
G = WorksheetFunctionSum(.Range("B2")
H= WorksheetFunctionSum(.Range("C2")
I = WorksheetFunctionSum(.Range("D2")
J = WorksheetFunctionSum(.Range("E2")

With objDoc.Bookmarks
.Item("Bookmarks1").Range.Insertafter (A * F)
.Item("Bookmarks2").Range.Insertafter (B * G)
.Item("Bookmarks3").Range.Insertafter (C * H)
.Item("Bookmarks4").Range.Insertafter (D * I)
.Item("Bookmarks5").Range.Insertafter (E * J)
End With



However if bookmark 1 = 40 and Boomark2 = 0
I would like to remove the single zero but leave the 0 on the fourty alone.

[nst1107]

First off, since it looks like you are doing what I used to do, this lineDim A, B, C, D, E, F, G, H, I, J As Longdims all variables except J as the default type, variant, not as long. To dim them all as long, you have to put "As Long" after each variable expression.

Second, rather than use WorksheetFunction.Sum() (since you are only summing a single-cell range, that is useless overhead), try using WorksheetFunction.Large(Array(1, .Range(YourRange), 1). I just ran across it and it looks perfect for your situation.

[rob0923]

The A,B,C,D As Long seems to working correctly. Is something else going to happen if I don't change these?

[nst1107]

Probably nothing will happen in your particular situation. The same values that can be passed to a long type variable can be passed to a variant. The only occasion where you'd run into trouble is where some data type such as a string or a range got passed to your variant variables and then you tried to use a method with them that would only support long data. You'd then get an error, etc.. Just noticed that you were declaring them as variant and that you probably meant to declare them as long. I used to do that all the time, so I thought I'd share the info.

[rob0923]

Thanks Nate,

Seems that I was actual looking for a variant rather then long. The long data was working correctly for numbers that don't have decimal points. If it does it rounds the number down. Variants will carry the decimal points, can integers carry decimal points aswell?

[rob0923]

Incase any one runs into this problem. I didn't solve how to test for the length "Len" but ran the line on if's

if worksheets("sheet1").range("a1") = "0" then...

[mdmackillop]

Thanks Nate,

Seems that I was actual looking for a variant rather then long. The long data was working correctly for numbers that don't have decimal points. If it does it rounds the number down. Variants will carry the decimal points, can integers carry decimal points aswell?
If you need non integer values, declare as Single or Double.