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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,215,087
Messages
6,123,046
Members
449,092
Latest member
ikke

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