CODEWITHSUNDEEP
vbaexcel
Clausthk
Clausthk

Reputation: 11

If statements inside for loop

I'm very new to VBA and what I'm trying to do here might be completely bonkers but please be gentle with me :-)

So a bit of background. I have a list of items(TLD's) and a pricelist. With the code below, I am trying to pull the price from the pricelist for each item if the name of the item, the product and the period is correct(i hope this makes some sense).

When i run it i get "Compile error: Else without If"

Sub add_prices()

    Dim startnumber As Long
    Dim endnumber As Long
    Dim TLD As String
    Dim Listtld As String

    endnumber = Sheets("Pricelist").Application.WorksheetFunction.CountF(Range("F2:F40000")) - 1

    For startnumber = 0 To endnumber

        TLD = Cells(3 + startnumber, 2)
        Listtld = Sheets("pricelist").Cells(2 + startnumber, 7)
        Product = Sheets("pricelist").Cells(2 + startnumber, 8)
        Period = Sheets("pricelist").Cells(2 + startnumber, 9)

        If TLD = Listtld Then
            If Product = "auto renewal" Then
                If Period = "1 year" Then
                    Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(2 + startnumber, 2)
                Else
                    If Period = "2 years" Then
                        Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(3 + startnumber, 2)
                    End If
                Else
                    If Period = "3 years" Then
                        Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(4 + startnumber, 2)
                    End If
                Else
                    If Period = "4 years" Then
                        Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(5 + startnumber, 2)
                    End If
                Else
                    If Period = "5 years" Then
                        Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(6 + startnumber, 2)
                    End If
                End If
            End If
        End If
    Next startnumber
End Sub

Any suggestions would be much appreciated

Upvotes: 1

Views: 57

Answers (4)

SJR
SJR

Reputation: 23081

I hesitate to add another solution, but you could shorten your code

Sub add_prices()

Dim startnumber As Long
Dim endnumber As Long
Dim TLD As String
Dim Listtld As String

endnumber = Sheets("Pricelist").Application.WorksheetFunction.CountF(Range("F2:F40000")) - 1

For startnumber = 0 To endnumber
    TLD = Cells(3 + startnumber, 2)
    Listtld = Sheets("pricelist").Cells(2 + startnumber, 7)
    Product = Sheets("pricelist").Cells(2 + startnumber, 8)
    Period = Sheets("pricelist").Cells(2 + startnumber, 9)

    If TLD = Listtld Then
        If Product = "auto renewal" Then
            Select Case Period
                Case "1 year", "2 years", "3 years", "4 years", "5 years"
                    Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(1 + startnumber + CLng(Left(Period, 1)), 2)
                Case Else
                    'perhaps nothing to do here
            End Select
        End If
    End If
Next startnumber

End Sub

Upvotes: 0

braX
braX

Reputation: 11755

Here it is fixed up:

Sub add_prices()
  Dim startnumber As Long
  Dim endnumber As Long
  Dim TLD As String
  Dim Listtld As String
  Dim Product As String
  Dim Period As String

  endnumber = Sheets("Pricelist").Application.WorksheetFunction.CountF(Range("F2:F40000")) - 1

  For startnumber = 0 To endnumber

  TLD = Cells(3 + startnumber, 2)
  Listtld = Sheets("pricelist").Cells(2 + startnumber, 7)
  Product = Sheets("pricelist").Cells(2 + startnumber, 8)
  Period = Sheets("pricelist").Cells(2 + startnumber, 9)

  If TLD = Listtld Then
      If Product = "auto renewal" Then
          If Period = "1 year" Then
              Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(2 + startnumber, 2)
          ElseIf Period = "2 years" Then
              Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(3 + startnumber, 2)
          ElseIf Period = "3 years" Then
              Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(4 + startnumber, 2)
          ElseIf Period = "4 years" Then
              Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(5 + startnumber, 2)
          ElseIf Period = "5 years" Then
              Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(6 + startnumber, 2)
          End If
      End If
  End If
  Next startnumber
End Sub

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33682

Definitely switch to Select Case, instead of mulitple Ifs:

Select Case Period
    Case "1 year"
        Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(2 + startnumber, 2)

    Case "2 years"
        Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(3 + startnumber, 2)

    Case "3 years"
        Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(4 + startnumber, 2)

    Case "4 years"
        Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(5 + startnumber, 2)

    Case "5 years"
        Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(6 + startnumber, 2)

End Select

Upvotes: 1

dwirony
dwirony

Reputation: 5450

Change those Elses to ElseIfs:

If TLD = Listtld Then
    If Product = "auto renewal" Then
        If Period = "1 year" Then
            Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(2 + startnumber, 2)
        ElseIf Period = "2 years" Then
            Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(3 + startnumber, 2)
        ElseIf Period = "3 years" Then
            Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(4 + startnumber, 2)
        ElseIf Period = "4 years" Then
            Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(5 + startnumber, 2)
        ElseIf Period = "5 years" Then
            Cells(3 + startnumber, 3).Value = Sheets("pricelist").Cells(6 + startnumber, 2)
        End If
    End If
End If

Upvotes: 2

Related Questions