PDA

View Full Version : [SOLVED] Format decimal places based on columns from same row



deadstick
04-10-2019, 06:44 AM
Hello all!

I have been searching everywhere but cannot find a solution to my problem. It should be simple, but maybe I am just missing it.:banghead:

I have three columns that contain the Dimension (D), Max tolerance (E), and Min tolerance (F) from a technical drawing. In the same row, there are five more columns for measured data. All fields are user entry. The user will format the number of decimal places in Columns D - F based on what is called out on the print. I want a way to automatically format the data fields (Columns G - K) to one more decimal place than the highest in Columns D - F.

I have the following code that works row by row, but can't figure out a way to make it work for all rows (rows can be added and deleted).


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range
For Each cell In Range("G2:K2")
If IsNumeric(cell.Value) And cell.Value <> "" Then
If Range("D2").NumberFormat = "0.0000" Or _
Range("E2").NumberFormat = "0.0000" Or _
Range("F2").NumberFormat = "0.0000" Then
cell.NumberFormat = "0.00000"
ElseIf Range("D2").NumberFormat = "0.000" Or _
Range("E2").NumberFormat = "0.000" Or _
Range("F2").NumberFormat = "0.000" Then
cell.NumberFormat = "0.0000"
ElseIf Range("D2").NumberFormat = "0.00" Or _
Range("E2").NumberFormat = "0.00" Or _
Range("F2").NumberFormat = "0.00" Then
cell.NumberFormat = "0.000"
ElseIf Range("D2").NumberFormat = "0.0" Or _
Range("E2").NumberFormat = "0.0" Or _
Range("F2").NumberFormat = "0.0" Then
cell.NumberFormat = "0.00"
ElseIf Range("D2").NumberFormat = "0" Or _
Range("E2").NumberFormat = "0" Or _
Range("F2").NumberFormat = "0" Then
cell.NumberFormat = "0.0"
End If
End If
Next cell
For Each cell In Range("G3:K3")
If IsNumeric(cell.Value) And cell.Value <> "" Then
If Range("D3").NumberFormat = "0.0000" Or _
Range("E3").NumberFormat = "0.0000" Or _
Range("F3").NumberFormat = "0.0000" Then
cell.NumberFormat = "0.00000"
ElseIf Range("D3").NumberFormat = "0.000" Or _
Range("E3").NumberFormat = "0.000" Or _
Range("F3").NumberFormat = "0.000" Then
cell.NumberFormat = "0.0000"
ElseIf Range("D3").NumberFormat = "0.00" Or _
Range("E3").NumberFormat = "0.00" Or _
Range("F3").NumberFormat = "0.00" Then
cell.NumberFormat = "0.000"
ElseIf Range("D3").NumberFormat = "0.0" Or _
Range("E3").NumberFormat = "0.0" Or _
Range("F3").NumberFormat = "0.0" Then
cell.NumberFormat = "0.00"
ElseIf Range("D3").NumberFormat = "0" Or _
Range("E3").NumberFormat = "0" Or _
Range("F3").NumberFormat = "0" Then
cell.NumberFormat = "0.0"
End If
End If
Next cell
End Sub

I tried creating dynamic named ranges and placing that in my code, but that did not seem to work. I have also attached a sample file.

Any help on this is much appreciated!!

Bob Phillips
04-10-2019, 08:01 AM
What are you envisaging as triggering this action. Selecting a cell seems overkill for all columns, all rows, and the change event is not triggered by a change to the numberformat.

Kenneth Hobs
04-10-2019, 08:33 AM
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
FormatCols Target
End Sub


Private Sub FormatCols(Target As Range)
Dim c As Range, ir As Range, r As Range, rn As Long
Dim a(1 To 3), n As Integer

Set ir = Intersect(Target, Range("D2:L" & ActiveSheet.UsedRange.Rows.Count))
If ir Is Nothing Then Exit Sub
If ir.Row = 1 Then Exit Sub

For Each r In ir.Rows
rn = r.Row
For Each c In Range("G" & rn & ":L" & rn)
a(1) = Len(Cells(rn, "D").NumberFormat) - 2
a(2) = Len(Cells(rn, "E").NumberFormat) - 2
a(3) = Len(Cells(rn, "F").NumberFormat) - 2
n = WorksheetFunction.Min(a)
If IsNumeric(c) And c <> "" Then
If n < 0 Then
c.NumberFormat = "0"
Else
c.NumberFormat = "0." & String(n + 1, "0")
End If
End If
Next c
Next r
End Sub

deadstick
04-10-2019, 10:08 AM
What are you envisaging as triggering this action. Selecting a cell seems overkill for all columns, all rows, and the change event is not triggered by a change to the numberformat.

I would rather not have a button for the user to click, but if that is more efficient, code-wise, maybe I should.

Any thoughts?

deadstick
04-10-2019, 10:12 AM
Thanks for the code, Kenneth! But I believe I may be missing something. I placed the SelectionChange in the Worksheet and FormatCols in a module but get the following error:
Compile error: Sub or Function not defined.

I appreciate your help!

Kenneth Hobs
04-10-2019, 10:52 AM
Private is for the sheet. It replaces your Selection routine. Rename your routine and then place that code in that sheet. Or, right click the sheet's tab, View Code, and paste.

If you want to go with a button, you would just call the FormatCols() routine with Selection rather than Target range. In that case, FormatCols() could be in a Module or in the sheet. Depends on which command button control type is that you use.

rothstein
04-10-2019, 11:24 AM
I am confused by this sentence from your original message (in particular, the part I highlighted in red) as it relates to the code you posted...


The user will format the number of decimal places in Columns D - F based on what is called out on the print. I want a way to automatically format the data fields (Columns G - K) to one more decimal place than the highest in Columns D - F.
Does this mean you want to find out the number format with the most trailing zeros in Columns D:F and apply that format to every number within Columns G:K? I ask because that is not what your code is attempting to do.

Kenneth Hobs
04-10-2019, 12:00 PM
That was confusing to me too Rick.

I used Min in my routine as his If() code did. One can change Min to Max in my code if the red part (most number of decimal places) was the actual goal.

Math-wise, I normally go with the minimum for degrees of accuracy.

deadstick
04-10-2019, 12:26 PM
Thanks for the perfect solution, Kenneth!

The only things I needed to tweak in your code was changing Min to Max, as you already stated, and I added .0 to the the If statement where n < 0 so that an integer in the spec columns would report with one decimal place.

Rick and Kenneth, sorry that sentence was confusing. Yes, I meant that the data would need to report one decimal place higher than the maximum precision in the spec. For example, Spec is .273" ± .0005", data reported would need to be .27350".

Again, thanks so much!