Hi,
The macro code below works fine until it gets to the last line:
ElseIf Myval >= "9.501" And Myval <= "20.750" Then
Cells(i, 46).Value = "11.50"
If there is a value in column AS (column 45) that is between 9.501 and 20.750
this part of the macro does not put the value 11.50 in the cell in Column AT (column 46) - it is just blank.
Can anyone tell me please how to fix it - is it something to do with the 'Dim' values? This macro was created for me by someone else but he is on holiday and I am unable to contact him.
Thanks,
Paul.
Here is the macro:
-------------------------------------------------------------
Application.ScreenUpdating = False
Dim lrow As Long
Dim i As Integer
Dim Myval As Variant
lrow = Sheets("Sheet1").Range("AS65536").End(xlUp).Row
For i = 3 To lrow '' start in row 2 assume header in row 1
Myval = Cells(i, 45).Text
If Myval >= "0.00" And Myval <= "0.100" Then
Cells(i, 46).Value = ".94"
ElseIf Myval >= "0.101" And Myval <= "0.250" Then
Cells(i, 46).Value = "1.22"
ElseIf Myval >= "0.251" And Myval <= "0.500" Then
Cells(i, 46).Value = "1.35"
ElseIf Myval >= "0.501" And Myval <= "0.750" Then
Cells(i, 46).Value = "1.64"
ElseIf Myval >= "0.751" And Myval <= "1.000" Then
Cells(i, 46).Value = "1.97"
ElseIf Myval >= "1.001" And Myval <= "1.250" Then
Cells(i, 46).Value = "2.15"
ElseIf Myval >= "1.251" And Myval <= "1.500" Then
Cells(i, 46).Value = "2.45"
ElseIf Myval >= "1.501" And Myval <= "1.750" Then
Cells(i, 46).Value = "2.75"
ElseIf Myval >= "1.751" And Myval <= "2.000" Then
Cells(i, 46).Value = "3.05"
ElseIf Myval >= "2.001" And Myval <= "2.250" Then
Cells(i, 46).Value = "3.33"
ElseIf Myval >= "2.251" And Myval <= "2.500" Then
Cells(i, 46).Value = "3.61"
ElseIf Myval >= "2.501" And Myval <= "2.750" Then
Cells(i, 46).Value = "3.89"
ElseIf Myval >= "2.751" And Myval <= "3.000" Then
Cells(i, 46).Value = "4.17"
ElseIf Myval >= "3.001" And Myval <= "3.250" Then
Cells(i, 46).Value = "4.45"
ElseIf Myval >= "3.251" And Myval <= "3.500" Then
Cells(i, 46).Value = "4.73"
ElseIf Myval >= "3.501" And Myval <= "3.750" Then
Cells(i, 46).Value = "5.01"
ElseIf Myval >= "3.751" And Myval <= "4.000" Then
Cells(i, 46).Value = "5.29"
ElseIf Myval >= "4.001" And Myval <= "4.250" Then
Cells(i, 46).Value = "5.57"
ElseIf Myval >= "4.251" And Myval <= "4.500" Then
Cells(i, 46).Value = "5.85"
ElseIf Myval >= "4.501" And Myval <= "4.750" Then
Cells(i, 46).Value = "6.13"
ElseIf Myval >= "4.751" And Myval <= "5.000" Then
Cells(i, 46).Value = "6.41"
ElseIf Myval >= "5.001" And Myval <= "5.250" Then
Cells(i, 46).Value = "6.69"
ElseIf Myval >= "5.251" And Myval <= "5.500" Then
Cells(i, 46).Value = "6.97"
ElseIf Myval >= "5.501" And Myval <= "5.750" Then
Cells(i, 46).Value = "7.25"
ElseIf Myval >= "5.751" And Myval <= "6.000" Then
Cells(i, 46).Value = "7.53"
ElseIf Myval >= "6.001" And Myval <= "6.250" Then
Cells(i, 46).Value = "7.81"
ElseIf Myval >= "6.251" And Myval <= "6.500" Then
Cells(i, 46).Value = "8.09"
ElseIf Myval >= "6.501" And Myval <= "6.750" Then
Cells(i, 46).Value = "8.37"
ElseIf Myval >= "6.751" And Myval <= "7.000" Then
Cells(i, 46).Value = "8.65"
ElseIf Myval >= "7.001" And Myval <= "7.250" Then
Cells(i, 46).Value = "8.93"
ElseIf Myval >= "7.251" And Myval <= "7.500" Then
Cells(i, 46).Value = "9.21"
ElseIf Myval >= "7.501" And Myval <= "7.750" Then
Cells(i, 46).Value = "9.49"
ElseIf Myval >= "7.751" And Myval <= "8.000" Then
Cells(i, 46).Value = "9.77"
ElseIf Myval >= "8.001" And Myval <= "8.250" Then
Cells(i, 46).Value = "10.05"
ElseIf Myval >= "8.251" And Myval <= "8.500" Then
Cells(i, 46).Value = "10.33"
ElseIf Myval >= "8.501" And Myval <= "8.750" Then
Cells(i, 46).Value = "10.61"
ElseIf Myval >= "8.751" And Myval <= "9.000" Then
Cells(i, 46).Value = "10.89"
ElseIf Myval >= "9.001" And Myval <= "9.250" Then
Cells(i, 46).Value = "11.17"
ElseIf Myval >= "9.251" And Myval <= "9.500" Then
Cells(i, 46).Value = "11.45"
ElseIf Myval >= "9.501" And Myval <= "20.750" Then
Cells(i, 46).Value = "11.50"
End If
Next i
End Sub
----------------------------------------------------------------------------
The macro code below works fine until it gets to the last line:
ElseIf Myval >= "9.501" And Myval <= "20.750" Then
Cells(i, 46).Value = "11.50"
If there is a value in column AS (column 45) that is between 9.501 and 20.750
this part of the macro does not put the value 11.50 in the cell in Column AT (column 46) - it is just blank.
Can anyone tell me please how to fix it - is it something to do with the 'Dim' values? This macro was created for me by someone else but he is on holiday and I am unable to contact him.
Thanks,
Paul.
Here is the macro:
-------------------------------------------------------------
Application.ScreenUpdating = False
Dim lrow As Long
Dim i As Integer
Dim Myval As Variant
lrow = Sheets("Sheet1").Range("AS65536").End(xlUp).Row
For i = 3 To lrow '' start in row 2 assume header in row 1
Myval = Cells(i, 45).Text
If Myval >= "0.00" And Myval <= "0.100" Then
Cells(i, 46).Value = ".94"
ElseIf Myval >= "0.101" And Myval <= "0.250" Then
Cells(i, 46).Value = "1.22"
ElseIf Myval >= "0.251" And Myval <= "0.500" Then
Cells(i, 46).Value = "1.35"
ElseIf Myval >= "0.501" And Myval <= "0.750" Then
Cells(i, 46).Value = "1.64"
ElseIf Myval >= "0.751" And Myval <= "1.000" Then
Cells(i, 46).Value = "1.97"
ElseIf Myval >= "1.001" And Myval <= "1.250" Then
Cells(i, 46).Value = "2.15"
ElseIf Myval >= "1.251" And Myval <= "1.500" Then
Cells(i, 46).Value = "2.45"
ElseIf Myval >= "1.501" And Myval <= "1.750" Then
Cells(i, 46).Value = "2.75"
ElseIf Myval >= "1.751" And Myval <= "2.000" Then
Cells(i, 46).Value = "3.05"
ElseIf Myval >= "2.001" And Myval <= "2.250" Then
Cells(i, 46).Value = "3.33"
ElseIf Myval >= "2.251" And Myval <= "2.500" Then
Cells(i, 46).Value = "3.61"
ElseIf Myval >= "2.501" And Myval <= "2.750" Then
Cells(i, 46).Value = "3.89"
ElseIf Myval >= "2.751" And Myval <= "3.000" Then
Cells(i, 46).Value = "4.17"
ElseIf Myval >= "3.001" And Myval <= "3.250" Then
Cells(i, 46).Value = "4.45"
ElseIf Myval >= "3.251" And Myval <= "3.500" Then
Cells(i, 46).Value = "4.73"
ElseIf Myval >= "3.501" And Myval <= "3.750" Then
Cells(i, 46).Value = "5.01"
ElseIf Myval >= "3.751" And Myval <= "4.000" Then
Cells(i, 46).Value = "5.29"
ElseIf Myval >= "4.001" And Myval <= "4.250" Then
Cells(i, 46).Value = "5.57"
ElseIf Myval >= "4.251" And Myval <= "4.500" Then
Cells(i, 46).Value = "5.85"
ElseIf Myval >= "4.501" And Myval <= "4.750" Then
Cells(i, 46).Value = "6.13"
ElseIf Myval >= "4.751" And Myval <= "5.000" Then
Cells(i, 46).Value = "6.41"
ElseIf Myval >= "5.001" And Myval <= "5.250" Then
Cells(i, 46).Value = "6.69"
ElseIf Myval >= "5.251" And Myval <= "5.500" Then
Cells(i, 46).Value = "6.97"
ElseIf Myval >= "5.501" And Myval <= "5.750" Then
Cells(i, 46).Value = "7.25"
ElseIf Myval >= "5.751" And Myval <= "6.000" Then
Cells(i, 46).Value = "7.53"
ElseIf Myval >= "6.001" And Myval <= "6.250" Then
Cells(i, 46).Value = "7.81"
ElseIf Myval >= "6.251" And Myval <= "6.500" Then
Cells(i, 46).Value = "8.09"
ElseIf Myval >= "6.501" And Myval <= "6.750" Then
Cells(i, 46).Value = "8.37"
ElseIf Myval >= "6.751" And Myval <= "7.000" Then
Cells(i, 46).Value = "8.65"
ElseIf Myval >= "7.001" And Myval <= "7.250" Then
Cells(i, 46).Value = "8.93"
ElseIf Myval >= "7.251" And Myval <= "7.500" Then
Cells(i, 46).Value = "9.21"
ElseIf Myval >= "7.501" And Myval <= "7.750" Then
Cells(i, 46).Value = "9.49"
ElseIf Myval >= "7.751" And Myval <= "8.000" Then
Cells(i, 46).Value = "9.77"
ElseIf Myval >= "8.001" And Myval <= "8.250" Then
Cells(i, 46).Value = "10.05"
ElseIf Myval >= "8.251" And Myval <= "8.500" Then
Cells(i, 46).Value = "10.33"
ElseIf Myval >= "8.501" And Myval <= "8.750" Then
Cells(i, 46).Value = "10.61"
ElseIf Myval >= "8.751" And Myval <= "9.000" Then
Cells(i, 46).Value = "10.89"
ElseIf Myval >= "9.001" And Myval <= "9.250" Then
Cells(i, 46).Value = "11.17"
ElseIf Myval >= "9.251" And Myval <= "9.500" Then
Cells(i, 46).Value = "11.45"
ElseIf Myval >= "9.501" And Myval <= "20.750" Then
Cells(i, 46).Value = "11.50"
End If
Next i
End Sub
----------------------------------------------------------------------------