Look Up Z Table Value, Problem with second if 0

[Bernieg]

The code below works to look up Z table value if the number is i.e. 1.258745
However if the number is 1.5 i have errors "Paul" is used to look up the Z column
"Peter" is used to find the value

Thanks in advance

Bernie

Get Z Table Value.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2		z	0.0000000	0.0100000	0.0200000	0.0300000	0.0400000	0.0500000	0.0600000	0.0700000	0.0800000	0.0900000		USL	1.52158966500
3		0.0000000	0.0000000	0.0040000	0.0080000	0.0120000	0.0160000	0.0199000	0.0239000	0.0279000	0.0319000	0.0359000		USL, 2 Decimal	1.52000000000
4		0.1000000	0.0398000	0.0438000	0.0478000	0.0517000	0.0557000	0.0596000	0.0636000	0.0675000	0.0714000	0.0753000
5		0.2000000	0.0793000	0.0832000	0.0871000	0.0910000	0.0948000	0.0987000	0.1026000	0.1064000	0.1103000	0.1141000		Paul ( Z Colum Ref )	1.50000000000
6		0.3000000	0.1179000	0.1217000	0.1255000	0.1293000	0.1331000	0.1368000	0.1406000	0.1443000	0.1480000	0.1517000		Peter ( Second Decimal )	2.00000000000
7		0.4000000	0.1554000	0.1591000	0.1628000	0.1664000	0.1700000	0.1736000	0.1772000	0.1808000	0.1844000	0.1879000
8		0.5000000	0.1915000	0.1950000	0.1985000	0.2019000	0.2054000	0.2088000	0.2123000	0.2157000	0.2190000	0.2224000		Z table Result	0.43570000000
9		0.6000000	0.2257000	0.2291000	0.2324000	0.2357000	0.2389000	0.2422000	0.2454000	0.2486000	0.2517000	0.2549000
10		0.7000000	0.2580000	0.2611000	0.2642000	0.2673000	0.2704000	0.2734000	0.2764000	0.2794000	0.2823000	0.2852000		Value can be changed
11		0.8000000	0.2881000	0.2910000	0.2939000	0.2967000	0.2995000	0.3023000	0.3051000	0.3078000	0.3106000	0.3133000
12		0.9000000	0.3159000	0.3186000	0.3212000	0.3238000	0.3264000	0.3289000	0.3315000	0.3340000	0.3365000	0.3389000
13		1.0000000	0.3413000	0.3438000	0.3461000	0.3485000	0.3508000	0.3531000	0.3554000	0.3577000	0.3599000	0.3621000
14		1.1000000	0.3643000	0.3665000	0.3686000	0.3708000	0.3729000	0.3749000	0.3770000	0.3790000	0.3810000	0.3830000
15		1.2000000	0.3849000	0.3869000	0.3888000	0.3907000	0.3925000	0.3944000	0.3962000	0.3980000	0.3997000	0.4015000
16		1.3000000	0.4032000	0.4049000	0.4066000	0.4082000	0.4099000	0.4115000	0.4131000	0.4147000	0.4162000	0.4177000
17		1.4000000	0.4192000	0.4207000	0.4222000	0.4236000	0.4251000	0.4265000	0.4279000	0.4292000	0.4306000	0.4319000
18		1.5000000	0.4332000	0.4345000	0.4357000	0.4370000	0.4382000	0.4394000	0.4406000	0.4418000	0.4429000	0.4441000
19		1.6000000	0.4452000	0.4463000	0.4474000	0.4484000	0.4495000	0.4505000	0.4515000	0.4525000	0.4535000	0.4545000
20		1.7000000	0.4554000	0.4564000	0.4573000	0.4582000	0.4591000	0.4599000	0.4608000	0.4616000	0.4625000	0.4633000
21		1.8000000	0.4641000	0.4649000	0.4656000	0.4664000	0.4671000	0.4678000	0.4686000	0.4693000	0.4699000	0.4706000
22		1.9000000	0.4713000	0.4719000	0.4726000	0.4732000	0.4738000	0.4744000	0.4750000	0.4756000	0.4761000	0.4767000
23		2.0000000	0.4772000	0.4778000	0.4783000	0.4788000	0.4793000	0.4798000	0.4803000	0.4808000	0.4812000	0.4817000
24		2.1000000	0.4821000	0.4826000	0.4830000	0.4834000	0.4838000	0.4842000	0.4846000	0.4850000	0.4854000	0.4857000
25		2.2000000	0.4861000	0.4864000	0.4868000	0.4871000	0.4875000	0.4878000	0.4881000	0.4884000	0.4887000	0.4890000
26		2.3000000	0.4893000	0.4896000	0.4898000	0.4901000	0.4904000	0.4906000	0.4909000	0.4911000	0.4913000	0.4916000
27		2.4000000	0.4918000	0.4920000	0.4922000	0.4925000	0.4927000	0.4929000	0.4931000	0.4932000	0.4934000	0.4936000
28		2.5000000	0.4938000	0.4940000	0.4941000	0.4943000	0.4945000	0.4946000	0.4948000	0.4949000	0.4951000	0.4952000
29		2.6000000	0.4953000	0.4955000	0.4956000	0.4957000	0.4959000	0.4960000	0.4961000	0.4962000	0.4963000	0.4964000
30		2.7000000	0.4965000	0.4966000	0.4967000	0.4968000	0.4969000	0.4970000	0.4971000	0.4972000	0.4973000	0.4974000
31		2.8000000	0.4974000	0.4975000	0.4976000	0.4977000	0.4977000	0.4978000	0.4979000	0.4979000	0.4980000	0.4981000
32		2.9000000	0.4981000	0.4982000	0.4982000	0.4983000	0.4984000	0.4984000	0.4985000	0.4985000	0.4986000	0.4986000
33		3.0000000	0.4987000	0.4987000	0.4987000	0.4988000	0.4988000	0.4989000	0.4989000	0.4989000	0.4990000	0.4990000
34		3.1000000	0.4990000	0.4991000	0.4991000	0.4991000	0.4992000	0.4992000	0.4992000	0.4992000	0.4993000	0.4993000
35		3.2000000	0.4993000	0.4993000	0.4994000	0.4994000	0.4994000	0.4994000	0.4994000	0.4995000	0.4995000	0.4995000
36		3.3000000	0.4995000	0.4995000	0.4995000	0.4996000	0.4996000	0.4996000	0.4996000	0.4996000	0.4996000	0.4997000
37		3.4000000	0.4997000	0.4997000	0.4997000	0.4997000	0.4997000	0.4997000	0.4997000	0.4997000	0.4997000	0.4998000
38		3.5000000	0.4998000	0.4998000	0.4998000	0.4998000	0.4998000	0.4998000	0.4998000	0.4998000	0.4998000	0.4998000
39		3.6000000	0.4998000	0.4998000	0.4999000	0.4999000	0.4999000	0.4999000	0.4999000	0.4999000	0.4999000	0.4999000
40		3.7000000	0.4999000	0.4999000	0.4999000	0.4999000	0.4999000	0.4999000	0.4999000	0.4999000	0.4999000	0.4999000
41		3.8000000	0.4999000	0.4999000	0.4999000	0.4999000	0.4999000	0.4999000	0.4999000	0.4999000	0.4999000	0.4990000
42
Z Table

