Am un tabel in care urmaresc 5 indicatori, pentru fiecare tara in parte, iar cu ajutorul unui cod VBA am reusit sa populez pe coloane datele referitoare la target, rezultate pe 12 luni si progresul (procent). Pentru majoritatea idicatorilor folosesc aceeasi formula pentru a-mi returna pe coloana targetul, insa am un indicator la care nu vreau sa imi returneze targetul, ci numarul de participanti.
Cum as putea scrie codul, astfel incat sa ii arat ca pentru indicatorul x, vreau sa imi returneze datele utilizand o alta formula decat cea folosita pentru ceilalti indicatori? cum scriu "limitarea" asta?
Codul meu arata cam asa:
Cod: Selectaţi tot
Public cr As Range
Public ss As Range
Public sj As Range
Public dj As Range
Public ds As Range
Public NrL As Single
Public NrC As Single
Sub SelectareCool()
Set cr = ActiveCell.CurrentRegion
Set ss = cr.Cells(1, 1)
NrL = cr.Rows.Count
Set sj = cr.Cells(NrL, 1)
NrC = cr.Columns.Count
Set ds = cr.Cells(1, NrC)
Set dj = cr.Cells(NrL, NrC)
End Sub
Sub Bouton7_Cliquer()
Call SelectareCool
Dim target As Range
Set target = Range(cr.Cells(4, 3), dj)
Range(cr.Cells(4, 3), dj).Formula = "=SUMIF('[Liste formations source.xlsx]Source Trainings'!R2C2:R4502C2,RC1,'[Liste formations source.xlsx]Source Trainings'!R2C16:R4502C16)"
Range("C4").Select
Selection.AutoFill Destination:=Range("C4:C9"), Type:=xlFillDefault
Range("C4:C9").Select
Dim ian As Range
Set ian = Range(cr.Cells(4, 4), dj)
Range(cr.Cells(4, 4), dj).Formula = "=SUMIF('[Liste formations source.xlsx]Source Trainings'!R2C2:R4502C2,RC1,'[Liste formations source.xlsx]Source Trainings'!R2C17:R4502C17)"
Range("D4").Select
Selection.AutoFill Destination:=Range("D4:D9"), Type:=xlFillDefault
Range("D4:D9").Select
Dim feb As Range
Set feb = Range(cr.Cells(4, 5), dj)
Range(cr.Cells(4, 5), dj).Formula = "=SUMIF('[Liste formations source.xlsx]Source Trainings'!R2C2:R4502C2,RC1,'[Liste formations source.xlsx]Source Trainings'!R2C18:R4502C18)"
Range("E4").Select
Selection.AutoFill Destination:=Range("E4:E9"), Type:=xlFillDefault
Range("E4:E9").Select
Dim march As Range
Set march = Range(cr.Cells(4, 6), dj)
Range(cr.Cells(4, 6), dj).Formula = "=SUMIF('[Liste formations source.xlsx]Source Trainings'!R2C2:R4502C2,RC1,'[Liste formations source.xlsx]Source Trainings'!R2C19:R4502C19)"
Range("F4").Select
Selection.AutoFill Destination:=Range("F4:F9"), Type:=xlFillDefault
Range("F4:F9").Select
Dim apr As Range
Set apr = Range(cr.Cells(4, 7), dj)
Range(cr.Cells(4, 7), dj).Formula = "=SUMIF('[Liste formations source.xlsx]Source Trainings'!R2C2:R4502C2,RC1,'[Liste formations source.xlsx]Source Trainings'!R2C20:R4502C20)"
Range("G4").Select
Selection.AutoFill Destination:=Range("G4:G9"), Type:=xlFillDefault
Range("G4:G9").Select
Dim may As Range
Set may = Range(cr.Cells(4, 8), dj)
Range(cr.Cells(4, 8), dj).Formula = "=SUMIF('[Liste formations source.xlsx]Source Trainings'!R2C2:R4502C2,RC1,'[Liste formations source.xlsx]Source Trainings'!R2C21:R4502C21)"
Range("H4").Select
Selection.AutoFill Destination:=Range("H4:H9"), Type:=xlFillDefault
Range("H4:H9").Select
Dim jun As Range
Set jun = Range(cr.Cells(4, 9), dj)
Range(cr.Cells(4, 9), dj).Formula = "=SUMIF('[Liste formations source.xlsx]Source Trainings'!R2C2:R4502C2,RC1,'[Liste formations source.xlsx]Source Trainings'!R2C22:R4502C22)"
Range("I4").Select
Selection.AutoFill Destination:=Range("I4:I9"), Type:=xlFillDefault
Range("I4:I9").Select
Dim jul As Range
Set jul = Range(cr.Cells(4, 10), dj)
Range(cr.Cells(4, 10), dj).Formula = "=SUMIF('[Liste formations source.xlsx]Source Trainings'!R2C2:R4502C2,RC1,'[Liste formations source.xlsx]Source Trainings'!R2C23:R4502C23)"
Range("J4").Select
Selection.AutoFill Destination:=Range("J4:J9"), Type:=xlFillDefault
Range("J4:J9").Select
Dim aug As Range
Set aug = Range(cr.Cells(4, 11), dj)
Range(cr.Cells(4, 11), dj).Formula = "=SUMIF('[Liste formations source.xlsx]Source Trainings'!R2C2:R4502C2,RC1,'[Liste formations source.xlsx]Source Trainings'!R2C24:R4502C24)"
Range("K4").Select
Selection.AutoFill Destination:=Range("K4:K9"), Type:=xlFillDefault
Range("K4:K9").Select
Dim sept As Range
Set sept = Range(cr.Cells(4, 12), dj)
Range(cr.Cells(4, 12), dj).Formula = "=SUMIF('[Liste formations source.xlsx]Source Trainings'!R2C2:R4502C2,RC1,'[Liste formations source.xlsx]Source Trainings'!R2C25:R4502C25)"
Range("L4").Select
Selection.AutoFill Destination:=Range("L4:L9"), Type:=xlFillDefault
Range("L4:L9").Select
Dim oct As Range
Set oct = Range(cr.Cells(4, 13), dj)
Range(cr.Cells(4, 13), dj).Formula = "=SUMIF('[Liste formations source.xlsx]Source Trainings'!R2C2:R4502C2,RC1,'[Liste formations source.xlsx]Source Trainings'!R2C26:R4502C26)"
Range("M4").Select
Selection.AutoFill Destination:=Range("M4:M9"), Type:=xlFillDefault
Range("M4:M9").Select
Dim nov As Range
Set nov = Range(cr.Cells(4, 14), dj)
Range(cr.Cells(4, 14), dj).Formula = "=SUMIF('[Liste formations source.xlsx]Source Trainings'!R2C2:R4502C2,RC1,'[Liste formations source.xlsx]Source Trainings'!R2C27:R4502C27)"
Range("N4").Select
Selection.AutoFill Destination:=Range("N4:N9"), Type:=xlFillDefault
Range("N4:N9").Select
Dim dec As Range
Set dec = Range(cr.Cells(4, 15), dj)
Range(cr.Cells(4, 15), dj).Formula = "=SUMIF('[Liste formations source.xlsx]Source Trainings'!R2C2:R4502C2,RC1,'[Liste formations source.xlsx]Source Trainings'!R2C28:R4502C28)"
Range("O4").Select
Selection.AutoFill Destination:=Range("O4:O9"), Type:=xlFillDefault
Range("O4:O9").Select
Dim total As Range
Set total = Range(cr.Cells(10, 3), dj)
Range(cr.Cells(10, 3), dj).Formula = "=SUM(R[-6]C:R[-1]C)"
Range("C10").Select
Selection.AutoFill Destination:=Range("C10:O10"), Type:=xlFillDefault
Range("C10:O10").Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Font.Bold = True
Range("C25:O25").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Selection.Font.Bold = True
Dim horsfr As Range
Set horsfr = Range(cr.Cells(11, 3), dj)
Range(cr.Cells(11, 3), dj).Formula = "=SUM(R[-6]C:R[-2]C)"
Range("C11").Select
Selection.AutoFill Destination:=Range("C11:O11"), Type:=xlFillDefault
Range("C11:O11").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Selection.Font.Bold = True
Range("C25:P25").Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Selection.Font.Bold = True
Range("C26:P26").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Selection.Font.Bold = True
Dim kpi As Range
Set kpi = Range(cr.Cells(4, 16), dj)
Range(cr.Cells(4, 16), dj).Formula = "=IFERROR(SUM(RC[-12]:RC[-1])/RC[-13],0)"
Range("P4").Select
Selection.AutoFill Destination:=Range("P4:P11"), Type:=xlFillDefault
Range("P4:P11").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0.0%"
Range("P19:P26").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0.0%"
Range("P10").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Font.Bold = True
Range("P11").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Selection.Font.Bold = True
End Sub
si m-am gandit ca pentru indicatorul x sa adaug:
Dim peoplesuccessful As Range
Set peoplesuccessful = Range(cr.Cells(31, 3), dj)
Range(cr.Cells(31, 3), dj).Formula = "=SUMIFS('[Liste formations source.xlsx]Source Trainings'!R2C29:R4502C29,'[Liste formations source.xlsx]Source Trainings'!R2C8:R4502C8,RC[-1],'[Liste formations source.xlsx]Source Trainings'!R2C3:R4502C3,""Yes"",'[Liste formations source.xlsx]Source Trainings'!R2C15:R4502C15,""Number of people evaluated after F to F"")"
Range("C31").Select
Selection.AutoFill Destination:=Range("C31:C36"), Type:=xlFillDefault
Range("C31:C36").Select
Dar mi-a populat tot cu targetul si nu mi-a tinut cont de formula.
Multumesc!