Scriere cod formule diferite VBA

Închis
Sima Alina
Mesaje: 13
Membru din: Joi Iun 07, 2018 2:15 pm

Scriere cod formule diferite VBA

Mesaj de Sima Alina » Mar Iun 12, 2018 2:26 pm

Buna,

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!

Sima Alina
Mesaje: 13
Membru din: Joi Iun 07, 2018 2:15 pm

Re: Scriere cod formule diferite VBA

Mesaj de Sima Alina » Mie Iun 13, 2018 10:40 am

Atasez si modelul fisierului meu.
Sper sa ma puteti lamuri, nu stiu cum ar trebui sa ii spun/unde ar trebui sa scriu fomula, a.i. pt indicator 2 sa imi calculeze diferit.
multumesc!
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

Închis

Înapoi la “Visual Basic for Application (VBA) cu Excel - Intrebari tehnice”