MadMatLePsyke
New Member
- Joined
- Jan 3, 2023
- Messages
- 18
- Office Version
- 365
- Platform
- Windows
Good Afternoon all,
Have a runtime error 91 when I'm trying to obtain adresse1 value / 'PUTTING TIME STAMP section
I dont understand why, need help
Have a runtime error 91 when I'm trying to obtain adresse1 value / 'PUTTING TIME STAMP section
I dont understand why, need help
VBA Code:
Public wb As ThisWorkbook
Public WsP As Worksheet
Public WsTR As Worksheet
Public WsTI As Worksheet
Public WsM As Worksheet
Sub TRANSFERT()
Set wb = ThisWorkbook
Set WsP = Sheets("PARAMS")
Set WsTR = Sheets("TRANSFERT")
Set WsTI = Sheets("TICKER")
Set WsM = Sheets("MARKETS")
Dim R As Integer
Dim R1 As Byte
Dim C As Integer
Dim debut As String
Dim fin As String
Dim slices As String
Dim ouverture As String
Dim fermeture As String
Dim volume As String
Dim volume2 As Integer
Dim mtf As String
Dim avg As String
Dim place As String
Dim Rng1 As Range
Dim somme As Long
Dim adresse As Long
Dim adresse1 As Long
Dim adresse2 As Long
Application.ScreenUpdating = False
' DELETE WSTR
WsTR.Select
Range("A:PPA").Delete
' STRING DEFINITION
WsP.Select
debut = Format(Range("F11").Value, "hh:mm:ss")
fin = Format(Range("K11").Value, "hh:mm:ss")
slices = Range("P11").Value
ouverture = Range("F13").Value
fermeture = Range("K13").Value
volume = Range("P13").Value
mtf = Range("F15").Value
place = WsTI.Cells(1, 6).Value
'COPYPASTE AVG
If volume = "30D" Then
avg = "VOLUME_AVG_30D"
Else
If volume = "20D" Then
avg = "VOLUME_AVG_20D"
Else
If volume = "10D" Then
avg = "VOLUME_AVG_10D"
Else
End If
End If
End If
WsTI.Select
R = Application.WorksheetFunction.CountA(Range("A:A"))
C = ActiveSheet.UsedRange.Columns.Count
Range(Cells(3, 1), Cells(R, C)).Copy
WsTR.Select
Cells(1, 1).PasteSpecial xlValues
Cells(1, 1).PasteSpecial xlFormats
R = ActiveSheet.UsedRange.Rows.Count
For i = C To 4 Step -1
If Cells(1, i).Value = avg Then
Else
Columns(i).EntireColumn.Delete
End If
Next i
For i = R To 2 Step -1
If Cells(i, 4).Value = "" Or Cells(i, 4).Value = 0 Or Cells(i, 4).Value = "#N/A N/A" Then
Rows(i).EntireRow.Delete
Else
End If
Next i
R = ActiveSheet.UsedRange.Rows.Count
Set Rng1 = Range(Cells(2, 4), Cells(R, 4))
somme = WorksheetFunction.Sum(Rng1)
For i = 2 To R
Cells(i, 5).Value = Cells(i, 4) / somme
Next i
Cells(1, 5).Value = "PERCENTAGE"
Cells(1, 4).Copy
Cells(1, 5).PasteSpecial xlFormats
Range("E:E").Copy
Range("E:E").PasteSpecial xlValues
' REDEFINING VOLUME STRING
If volume = "30D" Then
volume = 30
Else
If volume = "20D" Then
volume = 20
Else
volume = 10
End If
End If
' PUTTING TIME STAMP
WsM.Select
Set trouver = [B:B].Find(place, LookAt:=xlPart)
adresse = trouver.Row
Set trouver = [R:R].Find(debut, LookAt:=xlPart)
adresse1 = trouver.Row
Set trouver = [R:R].Find(fin, LookAt:=xlPart)
adresse2 = trouver.Row
If ouverture = "Yes" Then
Cells(adresse, 3).Copy
WsTR.Select
Cells(R + 5, 1).PasteSpecial xlValues
Cells(R + 5, 1).PasteSpecial xlFormats
WsM.Select
Cells(adresse, 4).Copy
WsTR.Select
Cells(R + 5, 2).PasteSpecial xlValues
Cells(R + 5, 2).PasteSpecial xlFormats
Cells(R + 5, 2).Copy
Cells(R + 6, 1).PasteSpecial xlValues
Cells(R + 6, 1).PasteSpecial xlFormats
WsM.Select
Set Rng1 = Range(Cells(adresse1 + 1, 18), Cells(adresse2 - 1, 18))
For Each cel In Rng1
cel.Copy
WsTR.Select
R1 = Application.WorksheetFunction.CountA(Range("A:A"))
Cells(R1 + 5, 1).PasteSpecial xlValues
Cells(R1 + 5, 1).PasteSpecial xlFormats
WsM.Select
Next
WsM.Select
Set Rng1 = Range(Cells(adresse1 + 1, 18), Cells(adresse2, 18))
For Each cel In Rng1
cel.Copy
WsTR.Select
R1 = Application.WorksheetFunction.CountA(Range("B:B"))
Cells(R1 + 5, 2).PasteSpecial xlValues
Cells(R1 + 5, 2).PasteSpecial xlFormats
WsM.Select
Next
Else
Cells(adresse1, 18).Copy
WsTR.Select
Cells(R + 5, 1).PasteSpecial xlValues
Cells(R + 5, 1).PasteSpecial xlFormats
WsM.Select
Cells(adresse1 + 1, 18).Copy
WsTR.Select
Cells(R + 5, 2).PasteSpecial xlValues
Cells(R + 5, 2).PasteSpecial xlFormats
WsM.Select
Set Rng1 = Range(Cells(adresse1 + 1, 18), Cells(adresse2 - 1, 18))
For Each cel In Rng1
cel.Copy
WsTR.Select
R1 = Application.WorksheetFunction.CountA(Range("A:A"))
Cells(R1 + 5, 1).PasteSpecial xlValues
Cells(R1 + 5, 1).PasteSpecial xlFormats
WsM.Select
Next
WsM.Select
Set Rng1 = Range(Cells(adresse1 + 2, 18), Cells(adresse2, 18))
For Each cel In Rng1
cel.Copy
WsTR.Select
R1 = Application.WorksheetFunction.CountA(Range("B:B"))
Cells(R1 + 5, 2).PasteSpecial xlValues
Cells(R1 + 5, 2).PasteSpecial xlFormats
WsM.Select
Next
End If
WsM.Select
If fermeture = "Yes" Then
Range(Cells(adresse, 5), Cells(adresse, 6)).Copy
WsTR.Select
R1 = Application.WorksheetFunction.CountA(Range("A:A"))
Cells(R1 + 5, 1).PasteSpecial xlValues
Cells(R1 + 5, 1).PasteSpecial xlFormats
Else
End If
' PUTTING MTF
WsTR.Select
R1 = Application.WorksheetFunction.CountA(Range("C:C"))
volume2 = (volume * (R - 1) + 2)
For i = 3 To volume2
Range(Cells(2, 3), Cells(R, 3)).Copy
Cells(R + 4, i).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
i = i + R - 2
Next i
' PUTTING DATE
WsM.Select
j = 3
For i = 2 To volume + 1
Cells(i, 15).Copy
WsTR.Select
Cells(R + 3, j).PasteSpecial xlValues
Cells(R + 3, j).PasteSpecial xlFormats
j = j + R - 1
WsM.Select
Next i
WsP.Select
Cells(1, 1).Select
Application.ScreenUpdating = True
End Sub