how to apply a formula to filtered rows with the help VBA

M

Melika_Sn

New Member
Joined
Apr 27, 2022
Messages
5
Platform
  1. Windows
Hey everyone, ,
I have the following problem: I use a macro to fill cells. When I filter the cells and then run the macro, the cells are filled.
I need the code when I filter a few rows, after running the macro, the formulas will apply only to the filtered rows.
can you please help me where do i need to change it?
a small part of the macro is like this:

[
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With

'Dictionary mit Columnsname
Set dt_col = getSpaltenDict() 'Public Function in Modul 'Funktionen'

'Ermitteln des Namen der aktiven Datei und Tabellenblatt
file = ActiveWorkbook.Name
sheet = "Ordersheet"

'Ermitteln zeilenübergreifende Variablen
kat = ThisWorkbook.Worksheets(sheet).Cells(2, 3) 'Kategorie 1 für Splittregel

With ThisWorkbook.Worksheets(sheet)

If .FilterMode Then .ShowAllData 'Autofilter entfernen
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row 'Anzahl Einträge ermitteln
anzahlGrößen = getAnzahlGrößen 'Länge des Größenlaufs ermitteln
ReDim arr1(anzahlGrößen)

stopSub = False 'Ausgang falls aufgerufenen Funktionen nicht funktionieren
nextRow = False

'All column unhide
Range(Columns(dt_col("Mengenverteilung")), Columns(dt_col("Gesamtsumme NETTO (BLOCK)") + 2)).Hidden = False
ThisWorkbook.Worksheets(sheet).Cells(1, dt_col("Festes Sortiment/LOT")) = "unhide"
'Abbruch bei leerem Ordersheet
If lastrow < getHeaderRow() + 1 Then
MsgBox (" Ordersheet ist empty")
Exit Sub
End If
]

I want to fill MengeVerleitung(LT1) only for filtered row when running macro for MengenVerleitung:

Vorlage_Ordersheet_FS22_Marke_Kat1_v7.4.6.1.xlsm
APAQARASATAUAVAWAXAYAZBABBBCBDBEBFCECFCGCHCICJCKCLCMCNCOCPCQCRCSCTCUCV
4Typ der Größenverteilung:PTCC
5DD
6EE
7FF
8G0€ -G
9Erster LT Menge Festes Sortiment/LOT Sortiment/LOTGrößenlaufAnzahlMengenverteilungSortiments-/LOT-rechnerStückGesamtsumme NETTOLT min (LT1)LT max (LT1)Anzahl (LT1)Mengenverteilung (LT1)
1001.08.2022100 B110101010101010101055100100775,00 €
1119.08.2022100 B110101010101010101055100100775,00 €
1206.09.2022100 B110101010101010101055100100775,00 €
1324.09.2022100 B110101010101010101055100100775,00 €
1412.10.2022100 B110101010101010101055100100775,00 €
1530.10.2022100 B110101010101010101055100100775,00 €
1617.11.2022100 B110101010101010101055100100775,00 €
1705.12.2022100 B110101010101010101055100100775,00 €
1823.12.2022100 B110101010101010101055100100775,00 €
1910.01.2023100 B110101010101010101055100100775,00 €
2028.01.2023100 B110101010101010101055100100775,00 €
Ordersheet
Cell Formulas
RangeFormula
CF8CF8=SUMIFS(INDIRECT("Ordersheet!$BY$" & $AO$5 & ":$BY$" & $AO$8),INDIRECT("Ordersheet!$AH$" & $AO$5 & ":$AH$" & $AO$8),"<>BLOCK",INDIRECT("Ordersheet!$AJ$" & $AO$5 & ":$AJ$" & $AO$8),"<>Nein",INDIRECT("Ordersheet!$AH$" & $AO$5 & ":$AH$" & $AO$8), "<>STORNO")
CG8CG8=SUMIFS(INDIRECT("Ordersheet!$BZ$" & $AO$5 & ":$BZ$" & $AO$8),INDIRECT("Ordersheet!$AH$" & $AO$5 & ":$AH$" & $AO$8),"<>BLOCK",INDIRECT("Ordersheet!$AJ$" & $AO$5 & ":$AJ$" & $AO$8),"<>Nein",INDIRECT("Ordersheet!$AH$" & $AO$5 & ":$AH$" & $AO$8), "<>STORNO")
CE10:CE20CE10=IF($A10="","",IF(SUM(AV10:CD10)=0,"",SUM(AV10:CD10)))
CF10:CF20CF10=IF($A10="","",IF(CE10="","",CE10*AU10))
CG10:CG20CG10=IF($A10="","",IF(CF10="","",CF10*(($S10*(1-$R$2))*(1-$R$3))))
Cells with Data Validation
CellAllowCriteria
AU4:AU7List=GL
AQ4List=level
AS10:AS381List=Sortimente
AT10:AT381List=Größenlauf
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
You must log in or register to reply here.

Similar threads

M
  • Question Question
Sum function in filtered row
Replies
2
Views
340
Melika_Sn
M
V
  • Question Question
Sort/filter/index formula...
Replies
0
Views
212
valmir
V
V
  • Solved
Swap column order in sort filter formula
2
Replies
19
Views
1K
Peter_SSs
Peter_SSs
J
  • Question Question
VBA to extract row data to sheet and update data set
Replies
0
Views
951
justatroy
J
C
  • Question Question
How to Reduce Filtered Results in Pivot Table??
Replies
1
Views
994
clayjm
C

Forum statistics

Threads
1,224,591
Messages
6,179,768
Members
452,940
Latest member
rootytrip

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top