definire una sub con variabili

[Ale75]

salve,

ho una serie di sub con la stessa struttura e vorrei realizzarne solo una per migliorare il file.

Le sub filtrano un elenco di dati in funzione di un Codice che definisco in una cella (che ho nominato CODICE) ed ai valori cosi filtrati applica un Valore che definisco in una seconda cella (Che ho nominato VALORE), copiandolo in una colonna specifica indicata dalla lettera Knum, nell'esempio la terza colonna.

Ho definito questa Sub

Codice: Seleziona tutto

Sub FILTRA(CellaFiltro, CellaValore As Range, Knum As Integer) ' si applica solo su K3

Dim intervallo As Range
filtro = Worksheets("Home").Range("CellaFiltro").Value 'la cella con il codice da filtrare

Sheets("Home").Select
Range("CellaValore").Select  ' la cella con il valore da applicare al filtrare
Selection.Copy

Sheets("BOM x ORE").Select
intervallo = Range(Cells(Knum + 1, 1), Cells(Knum + 1, 500))

intervallo.Select
Selection.ClearContents  ' elimina tutti i dati precedenti

ActiveSheet.intervallo.AutoFilter Field:=10, Criteria1:="=*" & filtro & "*", Operator:=xlFilterValues  ' filtro sulla colonna 10

' routine per incollare i valori solo nelle celle filtrate
On Error GoTo NoCell
For Each myc In intervallo.SpecialCells(xlCellTypeVisible)
    myc.Value = Sheets("Home").Range("CellaValore").Value
Next myc
NoCell:
On Error GoTo 0


End Sub


che richiamo semplicemente da

Codice: Seleziona tutto

Sub prova()

Call FILTRA(CODICE, VALORE, 3)

End Sub


La sub è la stessa delle altre macro ad eccesione della definizione delle variabili.
Ma ottengo sempre errore, sembtra che il problema sia nella definizione degli argomenti.

sapoete indicarmi iuna soluzione (o dove sbaglio)?

grazie,
ale.

[Anthony47]

Purtroppo quel codice contiene troppi errori per essere di qualche utilita' e comprensione.
Quindi spiega meglio che cosa vorresti realizzare e vedremo cosa poter suggerire.

Ciao

[Ale75]

la mia necessita è filtrare un dababase cercando determinati codici ed associare un coefficiente numerico ai campi filtrati.
il database per semplicità è composto da otto colonne
la prima colonna contiene i codici da filtrare
la seconda dei valori numerici associati ai codici
le colonne dalla terza alla settima sono vuota e servono per incollare il coefficiente numerico di cui sopra
Nell'ottava colonna viene fatta la moltiplicazione delle colonne con i coefficienti e della colonna con il valore numerico di base.

In una pagina a parte chiamata HOME, ho settato alcune coppie di celle, associandoci il nome CODICE e VALORE (CODICE1, VALORE1, CODICE2, VALORE2 ecc ecc)
in pratica in CODICE(x) definisco quale codice cercare ed in VALORE(x) quale valore associare ed incollare nella colonna (X). (la prima coppia andrà nella colonna 3 (cioè la prima colonna libera), la seconda coppia nella colonna 4 e cosi via.

Oggi il file prevede macro differenti associate ad ogni coppia di valori, ma essendo più o meno lo stesso concetto volevo replicare una macro che prendesse come input i nomi delle celle contenenti il codice ed il valore oltre al n° della colonna dove incollare i coefficienti.

La sub dovrebbe nell'ordine:
1) cancellare tutti i dati nella colonna K (eventualmente lasciati da macro precedenti
2) filtrare la colonna 1 cercando il testo contenuto nella cella CODICE
3) incollare nella colonna K per i soli dati filtrati, il valore contenuto nella cella VALORE