Function LastCell(ws As Worksheet) As Range
  Dim LastRow, LastCol
' Error-handling is here in case there is not any
' data in the worksheet
  On Error Resume Next
  With ws
' Find the last real row
  LastRow = .Cells.Find(What:="*", _
  SearchDirection:=xlPrevious, _
  SearchOrder:=xlByRows).Row
' Find the last real column
  LastCol = .Cells.Find(What:="*", _
  SearchDirection:=xlPrevious, _
  SearchOrder:=xlByColumns).Column
  End With
' Finally, initialize a Range object variable for
' the last populated row.
  Set LastCell = ws.Cells(LastRow, LastCol)
  Cells(LastRow, LastCol).Select
 If IsEmpty(LastRow) Then
  MsgBox ("No data in worksheet")
  End
  End If
End Function
Sub GetZTableValue()

Worksheets("Z Table").Select
Range("O3:O8").Select
    Selection.ClearContents
    Range("a1").Select


USL = Worksheets("Z Table").Range("O2")
USL = Round(USL, 2)
Range("o3") = USL
'**********************************

'Get USL Z Table value
Dim Boby, Paul As Single
Paul = Mid(USL, 1, 3) ' i.e 1.5 ( Original No 1.52 )
Peter = Right(USL, 1) ' i.e 2 Second decimal

