Sviluppare funzionalita su Microsoft Office con VBA Ciclo For Next con Formule

LoginRegistrati
Stai vedendo 5 articoli - dal 1 a 5 (di 5 totali)
  • Autore
    Articoli
  • #28167 Risposta

    Margherita Gobbi

      Buongiorno a tutti,

      ho iniziato da poco ad usare Excel VBA e sto incontrando alcune difficoltà nell'applicazione delle formule con il ciclo For Next.

      Vi presento il mio problema: 

      Ho un file costituito da 1 colonna e 133 righe (lavoro quindi sull'intervallo A1:A133) nel quale sono riportati dei valori numerici. Nelle celle che vanno da B1 a B133 devo applicare la seguente formula: 

      B1= SE(A1=-1000;0;(CONTA.PIÙ.SE(A1:A133;"<>-1000”;A1:A133;”<“&A1)+CONTA.SE(A1:A133;”=“&A1)/2)/CONTA.SE(A1:A133;"<>-1000"))

      L intervallo A1:A133 deve rimanere fisso, mentre il valore delle altre celle deve scorrere da A1 a A133 mano a mano che da B1 si passa a B2, a B3 e cosi via.

      Sto cercando di tradurre questa formula  con un codice for next e scrivo quanto ho fatto finora: 

      Sub prova()

      Dim i As Long

      For i = 2 To 133

      If Cells(i, 1) = "-1000" Then

      Cells(i, 2) = "0"

      Else

      Cells(i, 2).Value = Application.WorksheetFunction.CountIfs(Range(Cells(i , 2), Cells(i + 132 , 2)), "<>-1000", Range(Cells(i , 2), Cells(i + 132 , 2)), "<" & Cells(i, 2).Value)

      End If

      Next i

      End Sub

       

      La formula fin qui scritta non è corretta e ho due problemi: 

      1) non so come definire  l’intervallo con il contatore i in modo che rimanga fisso. Non posso utilizzare riferimenti assoluti perché questa formula poi sarà integrata in un altro Ciclo For Next 

      2) Nella seconda condizione della formula iffs non so come scrivere correttamente la seconda condizione.

      Spero di essere stata chiara e ringrazio molto per la disponibilità.

      Margherita

      #28174 Risposta

      GiuseppeMN
      Partecipante
        10 pts

        Buon pomeriggio Margherita; senza poter condurre dei Test significativi, posso solo suggerirti di provare a utilizzare la Funzione "FormulaLocal".

        FormulaLocal consente di utilizzare in VBA la stessa strurra della Formula Excel.

        Se vuoi un aiuto più concreto, allega un File (senza Dati sensibili) con alcuni Record significativi già inseriti e chiarisci ciò che desideri ottenere.

        Giuseppe

        #28175 Risposta
        PMC77
        PMC77
        Partecipante
          8 pts

          Ciao @Margherita Gobbi!

          Un saluto all'onnipresente @giuseppemn!

          Concordo con quest'ultimo sul fatto che sarebbe molto più facile con un file di esempio!

          Quello che posso dirti a prescindere è che se devi implementare il ciclo in un'altra funzione o altro cilco ti conviene essere più mirata sul Foglio su cui vuoi andare ad operare e quindi non scrivere Cells(i,1), ma dichiarare il foglio (WorkSheet) e riferirti alle celle con Shxxx.Range("A", 1).Value

          Attendo notizie (anzi, file)!

          Buona serata a tutti!

          Paolo

          #28181 Risposta
          scossa
          scossa

            Ciao,

            lato celle, per la formula, dovresti correggere così:

            in B1:

            = SE(A1=-1000;0;(CONTA.PIÙ.SE($A$1:$A$133;"<>-1000";$A$1:$A$133;"<"&A1)+CONTA.SE($A$1:$A$133;"="&A1)/2)/CONTA.SE($A$1:$A$133;"<>-1000"))

            da trascinare poi nelle righe sotto.

            #28182 Risposta
            scossa
            scossa

              Se invece vuoi usare il VBA puoi scrivere un codice che utilizza un ciclo For ... Next (più lento) oppure senza ciclo (più veloce).

              Per entrambi puoi scegliere se scrivere in B la formula oppure direttamente il risultato:

              Sub prova_con_ciclo_for()
                Dim sFormula As String, sAddr As String
                Dim rng As Range, rCell As Range
                
                Set rng = Foglio1.Range("A1:A133")
                sAddr = rng.Address
                For Each rCell In rng
                  sFormula = "=IF(" & rCell.Address & "=-1000,0,(COUNTIFS(" & sAddr & ",""<>-1000""," & sAddr & ",""<""&" & rCell.Address & ")+COUNTIF(" & sAddr & ",""=""&" & rCell.Address & ")/2)/COUNTIF(" & sAddr & ",""<>-1000""))"
                  
                  ''scrive nella cella B il risultato della formula
                  'rCell.Offset(0, 1).Value = Application.Evaluate(sFormula)
                  
                  ''scrive nella cella B la formula
                  rCell.Offset(0, 1).Formula = sFormula
                  
                Next
                Set rng = Nothing
              End Sub
              

              Senza ciclo:

              Sub prova_senza_ciclo_for()
                Dim sFormula As String
                Dim rng As Range
                
                Set rng = Foglio1.Range("B1:B133")
                sFormula = "= IF(A1=-1000,0,(COUNTIFS($A$1:$A$133,""<>-1000"",$A$1:$A$133,""<""&A1)+COUNTIF($A$1:$A$133,""=""&A1)/2)/COUNTIF($A$1:$A$133,""<>-1000""))"
                rng.Formula = sFormula
                '' scommentare le righe sotto per sostituire le formule coi valori
                'rng.Copy
                'rng.PasteSpecial (xlPasteValues)
                'Application.CutCopyMode = False
                'rng(1, 1).Select
                Set rng = Nothing
              End Sub
              
            LoginRegistrati
            Stai vedendo 5 articoli - dal 1 a 5 (di 5 totali)
            Rispondi a: Ciclo For Next con Formule
            Gli allegati sono permessi solo ad utenti REGISTRATI
            Le tue informazioni:



            vecchio frac - 2750 risposte

            albatros54
            albatros54 - 1022 risposte

            patel
            patel - 1000 risposte

            Marius44
            Marius44 - 874 risposte

            Luca73
            Luca73 - 734 risposte