in questo modo basta richiamare la stessa routine, associando celle diverse invece di avere N routine differente.
l'idea era questa.
Ho caricato ilfile su wetransfer (file senza macro
https://we.tl/TckDNzEVtu

grazie !!!

[Anthony47]

Era rimasta indietro...
Mi pare pero' che il file allegato c'entri poco con quanto qui discusso (e' lo stesso che avevi pubblicato sul quesito della convalida).
Puoi allegare quello che contiene la struttura dati a cui fai riferimento?

Ciao

[Ale75]

Ops... scusa
in alegato il file di test. ho provato a mettere le macro ma non funziona..
non so dove sbaglio
https://we.tl/MPS0tEEpOu

[Anthony47]

Ho speso 20 minuti cercando di far quadrare la descrizione col contenuto dei fogli e poi per capire cosa centrano le colonne vuote rispetto alla descrizione, ma e' stato tempo perso.
Questi i campi del foglio Database:


Questa la descrizione data:

la prima colonna contiene i codici da filtrare
la seconda dei valori numerici associati ai codici
le colonne dalla terza alla settima sono vuota e servono per incollare il coefficiente numerico di cui sopra
Nell'ottava colonna viene fatta la moltiplicazione delle colonne con i coefficienti e della colonna con il valore numerico di base.

Guardando su foglio Home il valori dati a Opzione1 sembrerebbe che Codice sia la colonna Descrizione (col B)e Valore sia la colonna Quanti (col C)

Cosa vuoi filtrare pere
Quanti ne vuoi 1

Mi fermo quindi a quella che e' la mia migliore interpretazione che riesco a dare:
-dato un foglio Database con il layout dato...
-vorrei filtrare sulla base di colonna B (Cosa) e colonna C (Quanti);...
-sulle righe filtrate vorrei inserire in colonne D-E-F-G un coefficiente numerico.
-Tutto possibilmente fatto con una unica macro parametrica che prende i dati da foglio Home

Non avendo detto come pensi di voler avviare questa macro, suppongo che i parametri siano da prelevare da foglio Home dove avrai creato una struttura di parametri simile a quella che hai pubblicato ma con in aggiunta i valori dei coefficienti K1-K4 (non e' obbligatorio che tutti e 4 i parametri siano compilati, possono anche essere lasciati vuoti)



Supponiamo che ci possano essere piu' gruppi di parametri sul foglio e che la scelta sia fatta (semplificando) in base alla selezione corrente.
Quindi, in base all'immagine, se viene selezionata la cella C5 allora si filtrera' per "pere" / "1"; poi nelle colonne D:G saranno inseriti i coefficienti 1, 2, 3, 4

Supponiamo che il filtro sia fatto per "Valore uguale a..." (e non, ad esempio "comincia per..." oppure "include ...")

Tutto cio' si potrebbe fare con il seguente codice:

Codice: Seleziona tutto

Sub Lancia()
'Passa a FiltraPar i parametri
Call FiltraPar(Selection.Cells(1, 1).Value, Selection.Cells(2, 1).Value, Selection.Cells(3, 1).Resize(4, 1))
End Sub


Codice: Seleziona tutto

Sub FiltraPar(ByVal ValoreB As String, ByVal ValoreC As Long, ByRef K1_K4 As Range)
'Parametri:
'   ValoreB = filtro per colonna B
'   ValoreC = Filtro per colonna C
'   K1_K4 = il Range che contiene i 4 coefficienti
'
Dim dSh As Worksheet, lastB As Long, myC As Range
'
Set dSh = Sheets("Database")
With dSh
    lastB = .Cells(.Rows.Count, 2).End(xlUp).Row
    'Filtra:
    .Range(.Range("A1"), .Cells(lastB, "K")).AutoFilter field:=2, Criteria1:=ValoreB
    .Range(.Range("A1"), .Cells(lastB, "K")).AutoFilter field:=3, Criteria1:=ValoreC
    'Assegna i coefficienti:
    On Error GoTo NoCell
    For Each myC In .Range("D2:D" & lastB).SpecialCells(xlCellTypeVisible)
        myC.Resize(1, 4).Value = Application.WorksheetFunction.Transpose(K1_K4)
    Next myC
NoCell:
    On Error GoTo 0
End With
End Sub


Va messo in un Modulo standard del vba; poi si seleziona la testa dell'elenco dei parametri (es C5, guardando la seconda immagine) e si avvia la Sub Lancia
Questa al suo interno spezza il contenuto delle 5 celle in verticale a partire dalla selezione e li trasforma in parametri da passare alla Sub FiltraPar

Ovviamente e' anche possibile richiamare direttamente la Sub FiltraPar passandole i parametri da posizioni a piacere, es
Call FiltraPar (Range"A2").Value, Range("B2").Value, Range("K1:K4"))

Il terzo parametro deve far riferimento a un intervallo obbligatoriamente "verticale" di 4 celle.

Se quanto propinato non corrisponde a quanto desiderato, beh allora devi riprovare a descrivere mantenendo maggiore coerenza tra quanto descrivi e quanto presente nel file pubblicato.

Ciao

[Ale75]

E' vero scusa. sto cercando di razionalizzare un file costruito negli anni ed ho fatto un po di casino.
Rifraso il quesito.
Cosa fa il file:
1. Dato un oggetto indicato nella Home ("Cosa vuoi Filtrare") applica solo a qull'oggetto dei coefficienti definiti sempre nella Home ("Quanti ne vuoi")
La macro deve filtrare i dati presenti nel database filtrando nella colonna "B" (COSA) tutti i campi che contengono il filtro definito in Home ("Cosa vuoi Filtrare") ed a questi applicare nelle colonne D, E, F o G il coefficente ("Quanti ne vuoi").
Sempre nella pagina batabase i coefficienti cosi incollati (potrebbero essere piu di uno per riga) vengono sommati tra se e moltiplicati per il valore indicato nella colonna C ("Quanti")

Qual' è il mio problema.
entrambe le pagine sono soggette a modifiche ed integrazioni, e quindi vorrei generalizzare le macro in modo da:
1) assegnare dei nomi alle celle della Home in modo che se sono costretto ad aggiungere una riga o colonna non devorivedere le macro
2) filtrare la pagina “Database” indicando un Range dinamico senza doverlo aggiustare a mano.
3) Nominare analogamente la colonne dove applicare i coefficenti (col D-E-F-G della pagina database) in modo da poter aggiungere nel futuro una colonna (ad esempio tra le colonne B e C) senza dover modificare la macro

