Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 'LA SINTASSI DELLA STRINGA DA SPLITTARE E' &Colonna1&Colonna2&Colonna3 ecc...
- Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
- On Error GoTo ErrorHandler
- Dim lastAction As String
- Dim cella As String
- Dim ColumnArray() As String
- Dim row_number, i As Integer
- Dim ws As Worksheet
- Dim tbl As ListObject
- Dim saldoBonus As Integer
- #If Mac Then
- MsgBox ("Scarica la versione per mac perfavore")
- #Else
- If Application.CommandBars("Standard").FindControl(ID:=128, recursive:=True).Enabled = True Then
- If (ActiveCell.Column = 5) Then
- lastAction = Application.CommandBars("Standard").FindControl(ID:=128, recursive:=True).List(1)
- If lastAction = Replace(Application.CommandBars("Standard").FindControl(ID:=6002).Caption, "&", "") Then
- 'ATTENZIONE! NOME FOGLIO "Profit"
- Worksheets("LISTA SCOMMESSE").Activate
- row_number = ActiveCell.Row
- cella = ActiveCell.Value
- ColumnArray = Split(cella, "&")
- Set ws = ActiveSheet 'aggiungo una riga in caso di punta punta'
- Set tbl = ws.ListObjects("LISTABETS")
- If (Left(cella, 1) = "&") Then 'Il primo carattere deve sempre essere una & commerciale per evitare falsi positiv
- If (ColumnArray(1) = "M") Then 'Multipla'
- If (ColumnArray(4) = "BR") Then
- saldoBonus = 1
- Else
- saldoBonus = 0
- End If
- tbl.ListRows.Add
- tbl.ListRows.Add
- If (ColumnArray(3) = 4) Then
- tbl.ListRows.Add
- End If
- If (ColumnArray(3) = 5) Then
- tbl.ListRows.Add
- tbl.ListRows.Add
- End If
- For i = 0 To ColumnArray(3) - 1
- cella = ColumnArray(23)
- Cells(row_number + i, 5).Value = cella 'copio la data
- Cells(row_number + i, 7).Value = "Scommesse"
- Cells(row_number + i, 12).Value = "P-B"
- cella = ColumnArray(5 + 6 * i)
- Cells(row_number + i, 21).Value = cella 'data evento'
- cella = ColumnArray(6 + 6 * i)
- Cells(row_number + i, 17).Value = cella 'descrizione evento'
- cella = ColumnArray(7 + 6 * i)
- Cells(row_number + i, 18).Value = cella 'Puntata A'
- cella = ColumnArray(8 + 6 * i)
- Cells(row_number + i, 22).Value = cella 'quota 1'
- cella = ColumnArray(9 + 6 * i)
- Cells(row_number + i, 25).Value = cella 'quota 2'
- cella = ColumnArray(10 + 6 * i)
- Cells(row_number + i, 14).Value = cella 'scrivo Commisione'
- Cells(row_number + i, 19).Value = "SI"
- Next i
- Cells(row_number, 20).Value = 1
- Cells(row_number + 1, 20).Value = 2
- Cells(row_number + 2, 20).Value = 3
- If (ColumnArray(3) >= 4) Then
- Cells(row_number + 3, 20).Value = 4
- If (ColumnArray(3) = 5) Then
- Cells(row_number + 4, 20).Value = 5
- End If
- End If
- Cells(row_number + ColumnArray(3) - 1, 26 + saldoBonus).Value = ColumnArray(2)
- Else
- If (ColumnArray(4) = "P-P") Then
- tbl.ListRows.Add
- End If
- If (ColumnArray(15) = "BR") Then
- saldoBonus = 1
- Else
- saldoBonus = 0
- End If
- If (ColumnArray(4) = "P-P") Then
- cella = ColumnArray(1)
- Cells(row_number, 5).Value = cella 'copio la data
- Cells(row_number + 1, 5).Value = cella 'copio la data
- cella = ColumnArray(2)
- Cells(row_number, 7).Value = cella 'Scommesse'
- Cells(row_number + 1, 7).Value = cella 'Scommesse'
- cella = ColumnArray(3)
- Cells(row_number, 11).Value = cella 'Book1'
- cella = ColumnArray(4)
- Cells(row_number, 12).Value = cella 'scrivo P-P'
- Cells(row_number + 1, 12).Value = cella 'scrivo P-P'
- cella = ColumnArray(5)
- Cells(row_number, 17).Value = cella 'descrizione evento'
- Cells(row_number + 1, 17).Value = cella 'descrizione evento'
- cella = ColumnArray(6)
- Cells(row_number, 18).Value = cella 'Puntata A'
- cella = ColumnArray(7)
- Cells(row_number + 1, 11).Value = cella 'book 2'
- cella = ColumnArray(8)
- Cells(row_number, 19).Value = cella 'multipla'
- Cells(row_number + 1, 19).Value = cella 'multipla'
- cella = ColumnArray(9)
- Cells(row_number, 21).Value = cella 'data evento'
- Cells(row_number + 1, 21).Value = cella 'data evento'
- cella = ColumnArray(10)
- Cells(row_number, 22).Value = cella 'quota 1'
- cella = ColumnArray(11)
- Cells(row_number + 1, 22).Value = cella 'quota 2'
- cella = ColumnArray(12)
- Cells(row_number, 26 + saldoBonus).Value = cella 'Importo punta 1'
- cella = ColumnArray(13)
- Cells(row_number + 1, 26).Value = cella 'Importo punta 2'
- cella = ColumnArray(14)
- Cells(row_number + 1, 18).Value = cella 'Puntata B'
- Else
- cella = ColumnArray(1)
- Cells(row_number, 5).Value = cella 'copio la data
- cella = ColumnArray(2)
- Cells(row_number, 7).Value = cella 'Scommesse'
- cella = ColumnArray(3)
- Cells(row_number, 11).Value = cella 'Book1'
- cella = ColumnArray(4)
- Cells(row_number, 12).Value = cella 'scrivo P-B'
- cella = ColumnArray(7)
- Cells(row_number, 13).Value = cella 'scrivo Exchange'
- cella = ColumnArray(16)
- Cells(row_number, 14).Value = cella 'scrivo Commisione'
- cella = ColumnArray(5)
- Cells(row_number, 17).Value = cella 'descrizione evento'
- cella = ColumnArray(6)
- Cells(row_number, 18).Value = cella 'Puntata A'
- cella = ColumnArray(8)
- Cells(row_number, 19).Value = cella 'multipla'
- cella = ColumnArray(9)
- Cells(row_number, 21).Value = cella 'data evento'
- cella = ColumnArray(10)
- Cells(row_number, 22).Value = cella 'quota 1'
- cella = ColumnArray(11)
- Cells(row_number, 25).Value = cella 'quota 2'
- cella = ColumnArray(12)
- Cells(row_number, 26 + saldoBonus).Value = cella 'Importo punta 1'
- cella = ColumnArray(13)
- Cells(row_number, 28).Value = cella 'Importo punta 2'
- End If
- End If
- End If
- End If
- End If
- End If
- #End If
- Exit Sub
- ErrorHandler: ' Error-handling routine.
- End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement