Keep trailing any trailing zeros in formula in vba

[ArnMan]

Hello,
I was wondering how I can keep my trailing zeros when I reference a cell from one sheet to another sheet in a = text & value & value statement
And the numbers of zeros is going to be different so setting it with Fixed isn't going to work, I don't think.

I have 2 sheets
Sheet1
Sheet2

in Sheet1 M column I have values, each one is going to be different, and some of them may have one or more trailing zeros. I need to show any zeros shown and Sheet2 needs to have a reference to Sheet1.

I have a if with loop that goes through each cell in M column on sheet one and references them on Sheet2 in the .Value in the code below adding a plusminus and value from another cell in Sheet2
Example
Sheet2 E17 value = "V"
Sheet1.M17 value is 18.0000
Sheet2 is supposed to display ±18.0000V

instead my code only shows ±18V

And I should be able to click on the cell in Sheet2 and in the formula bar, it should show the reference I cant have it just be text. Because the value on Sheet1 may change and it needs to reflect on Sheet2.

="±" & ('Sheet1'!M17)&(Sheet2!E17)

It needs to show all the zeros in Sheet1 whether it be 1 zero or 5, even though I know 18 and 18.0000 are the same.

Dim lRow As long
lRow = Sheets("Sheet1").Cells(Rows.Count, "AAA").End(xlUp).Row
Dim i As Integer
    For i = 7 To lRow

If IsNumeric(Sheets("Sheet1").Range("M" & i)) = True Then

With ACCws.Range("I" & i)
            .Borders.LineStyle = xlNone
            .Interior.Color = xlNone
            .HorizontalAlignment = xlCenter
            .Value = "=""±"" & ('Sheet1'!M" & i & ")" & "&" & "('Sheet2'!E" & i & ")"
        End With

Thank you anyone in advance Any help would be greatly appreciated.

 

[kvsrinivasamurthy]

Re: Need to keep trailing any trailing zeros in formula in vba

The number of zero's are fixed ?

 

[ArnMan]

Re: Need to keep trailing any trailing zeros in formula in vba

No the number of decimal places is going to be different in each cell and random. So I wont know before hand what cell decimal places will be. But now that that question was asked. I do have a number I can go by on a different sheet. I have several tabs and on the main sheet, I have a place where I put in a number for generate the number format way before Sheet 1 and Sheet 2 are even created. I can make a column in Sheet1 and when those numbers are entered in the Main Sheet I'll copy them in Sheet1. That way I can used fixed, if it will work that way.

Thank you I will let you know how that works

 

[ArnMan]

Re: Need to keep trailing any trailing zeros in formula in vba

The number of zero's are fixed ?

Now that I think about it. Sometimes these numbers will be imported from elsewhere and I wont have the number of decimals saved anywhere. So I am wondering if there is a way to count the decimal places and use that in FIXED.

 

[Rick Rothstein]

Re: Need to keep trailing any trailing zeros in formula in vba

Are the values in Column M on Sheet 1 Text or are they numbers that have been Cell Formatted to show the trailing zeroes?

 

[ArnMan]

Re: Need to keep trailing any trailing zeros in formula in vba

Those numbers are previously formatted using a code snippit you had created for me in one of my first posts. Which is working like a champ by the way.

Are the values in Column M on Sheet 1 Text or are they numbers that have been Cell Formatted to show the trailing zeroes?

 

[ArnMan]

Re: Need to keep trailing any trailing zeros in formula in vba

The numbers are actually generated on my main sheet then copied over using pastespecial and after that they are copied to another sheet, with interior color and borders removed. So on the main sheet sheet1 and sheet 2 I have 18.0000
And when you go to Format Cell, it shows 4 decimal places.

 

[Rick Rothstein]

Re: Need to keep trailing any trailing zeros in formula in vba

With ACCws.Range("I" & i)
            .Borders.LineStyle = xlNone
            .Interior.Color = xlNone
            .HorizontalAlignment = xlCenter
            [B][COLOR="#FF0000"].Value = "=""±"" & ('Sheet1'!M" & i & ")" & "&" & "('Sheet2'!E" & i & ")"[/COLOR][/B]
        End With

See if replacing the highlighted line of code above with this works...

[table="width: 500"]
[tr]
	[td].Value = Evaluate("""±""&TEXT(Sheet1!M" & i & ",""" & Sheets("Sheet1").cells(i,"M").NumberFormat & """)&Sheet2!E" & i)[/td]
[/tr]
[/table]

 

[ArnMan]

Re: Need to keep trailing any trailing zeros in formula in vba

See if replacing the highlighted line of code above with this works...

[TABLE="width: 500"]
<tbody>[TR]
[TD].Value = Evaluate("""±""&TEXT(Sheet1!M" & i & ",""" & Sheets("Sheet1").cells(i,"M").NumberFormat & """)&Sheet2!E" & i)[/TD]
[/TR]
</tbody>[/TABLE]

Thank you Mr. Rick, your code always works. Unfornuately I needed to make it change if something changes on Sheet1. Since the vba runs everything at runtime, it only does it once. So what I've done was on my Main sheet where I create each row through input. When I click my Add row button, after everything is filled out, which includes number of decimals, it adds that number off to the side in another column. Then when I click my add to sheet1 button, the button copies everything over to Sheet1 and sheet2 and also adds off to the side outside of the print area, the number for decimal places. And linked my .Value to that number so if I change it it changes the number format in Sheet2 I column

 With ACCws.Range("I" & i)
            .Borders.LineStyle = xlNone
            .Interior.Color = xlNone
            .HorizontalAlignment = xlCenter
            [B][COLOR=#ff0000].Value = "=""±""&FIXED('Sheet1'!M" & i & "," & "('Sheet2'!M" & i & ")" & ")&"" ""& ('Sheet2'!E" & i & ")"[/COLOR][/B]
            
        End With

But I thank you very much, you have been very helpful. I think I can use the code snippit you gave in another part of my every growing project, Imma save it when I do need it.