Per questo avevo provato a creare una Sub dove definire le Variabili Globali e richiamare le macro.
Però ho dei (grossi) problemi nel richiamare le sub imponendo le variabili globali, e non capisco perchè.

Riallego il file caricato su we transfer all’indirizzo
https://we.tl/fjTclpWsbu

Un altro consiglio:
La pagina Database ha oltre mille righe, lavorando sulle macro ho sbagliato dei riferiemnti ed ho cancellato dei dati.
Posso “bloccare” le celle senza compromettere le macro? Considera che nel file ho altre macro che copiano i dati (dalle celle che vorrei bloccare) e gli incollano in altre pagine.

Grazie ancora,
Alessandro.

[Anthony47]

Ho perso tempo e ora il file non e' piu' disponibile
Se il problema e' sempre in coda allora per favore pubblica nuovamente un file dimostrativo.

Ciao

[Ale75]

eccolo
https://we.tl/gkwrUeip0I

(sempre su wetransfer)


e grazie ancora.
Ale.

[Anthony47]

Mi dispiace, ma la tua terminologia e' diversa dalla mia e faccio fatica, anche guardando il file, a capire quel che dici.

La migliore traduzione che ho fatto delle varie tue affermazioni:

1. Dato un oggetto indicato nella Home ("Cosa vuoi Filtrare") applica solo a qull'oggetto dei coefficienti definiti sempre nella Home ("Quanti ne vuoi")
La macro deve filtrare i dati presenti nel database filtrando nella colonna "B" (COSA) tutti i campi che contengono il filtro definito in Home ("Cosa vuoi Filtrare") ed a questi applicare nelle colonne D, E, F o G il coefficente ("Quanti ne vuoi")

Nel foglio Home indico dei valori da filtrare (vedi voce "Cosa vuoi Filtrare"); ho bisogno di usare questi valori per filtrare la colonna B del foglio DATABASE, e alle righe cosi' filtrate devo inserire (in UNA DELLE COLONNE D-E-F-G) il valore impostato in foglio Home nella cella "Quanti ne vuoi", e vorrei poter impostare piu' valori "Quanti ne vuoi" per ognuna delle 4 colonne (D-E-F-G).

