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