Need help completing Code

[ericpny2]

I have a code almost finished but i need a couple things touched up
This code is basically a bunch of if statement to determine the type of concrete to be used for a waterline

the class is dependent on the diameter of the pipe and the cover of dirt over the pipe

for example

if the diameter = 12 "
If cover is 14" or less, concrete is class III
if cover is between 15 and 19, class is IV
if cover is between 20 and 29, class is V
cover over 29 inches requires special design

if diameter = 15"....and so on

it is always a class III if the cover is 14 or less no matter what the diameter is.

there are many pipes that need to be classififed. the diameter is in column J,
the cover is column K, and the class will be put in column M. the data starts on row 12

this basically needs to run a certain amount of time which is determined by the user. i created a for loop that will run as many times as desired

please take a look at this and let me know why it wont run

when i run it, there is an error when defining what the diameter and cover is, so this is proabably something wrong with that


Code:


Sub RCPCLASS2()
Dim diameter As Double, cover As Double, Class As String, row As Integer


Dim ntimes As Long, i As Long
ntimes = InputBox("How many times would you like it to run")

For i = 1 To ntimes
diameter = ws.Cells(11 + i, 10)

cover = ws.Cells(11 + i, 11)

If cover <= 14 Then
Class = "III"
Else



If diameter = 12 Then
If cover <= 19 Then
Class = "IV"
ElseIf cover >= 19 And cover < 29 Then
Class = "V"
Else
Class = "Special"
End If

End If
End If


If diameter = 15 Then
If cover <= 19 Then
Class = "IV"
ElseIf cover >= 19 And cover < 29 Then
Class = "V"
Else
Class = "Special"
End If
End If


If diameter = 18 Then
If cover <= 20 Then
Class = "IV"
ElseIf cover >= 19 And cover < 29 Then
Class = "V"
Else
Class = "Special"
End If
End If








Class = ws.Cells(11 + i, 13)

Next i




End Sub

 

[jbeaucaire]

Like so, perhaps. You were using a ws variable that had not been defined, so I added it in...reference to the Activesheet.

Option Explicit

Sub RCPCLASS2()
Dim diameter As Double, cover As Double
Dim ws As Worksheet, Cls As String
Dim nTimes As Long, i As Long

nTimes = Application.InputBox("How many times would you like it to run", "Cycles", 5, Type:=1)
If nTimes = 0 Then Exit Sub
Set ws = ActiveSheet

For i = 1 To nTimes
    diameter = ws.Cells(11 + i, 10)
    cover = ws.Cells(11 + i, 11)

    If cover <= 14 Then
        Cls = "III"
    Else
        Select Case diameter
            Case 12, 15
                Select Case cover
                    Case 15 To 19
                        Cls = "IV"
                    Case 20 To 28
                        Cls = "V"
                    Case Else
                        Cls = "Special"
                End Select
            Case 18
                Select Case cover
                    Case 15 To 20
                        Cls = "IV"
                    Case 21 To 29
                        Cls = "V"
                    Case Else
                        Cls = "Special"
                End Select
        End Select
    End If
    
    ws.Cells(11 + i, 13) = Cls
Next i

End Sub

 

[ericpny2]

thank you that worked perfectly.

one more thing...
what should i add to the code to make it return nothing in the class cell if that row doesnt have and numbers in it for diameter and cover?

at some points i have a break in data and an empty row

right now it is inputing a class III on the blank rows

thanks

 

[jbeaucaire]

Maybe:

Option Explicit

Sub RCPCLASS2()
Dim diameter As Double, cover As Double
Dim ws As Worksheet, Cls As String
Dim nTimes As Long, i As Long

nTimes = Application.InputBox("How many times would you like it to run", "Cycles", 5, Type:=1)
If nTimes = 0 Then Exit Sub
Set ws = ActiveSheet

For i = 1 To nTimes
    diameter = ws.Cells(11 + i, 10)
    cover = ws.Cells(11 + i, 11)

    If Len(cover) = 0 Or Len(diameter) = 0 Then
        Cls = ""
    ElseIf cover <= 14 Then
        Cls = "III"
    Else
        Select Case diameter
            Case 12, 15
                Select Case cover
                    Case 15 To 19
                        Cls = "IV"
                    Case 20 To 28
                        Cls = "V"
                    Case Else
                        Cls = "Special"
                End Select
            Case 18
                Select Case cover
                    Case 15 To 20
                        Cls = "IV"
                    Case 21 To 29
                        Cls = "V"
                    Case Else
                        Cls = "Special"
                End Select
        End Select
    End If
    
    ws.Cells(11 + i, 13) = Cls
Next i

End Sub

 

[ericpny2]

That is what i originally tried but the cells dont actually have a "0" in them. They are blank. Running a code like that will still input a "III" in the blank rows