Sempre nella pagina DATABASE i coefficienti cosi incollati (potrebbero essere piu di uno per riga) vengono sommati tra se e moltiplicati per il valore indicato nella colonna C ("Quanti")

Vedo che l'hai fatto con la formula =SOMMA(D3:G3)*C3

Qual' è il mio problema.
entrambe le pagine sono soggette a modifiche ed integrazioni, e quindi vorrei generalizzare le macro in modo da:
1) assegnare dei nomi alle celle della Home in modo che se sono costretto ad aggiungere una riga o colonna non devorivedere le macro
2) filtrare la pagina “Database” indicando un Range dinamico senza doverlo aggiustare a mano.
3) Nominare analogamente la colonne dove applicare i coefficenti (col D-E-F-G della pagina database) in modo da poter aggiungere nel futuro una colonna (ad esempio tra le colonne B e C) senza dover modificare la macro

Le cose del punto 1 mi pare che le hai fatte
Le cose del punto 2 non sono complesse; basta infatti indirizzare le colonne e automaticamente sarebbero prese in considerazione solo le righe valide.
Il punto 3 e' chiaro.

Per questo avevo provato a creare una Sub dove definire le Variabili Globali e richiamare le macro.
Però ho dei (grossi) problemi nel richiamare le sub imponendo le variabili globali, e non capisco perchè

Beh, diciamo che devi fare un po' di pratica.
Ad esempio, se la sub parametrizzata ha questa intestazione

Codice: Seleziona tutto

Sub FILTRA(CellaFiltro As String, CellaValore As String, Knum As Integer)

allora poi non devi usare (per cominciare) Filtro = Worksheets("Home").Range("CellaFiltro").Value perche' in questo caso vai a usare la stringa CellaFiltro, che e' diverso che usare la variabile CellaFiltro.


Comunque se le mie "migliori traduzioni" del tuo messaggio rappresentano le tue richieste, allora la mia proposta e' la seguente:

Sul foglio HOME crea un'are in cui inserisci, su N celle verticalmente contigue, rispettivamente:
1) il contenuto di colonna B (su foglio DataBase) che vuoi filtrare; a questa cella assegna il nome "FiltroA"
2) i valori da attribuire alle colonne, con accanto l'intestazione della colonna cui quel valore si riferisce
Puoi inserire fino a 100 righe, purche' siano contigue, abbiano nella colonna adiacente una intestazione della colonna, e abbiano in fondo una riga vuota nella colonna "intestazione di colonna". Il valore "intestazione di colonna" deve essere uguale a quanto usato in Riga2 del foglio DataBase. Valori che non corrispondono vengono evidenziati in rosso.

Il contenuto delle celle "2)" puo' essere
a) un Numero; sara' inserito nella colonna "intestazione di colonna"
b) cella vuota; il contenuto della colonna non sara' alterato
c) il numero 0; il contenuto della colonna potra' essere sostituito da 0 (come caso "a") o semplicemente cancellato e colonna lasciata vuote (vedi riga marcata +++ nel codice)

Insomma, non vedo il bisogno di usare 4 opzioni per filtrare un prodotto e compilare una alla volta le quattro colonna D-E-F-G

Questa struttura dati su foglio Home viene poi "attuata" tramite questa macro:

Codice: Seleziona tutto

Sub DBManage()
Dim dbSh As Worksheet, dbT0 As String, dbTb As Range
Dim cuFilt As String, coFilt As Long, kCol
Dim I As Long, J As Long
'
Set dbSh = Sheets("DATABASE")       '<<< Il foglio con la tabella
dbT0 = "A2"                         '<<< L'inizio della tabella su foglio dbSh
coFilt = 2                          '<<< La colonna della tabella da filtrare
'
'Imposta Tabella corrente:
Set dbTb = dbSh.Range(dbT0).Resize(dbSh.Range(dbT0).End(xlDown).Row, _
    dbSh.Range(dbT0).End(xlToRight).Column - dbSh.Range(dbT0).Column + 1)