Range("o5") = Paul
Range("o6") = Peter

'Stop

rlc = LastCell(ActiveSheet).Address(False, False)
lr = ActiveCell.Row
lc = ActiveCell.Column

For r = 3 To lr '
If Not IsEmpty(Cells(r, 2)) And (Cells(r, 2)) = Paul Then
Dingo = Peter + 3
Boby = Cells(r, Dingo).Value
'bernie = (0.5 - Boby)
End If
Next r

'Stop

Range("o8") = Boby

'font turns white, don't know why
Range("O8").Select
With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0 'Black
End With
Range("a1").Select

End Sub

 

[DanteAmor]

Hi @Bernieg .
Thanks for posting on MrExcel board.

I think it would work if you change this line:

Peter = Right(USL, 1) ' i.e 2 Second decimal

For this

Peter = Right(Format(USL, "0.00"), 1) ' i.e 2 Second decimal

--------------------------------​

But I would like to give you the following reduced code.
Change all your macro to the following:
(It's a weird way to name variables, but I kept the same name in the variables so you're familiar)

Sub GetZTableValue()
  Dim Usl As Double, Paul As Double, Peter As Double
  Dim f As Range
 
  With Worksheets("Z Table")
    .Range("O3:O8").ClearContents
   
    Usl = Round(.Range("O2").Value, 2)
    Paul = Round(Usl, 1)
    Peter = Right(Format(Usl, "0.00"), 1)
   
    Set f = .Range("B:B").Find(Paul, , xlFormulas, xlWhole)
    If Not f Is Nothing Then
      .Range("O3").Value = Usl
      .Range("O5").Value = Paul
      .Range("O6").Value = Peter
      .Range("O8").Value = f.Offset(, Peter + 1)
    End If
  End With
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[Bernieg]

Hi @Bernieg .
Thanks for posting on MrExcel board.

I think it would work if you change this line:

Peter = Right(USL, 1) ' i.e 2 Second decimal

For this

Peter = Right(Format(USL, "0.00"), 1) ' i.e 2 Second decimal

--------------------------------​

But I would like to give you the following reduced code.
Change all your macro to the following:
(It's a weird way to name variables, but I kept the same name in the variables so you're familiar)

Sub GetZTableValue()
  Dim Usl As Double, Paul As Double, Peter As Double
  Dim f As Range
 
  With Worksheets("Z Table")
    .Range("O3:O8").ClearContents
  
    Usl = Round(.Range("O2").Value, 2)
    Paul = Round(Usl, 1)
    Peter = Right(Format(Usl, "0.00"), 1)
  
    Set f = .Range("B:B").Find(Paul, , xlFormulas, xlWhole)
    If Not f Is Nothing Then
      .Range("O3").Value = Usl
      .Range("O5").Value = Paul
      .Range("O6").Value = Peter
      .Range("O8").Value = f.Offset(, Peter + 1)
    End If
  End With
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Hi Dante
Thank you both solutions worked, your version is far more elegant though.
I don't program as much as I'd like to so will have to make a copy for referencing in the future.

Bernie

 

[Bernieg]

Hi Dante

Just spotted an issue, if a number is 1.588 "USL" rounds it to 2 decimal places i.e. 1.59
However "Paul" also rounds it up to 1.6.
"Peter then extracts the last digit of "USL" = 9
New figure becomes 1.69 we gained 0.1

I have added my original code and that works, is there a way to format " Paul " as you formatted "Peter"

Usl = Round(.Range("O2").Value, 2) ' = 1.59

'Paul = Round(Usl, 1) ' = 1.6
Paul = Mid(Usl, 1, 3) ' = 1.59

Peter = Right(Format(Usl, "0.00"), 1) ' = 9

Thanks for all your help
Bernie

 

[DanteAmor]

In my macro.

Change this:

Paul = Round(Usl, 1)

For this:

Paul = Mid(Usl, 1, 3)