schimbare range in functie de conditie

[pedersen]

va rog sa ma ajutati cu o functie in cod vba care sa produca rezultatele ca in fisierul atasat.

[IPP]

Buna ziua

Atasat aveti spre testare o propunere folosind macro.
Obiectiv: inserarea unor formule countif in care inceputul range-ului este dat de prezenta unui criteriu pe coloana A.

Premise: codul ruleaza in concordanta cu structura din fisierul exemplu si informatia de pe coloana B nu e intrerupta (adica nu exista celule goale)

Am folosit urmatorul cod:

Cod: Selectaţi tot

Sub InsertFormulas()

'IPP - 12.09.2017

Dim myRow As Long
Dim myClm As Integer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("C3").Select
Range("C3:C" & Range("B1000000").End(xlUp).Row).ClearContents

Do Until Cells(ActiveCell.Row, 2) = ""
 If Cells(ActiveCell.Row, 1) <> "" Then
  myRow = ActiveCell.Row
  myClm = 2

  ActiveCell.FormulaR1C1 = "=countif(R" & myRow & "C" & myClm & ":RC[-1],RC[-1])"
 End If

ActiveCell.Offset(1, 0).Select
Loop

Range("C4").Select
 Do Until Cells(ActiveCell.Row, 2) = ""
  If ActiveCell = "" Then Selection.FillDown
 ActiveCell.Offset(1, 0).Select
 Loop
 
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Acesta, sterge eventuale formule de pe coloana C, insereaza formulele noi in dreptul informatiei existente pe coloana A si apoi copiaza restul formulelor

Pentru testare: deschideti fisierul atasat, activati macro/continutul, selectati Sheet1 si rulati macro

IP

PS. Din pacate nu pot face o functie UDF care sa se muleze pe aceste cerinte. Din ce stiu o functie, oricare ar fi ea, trebuie sa afiseze un rezultat. Conform fisierului exemplu, obiectivul este inserarea de alte functii.

[pedersen]

mersi dar nu ma ajuta
la rulare formula se muta automat pe coloana C.

[IPP]

Buna ziua

Nu imi este clar la ce va referiti.

Formula e creata de macro pe coloana C pentru ca acolo am vazut-o in fisierul dvs. exemplu (si am spus explicit acest lucru), iar formule propriuzise sunt ca cele din fisierul dvs. exemplu. Nu scrie niciunde ca ar trebui inserate pe o alta coloana. Daca va ganditi la un macro universal valabil, adica sa insereze ce trebuie si unde trebuie independent de structura informatiei, atunci cred ca va pot ura succes de pe acum. Structura informatiei din fisierul dvs. este atipica (sa nu spun incorecta) din punctul de vedere al unei liste excel-baza de date si orice solutie e de fapt un workaround cu rezultate mai bune, mai putin bune sau de-a dreptul proaste. Normal, toata coloana A trebuia sa fie completa si apoi se putea folosi un pivot table simplu. Nu este neaparat cazul acum, dar in stilul acesta va veni vremea in care va trebui sa va decideti: ori o completare corecta a unei liste excel baza de date (fapt ce implica o completare totala a coloanelor in care se gasesc criteriile), ori o structura fixa in care se poate face un macro. Sensul de macro este de automatizare.

Formulele sunt inserate complet pe coloana C pentru ca asa le-am vazut in fisierul dvs. exemplu. Daca trebuiesc inserate doar in dreptul apartiei criteriului de pe coloana A se poate scoate instructiunea macro de copiere formula in celelalte celule. Insa asa cum este acum, formula din dreptul criteriului nu va numara practic nimic pentru ca se refera la o singura celula nu la un range (de exemplu range-ul $B$3:B3 e de o singura celula in realitate).

IP

PS. Din fisierul dvs. exemplu s-ar mai putea deduce si altceva: se doreste obtinerea unui rezultat al unei functii countif in care criteriul e scris pe coloana A iar range-ul este pe coloana B si este delimitat de celula din dreptul criteriului pana la ultima celula aflata in dreptul criteriului urmator.

[TudyBTH]

Buna,

Daca nu tineti neaparat sa folositi VBA, incercati urmatoarea formula

Cod: Selectaţi tot

=COUNTIF(INDIRECT("$B"&MATCH("zzzzz",$A$1:$A3)&":$B"&ROW()),$B3)

[pedersen]

formula asta functioneaza da nu poate fi trasa in dreapta

=COUNTIF(INDIRECT("$B"&MATCH("zzzzz",$A$1:$A3)&":$B"&ROW()),$B3)

[TudyBTH]

pai nu poate, pentru ca nu ati cerut

Cod: Selectaţi tot

=COUNTIF(INDIRECT("B"&MATCH("zzzzz",$A$1:$A3)&":B"&ROW()),B3)

[pedersen]

am incercat si eu fara coordonate absolute dar tot nu merge trasa in dreapta tot B din ghilimele nu se schimba

[TudyBTH]

buna,

ar fi fost foarte util daca ati fi afisat un fisier din care sa rezulte structura datelor, asa cum o aveti in fisierul in care doriti sa introduceti formula.

doar asa pe presupuneri, pot doar sa va dau spre incercare o alta formula

Cod: Selectaţi tot

=COUNTIF(OFFSET($B$3,MATCH("zzzzz",$A$3:$A3)-1,COLUMNS($A:A)-1,ROWS($1:1)-MATCH("zzzzz",$A$3:$A3)+1,1),B3)

[pedersen]

Formula asta "=COUNTIF(OFFSET($B$3,MATCH("zzzzz",$A$3:$A3)-1,COLUMNS($A:A)-1,ROWS($1:1)-MATCH("zzzzz",$A$3:$A3)+1,1),B3)" n-o mers
da mesa N/A

Formula trebuie sa permita tragerea in dreapta .

Deci formula schimba randul din rangeul de inceput cu randul pe care se afla conditia de pe coloana specificata.
in fisier am trecut cateva rezultate manual.