'Filtro corrente:
cuFilt = Range("FiltroA").Value
'Fitra tabella; SERVE??
'dbTb.AutoFilter Field:=coFilt, Criteria1:="=*" & cuFilt & "*", Operator:=xlFilterValues  ' filtra tabella
'Scan Righe della tabella:
For I = 1 To dbTb.Rows.Count
If InStr(1, dbTb.Cells(I, coFilt), cuFilt, vbTextCompare) > 0 Then
'Scan righe "Quanti ne vuoi":
    For J = 1 To 100
        If Range("FiltroA").Offset(J, 1) <> "" Then
            kCol = Application.Match(Range("FiltroA").Offset(J, 1).Value, WorksheetFunction.Index(dbTb, 1, 0), 0)
            If IsError(kCol) Then
                Range("FiltroA").Offset(J, 1).Interior.Color = RGB(255, 100, 100)
            Else
                Range("FiltroA").Offset(J, 1).Interior.ColorIndex = xlNone
                If Range("FiltroA").Offset(J, 0).Value <> "" Then
                    dbTb.Cells(I, kCol).Value = Range("FiltroA").Offset(J, 0).Value
                    If dbTb.Cells(I, kCol).Value = 0 Then dbTb.Cells(I, kCol).ClearContents    '+++ Rimuovi per inserire 0 e non Vuoto
                End If
            End If
        Else
            Exit For        'Termine lista "Quanti ne vuoi"
        End If
    Next J
End If
Next I
End Sub


Nota che la macro non filtra la tabella, perche' lavorare con la tabella filtrata non e' piu' semplice che lavorare con la tabella completa...

Il tutto e' dimostrato nel file scaricabile qui:
https://www.dropbox.com/s/gesnu881fkucf ... .xlsm?dl=0
L'area da compilare e' sul foglio Home, le celle colorate in Verde; la macro da lanciare e' la Sub DBManage, in Modulo1.

Ciao

[Ale75]

Perfetto. ho solo un ultimo dubbio sull'uso delle variabili (scusate ....)
Ho semplificato come sotto, perche vorrei creare una sola sub, e poi richiamarla definendo delle variaili, quindi ho definito la sub DBManager impostando 3 variabili.

Codice: Seleziona tutto

Sub DBManager(ByVal Filtro As String, ByVal ValoreK As Single, ByVal CoefK As String)


e richiamando la call come segue.
facendo cosi ottengo errore (run time 13 - type mismatch) e non capisco perchè.

