jeudi 26 octobre 2017

VBA - elseif statement skipping "ElseIf"

In the following code, the code works, but skips the "else if" line: ElseIf y = "BAS" then "Basingstoke".

Since Basingstoke follows Belfast in the array from which the Ys come, it retains "Belfast" as the "Region" value. It works fine, but simply skips the ElseIf line entirely and goes to the "end if" immediately after.

I have a strong feeling I'm missing something obvious here...

For x = MinDate To MaxDate
With salesr
    Set FindRng = .Find(What:=x, _
                            LookIn:=xlValues, _
                            Lookat:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                                If Not FindRng Is Nothing Then
                                RowNumber = FindRng.Row
                                FindRng.Offset(, 2).Resize(1, 39).ClearContents
                                    For Each y In RegionsArray
                                     With Regions
                                          Set FindRng2 = .Find(What:=y, _
                                                                    LookIn:=xlValues, _
                                                                    Lookat:=xlWhole, _
                                                                    SearchOrder:=xlByColumns, _
                                                                    SearchDirection:=xlNext, _
                                                                    MatchCase:=False)
                                                                      If Not FindRng2 Is Nothing Then
                                                                          ColLeft = FindRng2.Column
                                                                          ColRight = FindRng2.Column + 1
                                                                          result = Application.WorksheetFunction.SumIfs(Import.Range("S:S"), Import.Range("M:M"), x, Import.Range("O:O"), y)
                                                                          result1 = Application.WorksheetFunction.SumIfs(Import.Range("T:T"), Import.Range("M:M"), x, Import.Range("O:O"), y)
                                                                            If result > 0 Then
                                                                                sales.Cells(RowNumber, ColLeft).Value = result
                                                                            Else
                                                                            End If
                                                                          sales.Cells(RowNumber, ColRight + 1).Value = result1
                                                                                If y = "BLF" Or y = "BAS" Then
                                                                                        If y = "BLF" Then Region = "Belfast"
                                                                                        ElseIf y = "BAS" Then Region = "Basingstoke"
                                                                                        End If
                                                                                If y <> "BLF" And y <> "BAS" Then
                                                                                Region = y
                                                                                End If
                                                                      End If
                                                                            sales.Cells(RowNumber, ColRight).Formula = "=SUMIFS(Table_PTQV02_PTQSOFT_SALES_ORDERS_Index3[Sales_day],Table_PTQV02_PTQSOFT_SALES_ORDERS_Index3[SALES_DATE],""" & x & """,Table_PTQV02_PTQSOFT_SALES_ORDERS_Index3[REGION_NAME],""*" & Region & "*"")"

                                      End With
                                    Next y

                                Else
                                End If
End With

Next x

Aucun commentaire:

Enregistrer un commentaire