nota se associo direttamente le variabili globali dento DBManaget (cioè sostituisco Filtro con A e ValoreK con B e CoefK con "C", la macro gira perfettamente



sotto il codie completo.

Codice: Seleziona tutto

Call DBManager(A, B, "C")


dove A e B sono due variabili Globali definite all'inizio del modulo e caricate da una prima sub (che qui non ho mostrato)

Codice: Seleziona tutto

Public A as String
Public B as single


Codice: Seleziona tutto

Sub xxx()


Call DBManager(A, B, "C")

End Sub
-------
Sub DBManager(ByVal Filtro As String, ByVal ValoreK As Single, ByVal CoefK As String)

For I = 1 To RangeX.Rows.Count
    If InStr(1, RangeX.Cells(I, 15), Filtro, vbTextCompare) > 0 Then
        kCol = Application.Match(CoefK, WorksheetFunction.Index(RangeX, 1, 0), 0) 'cerca la colonna nella riga 4 corrispondente al CoefK

        'If IsError(kCol) Then
           '   pippo = 1 'solo per far fare qualcosa (da eliminare quando ho sistemato tutto)
         '   Else
                    If ValoreK > 0 Then RangeX.Cells(I, kCol).Value = ValoreK
                    If ValoreK = 0 Then RangeX.Cells(I, kCol).ClearContents   
    End If
Next I

end sub


[Anthony47]

Per capire come puoi richiamare una Sub e passargli i parametri richiesti, guarda l'help on line del vba; parti dalle voci

Codice: Seleziona tutto

Chiamata di routine Sub e Function

Istruzione Sub

Argomenti predefiniti e facoltativi


E guarda anche le voci sotto "Vedere anche"

Nel caso pubblicato non sono in grado di aiutare perche' non so su quale base dati hai provato; ma noto che le variabili A e B non sono mai avvalorate, mentre RangeX immagino sia il risultato di una Set che non compare nel codice pubblicato.

Quanto all'errore che compare, il significato e' illustrato nell'help on line alla voce

Codice: Seleziona tutto

Tipo non corrispondente (errore 13)

Nello stesso file gia' pubblicato ho inserito in Modulo2, a mo' di demo, una nuova Sub xXx che richiama una versione parametrica della Sub DBManager, denominata Sub DBManager2.
Come vedi ho spostato alla sub chiamante (Sub xXx) la definizione di elementi che prima venivano impostati nel codice della Sub DBManager.

Puoi copiare quato ho fatto per realizzare delle versioni piu' sofisticate compatibili con i tuoi obiettivi.

Ciao

[Ale75]

Grazie,

funziona tutto.
Era un errore nella definizione (un nome digitato male).

un ultima cosa.
Esiste un modo per proteggere le celle da scriittura facendo pero funzionare le macro?

spiego meglio. dato che il file è grosso ed ha diverse righe, vorrei proteggere le celle compilate del database e las.ciare editabili quelle dove vengono imposti i valori K

il problema è che se proteggo le celle del database poi non posso fare i filtri per attivare le macro.

Esiste un modo per aggiurare il problema?

grazia,
Ale.

[Anthony47]

Nel momento in cui proteggi un foglio hai la possibilita' di spuntare quali operazioni rimangono consentite all'utente, compreso Usa filtro automatico e Formato celle

L'alternativa e' inserire Sheets("Foglio1").Unprotect a inizio macro e Sheets("Foglio1").Protect a fine macro; oppure proteggere il foglio usando l'opzione "UserInterFaceOnly", che blocca l'utente ma lascia libere le macro di fare molte cose (non "tutte", però).
Per ottenere questo, metti nel modulo ThisWorkbook

Codice: Seleziona tutto

Private Sub Workbook_Open()
Sheets("Foglio1").Unprotect Password:="Segretissima"       '<<< Inserisci il vero foglio di lavoro e la vera password
Sheets("Foglio1").Protect Password:="Segretissima", UserInterFaceOnly:=True  '<<<Idem
Sheets("Foglio2").Unprotect Password:="Segretissima"       '<<< Inserisci il vero foglio di lavoro
Sheets("Foglio2").Protect Password:="Segretissima", UserInterFaceOnly:=True  '<<<Idem
'. . . (altri Fogli)
'...
End Sub

In questo caso le macro avranno "mano libera".

Ciao

[Ale75]

il mio problema è che non voglio che non vorrei abilitare le macro e sovrascrivere per errore dei dati, quindi vorrei proteggere il file.
ho scritto questa macro che funziona correttamente, nel senso che blocca le celle e consente l'utilizzo del filtro , ma solo a mano

Codice: Seleziona tutto

Sub BloccaBOM()

Sheets("Pagina").Select
    Range("L1").Select
    Selection.Style = "Bad"
    Range("L1").Value = "LOCKED"
''''''''''''''''''''''''''''''''''''' intervallo celle sbloccate '''''''''''''''''
    Range("A2:A1500,AL5:AQ1500,BC5:BH1500,BT5:BY1500,CK5:CP1500,DB5:DG1500").Select
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Selection.Locked = False
    Selection.FormulaHidden = False
   
    Cells.Select
    ActiveSheet.Protect "pippo", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFiltering:=True

End Sub


infatti poi la macro con il filtro mi si blocca qua

Codice: Seleziona tutto

Range("A4:Dz5000").AutoFilter Field:=14, Criteria1:="Y", Operator:=xlFilterValues 


dicendomi che le celle sono bloccate e la macro non puo funzionare.

Nota. il filtro è nella riga 4, che ho sbloccato da FORMAT CELLS --> Protection e smarcando Locked

come posso risolvere?

grazie,
Ale.

[Anthony47]

Come detto, se il primo metodo non funziona, allora "L'alternativa e' (a) inserire Sheets("Foglio1").Unprotect a inizio macro e Sheets("Foglio1").Protect a fine macro; oppure (b) proteggere il foglio usando l'opzione "UserInterFaceOnly", che blocca l'utente ma lascia libere le macro di fare molte cose (non "tutte", però)"

(a) e (b) li ho aggiunti per evidenziare che i metodi alternativi sono due.

Ciao