Scrivere in ogni cella vuota un numero scelto da un range

[ikwae]

Ciao a tutti … sono ancora qui a chiedere il vostro prezioso aiuto…. Ho “assemblato”
una macro ma non riesco a fargli fare operazioni dettagliate che sono alla base della ricerca.

Quindi chiedo aiuto a tutto il Forum. L’aiuto che cerco è una semplice macro che compie le seguenti operazioni.
1) Cerca nell’intervallo ("B5:F22,B24:F41,B43:F60") la prima cella vuota.
2) Copia il numero presente dalla cella AM3 e controlla che sulla stessa riga della cella vuota non sia già presente
3) Incolla il numero della cella AM3 nella cella vuota.
4) Ritorna in cella AM3 e tira su di una cella verso l’alto
5) Continua la ricerca per la seconda cella vuota e prosegue come sopra fino al completamento delle celle vuote

Se il numero in AM3 non è idoneo perché già presente sulla stessa riga della cella vuota si scende in AM4 se anche il numero in AM4 non è idoneo perché è presente sulla stessa riga della cella vuota si scende di una cella si seleziona AM5 se il numero in AM5 è idoneo perché non è presente sulla stessa riga della cella vuota si scrive nella cella vuota in esame. Si ritorna in AM5 e si tira su di una cella.

E riprende il giro dalla prossima cella vuota prelevando sempre Inizialmente il numero in cella AM3 fino al completamento delle celle vuote dell’intervallo.

Se una cella vuota non ha numeri idonei (in colonna AM) per “riempirla” si lascia vuota e si prosegue con la prossima cella vuota.

Se si riesce a “riempire” tutte le celle vuote con i numeri la macro finisce con messaggio “completato”.

Se rimangono celle vuote la macro finisce con un messaggio tipo “non completato” o altro.

NON è richiesto che ma macro finisca in ogni caso di riempire le celle vuote se non ci sono numeri idonei.
A tale proposito chiederò in seguito, se necessario, una piccola modifica dopo averla studiata …
Allego un file con un foglio per eventuali prove … ringrazio anticipatamente tutti coloro che mi aiuteranno. 73 ikwae
http://www.filedropper.com/riempierecellevuote

[Anthony47]

Per come l'ho capita potrebbe andare bene questa macro:

Codice: Seleziona tutto

Sub ikVuote()
Dim eCell As Range, LastR As Long, myMatch, hLight As Long
Dim I As Long, J As Long, staRow As Long, staCol As Long, mErr As Long
Dim ME2 As String, ME3 As String
'
On Error Resume Next
LastR = Range("B:F").Find(What:="*", After:=Range("B1"), _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row
On Error GoTo 0
'
staCol = 39         '39=AM
staRow = 3
hLight = RGB(255, 255, 0)
For Each aa In Range("B5:F" & LastR)
    If aa.Interior.Color = hLight Then aa.ClearContents: aa.Interior.Color = xlNone
Next aa
Cells(staRow, staCol).Resize(1000, 1).Interior.Color = xlNone
For I = 5 To LastR
    For J = 2 To 6
        myMatch = CVErr(2200)
        If Cells(I, J) = "" Then
reGet:
            myMatch = Application.Match(Cells(staRow, staCol), Cells(I, "B").Resize(1, 5), False)
            If IsError(myMatch) Then
                Cells(I, J) = Cells(staRow, staCol)
                Cells(I, J).Interior.Color = hLight
                Cells(staRow, staCol).Interior.Color = hLight
                staRow = staRow + 1
            Else
                staRow = staRow + 1
            End If
            If Cells(staRow, staCol) = "" Then Exit For
            If Not IsError(myMatch) Then GoTo reGet
        Else
            If Cells(I, J).Interior.Color = hLight Then Cells(I, J).Interior.Color = xlNone
        End If
    Next J
    If Cells(staRow, staCol) = "" Then Exit For
    If Not IsError(myMatch) Then mErr = mErr + 1
Next I
If mErr > 0 Then ME2 = vbCrLf & "Righe incompilate: " & mErr Else ME2 = ""
If Cells(staRow, staCol) = "" Then ME3 = vbCrLf & "Rimpiazzi terminati su riga " & I Else ME3 = ""
MsgBox ("Completato" & ME2 & ME3)
End Sub


Quando la macro compila una cella vuota allora la cella viene colorata in Giallo, come pure il valore utilizzato in colonna AM; questo e' fatto ad uso debug, e poi potra' essere eliminato quando si confermera' che e' tutto regolare.

Un messaggio finale segnala (1) quante righe non sono state riempite perche' i valori in colonna AM erano tutti inutilizzabili e (2) su quale riga i valori in colonna AM si sono esauriti. Questo secondo controllo dovrebbe in realta' rendere inutile il primo, ma se lo lasciamo danni non ne fa.

Prova e fai sapere...

[ikwae]

Gentilissimo Anthony sempre gentile a rispondermi… La tua macro è magnifica, stupenda
e veloce illuminando di giallo una marea di celle. Ha però delle lacune nelle procedure richieste ecco i punti:

*La macro dovrebbe lavorare esclusivamente nel range ("B5:F22,B24:F41,B43:F60") ho provato a fare copia incolla del range e altre cosucce ma con scarso successo.

*Ho notato che in colonna AM non elimina il numero che ha incollato nella cella vuota.
In colonna AM dovrebbero rimanere solo i numeri che non sono idonei per completare
le celle vuote. I numeri rimanenti verranno prelevati, in automatico, per ulteriori elaborazioni.

*Ho mandato in esecuzione la tua macro e ho notato che rimangono dei numeri in colonna AM ossia i numeri 11,16,75 e 76 questi numeri sono idonei alle cinquine di riga 47, il numero 11 e 16, così si completa la cinquina e ancora il 76 per completare la cinquina della riga 48 mentre il numero 75 trova posto, senza completare la cinquina, in riga 50 e precisamente in B50.

*La tua macro la devo inserire in un circuito automatizzato e quindi sia il colore che i messaggi sono molto graditi ma purtroppo non saranno utilizzati. Dico questo perché la tua macro la devo replicare per 11 ruote ognuna con il suo range e colonna “AM” propria quindi non riuscirei a gestire vari messaggi o colori.

I messaggi solo se completa o non completa le cinquine che io modificherò al posto dei messaggi metterò un richiamo a una macro per decidere cosa fare in funzione del messaggio.

I numeri della colonna AM sono dei numeri super selezionati uno a uno e sono differenziati
dall’ottimo in AM3 a scendere in giù, nella colonna, con il numero meno ottimo.

Quindi la prima cella vuota con il primo numero la seconda cella vuota con il secondo numero e così via per le altre celle eliminando i numeri presi dalla colonna AM tirando su una cella.

Ricordo se il numero è presente nella riga della cella vuota si prende il numero successivo
della colonna AM e si scrive nella cella vuota cancellandolo dalla colonna AM tirando su una cella.

Ricordo ancora se una cella vuota non ha numeri disponibili si passa alla cella vuota successiva fino al completamento dei numeri in AM. Se tutte le celle vuote sono state confrontate e non ci sono numeri utili ecco che in colonna AM devono rimanere i numeri (dalla cella AM3 in giù) non idonei a nessuna cella vuota del range.

Aggiungo; le cinquine complete servono per le ripetizioni delle quaterne che è il cuore di tutto questo lavoro mente le cinquine non completate serviranno per la taratura della matrice che genera le cinquine e, i numeri rimasti in colonna AM, servono per la taratura dei numeri scelti.

Se hai tempo con calma e puoi modificare la macro come richiesto te ne sarei grato intanto ti ringrazio per il lavoro e il tempo che mi hai dedicato. Cordialmente ikwae

[Anthony47]

Ho mandato in esecuzione la tua macro e ho notato che rimangono dei numeri in colonna AM ossia i numeri 11,16,75 e 76 questi numeri sono idonei alle cinquine di riga 47, il numero 11 e 16, così si completa la cinquina e ancora il 76 per completare la cinquina della riga 48 mentre il numero 75 trova posto, senza completare la cinquina, in riga 50 e precisamente in B50

Questo comportamento e' dovuto alla mia interpretazione di questa tua frase: "Se il numero in AM3 non è idoneo perché già presente sulla stessa riga della cella vuota si scende in AM4 se anche il numero in AM4 non è idoneo perché è presente sulla stessa riga della cella vuota si scende di una cella si seleziona AM5 se il numero in AM5 è idoneo perché non è presente sulla stessa riga della cella vuota si scrive nella cella vuota in esame. Si ritorna in AM5 e si tira su di una cella"
Cioe' nella mia interpretazione una volta che si skippa una riga di colonna AM la riga andava considerata "bruciata". Prendo atto che invece i numeri saltati rimangono ancora disponibili.
Come pure prendo atto che e' necessario lasciare solo i numeri inutilizzabili.

Prima di proporre la prossima versione, vorrei sapere se la sequenza di ricerca dei vuoti deve essere rigorosamente dall'alto a sinistra verso il basso a destra o sono possibili anche sequenze meno regolari (ad esempio su riga 6 risovere prima F6 e poi B6)
Poi, visto che ribadisci l'esclusione delle righe 4-23-42, vorrei sapere se e' possibile che in quelle righe ci siano delle celle vuote.

Aggiungo; le cinquine complete servono per le ripetizioni delle quaterne che è il cuore di tutto questo lavoro mente le cinquine non completate serviranno per la taratura della matrice che genera le cinquine e, i numeri rimasti in colonna AM, servono per la taratura dei numeri scelti

Questa non la capiro' mai, ma so che non e' fondamentale per il mio contributo

Ti aspetto quindi coi due chiarimenti richiesti.

[ikwae]

Gentilissimo Anthony… Dalla cella A2 fino alla cella F630 non ci sono celle vuote tranne quelle che si tolgono i numeri in ogni intervallo. Per quanto riguarda l’intervallo preferisco, se possibile, lasciare come indicazione ("B5:F22,B24:F41,B43:F60") che il range attuale di azione perché poi quando replico la macro farò il copia e incolla delle altre ruote ad esempio ("B62:F79,B81:F98,B100:F117") … ("B119:F136,B138:F155,B157:F174") che sono le prossime della ruota di CA e di FI e poi tutte le altre ruote che sono simili a queste…

Io devo sapere cosa fa la macro in ogni istante perché si possono fare degli aggiustamenti con i numeri da mettere nelle celle vuote e mi riferisco alla tua richiesta. Si devono seguire le celle vuote ossia si parte dalla prima cella vuota e, se i numeri lo permettono, finire fino all’ultima cella vuota. Quindi si parte da B5 fino a F60 senza lasciare dietro nessuna cella vuota. Se devono rimanere celle vuote dovrebbero rimanere tutte in basso.

Con un esempio teorico elementare se io voglio scrivere, nelle celle vuote i numeri dall’1 al 40 e scrivo in colonna AM i primi 40 numeri dall’1 al 40 mi aspetto che nella prima cella vuota ci sia il numero 1 …che nella seconda cella vuota ci sia il numero 2 … che nella terza cella vuota ci sia il numero 3 … fino alla 40à cella vuota che dovrebbe contenere il numero 40.
Rispettando le regole precedentemente già esposte.

Alcune volte spiego il motivo del lavoro che si sta cercando di realizzare ma penso che con parole aggiuntive si possono creare degli errori di interpretazione dell’aiuto che cerco la prossima volta cercherò di scrivere l’indispensabile.

Penso di aver dato le indicazioni richieste e quindi con molta calma e quando hai tempo se puoi finire la macro ne sarei contento…ringraziandoti ancora per il tuo prezioso tempo che mi stai dedicando. Cordialmente ikwae

[Anthony47]

Una nuova versione della macro:

Codice: Seleziona tutto

Sub ikVuote2()
Dim myMatch, hLight As Long, fCell As Range, dDbug As Boolean
Dim I As Long, J As Long, staRow As Long, staCol As Long, mErr As Long
Dim ME2 As String, ME3 As String
Dim LookArea, K As Long, MArea As Range, WWI As Long
'
LookArea = Array("B5:F22", "B24:F41", "B43:F60")        '<<< Gli intervalli da esaminare
'
staCol = 39                 '<<< 39=AM
staRow = 3                  '<<< riga 3
hLight = RGB(255, 255, 3)   'solo per debug
dDbug = False                '
'
For K = 0 To UBound(LookArea)
    Set MArea = Range(LookArea(K))
    For I = MArea.Cells(1, 1).Row To (MArea.Cells(1, 1).Row + MArea.Rows.Count - 1)
        WWI = staRow
        For J = MArea.Cells(1, 1).Column To (MArea.Cells(1, 1).Column + MArea.Columns.Count - 1)
            myMatch = CVErr(2200)
            If Cells(I, J) = "" Then
reGet:
                myMatch = Application.Match(Cells(WWI, staCol), Cells(I, "B").Resize(1, 5), False)
                If IsError(myMatch) Then
                    Cells(I, J) = Cells(WWI, staCol)
                    If dDbug Then Cells(I, J).Interior.Color = hLight
                    Cells(WWI, staCol).Delete Shift:=xlUp
                Else
                    WWI = WWI + 1
                End If
                If Cells(WWI, staCol) = "" Then Exit For
                If Not IsError(myMatch) Then GoTo reGet
            End If
        Next J
        If Cells(staRow, staCol) = "" Then Exit For
        If Not IsError(myMatch) Then
            mErr = mErr + 1
        End If
    Next I
    If Cells(staRow, staCol) = "" Then Exit For
Next K
'
If mErr > 0 Then ME2 = vbCrLf & "Match errors: " & mErr Else ME2 = ""
If Cells(staRow, staCol) = "" Then ME3 = vbCrLf & "Rimpiazzi esauriti su riga " & I Else ME3 = ""
MsgBox ("Completato" & ME2 & ME3)
End Sub


Qualche prova l'ho fatta, ma un collaudo esaustivo puoi farlo solo tu

Ciao

[ikwae]

Gentilissimo Anthony… Dopo diverse ore di prove posso dire che la macro è bellissima, veloce e precisa al 99,99% c’è una leggerissima “imprecisione” mi spiego con un semplice esempio possiamo dire che le celle vuote sono 86.

Mandando in esecuzione la tua nuova macro legge i numeri in colonna AM e li mette al posto giusto con ordine e precisione ma ci sono le seguenti indicazioni “anomale” sui messaggi di risposta che come già detto nei post precedenti sui messaggi di risposta ci saranno ulteriori elaborazioni.

*se le celle vuote sono 86 e i numeri in AM sono 87 esce il messaggio [Completo…] che è giusto ma è “anomalo” perché in AM c’è un numero si è giusto per la completezza delle cinquine ma non per il proseguo delle “azioni”.

*se le celle vuote sono 86 e i numeri in AM sono 86 esce il messaggio [Completo Rimpiazzi esauriti su riga 60] che sembrerebbe giusto ma non certifica la completezza della cinquina e lo spiego con l’indicazione che segue

*se le celle vuote sono 86 e i numeri in AM sono 85 esce lo stesso il messaggio [Completo Rimpiazzi esauriti su riga 60] ma certamente una cinquina non è stata completata.

Se puoi correggere la macro in modo di avere un controllo sui numeri rimpiazzati che devono essere uguali alle celle vuote
(o viceversa come è meglio) e nessun numero in colonna AM. Solo in presenza di entrambi i dati deve dare il messaggio [Completo…] mentre in tutti gli altri casi un altro messaggio.

In poche parole se il messaggio è [Completo…] una macro copia il numero del concorso (B4) e la “cassa” ossia il range S7:V7 in totale 5 celle poi il foglio viene distrutto … Con un messaggio non completo una macro copia la B4, le ultime cinquine e i numeri rimasti in colonna AM e poi il foglio viene distrutto.

Il ciclo si ripete per 700/800 volte (800:156/7= 5) equivalenti a 5 anni di estrazioni quindi una macro specifica e tecnica che serve esclusivamente a dare pochissime e preziose informazioni di ricerca.

Ti ricordo che la macro viene replicata per 11 volte e ti ringrazio del range facilmente aggiornabile come da richiesta e della facile colonna AM (la 39) che si può anche lei aggiornare facilmente in tal caso ti allego il file per avere una idea dei range utilizzati. Questo lo faccio per evitare di disturbarti per chiederti modifiche o variazioni alla macro.

Ringraziandoti per il tuo tempo e il lavoro che mi stai dedicando… cordialmente ikwae
http://www.filedropper.com/riempierecellevuote2

[Anthony47]

Ecco la penultima versione:

Codice: Seleziona tutto

Sub ikVuote3()
Dim myMatch, hLight As Long, fCell As Range, dDbug As Boolean
Dim I As Long, J As Long, staRow As Long, staCol As Long, mErr As Long
Dim ME2 As String, ME3 As String, ME4 As String, Resid As Long
Dim LookArea, K As Long, MArea As Range, WWI As Long
'
LookArea = Array("B5:F22", "B24:F41", "B43:F60")        '<<< Gli intervalli da esaminare
'
staCol = 39                 '<<< 39=AM
staRow = 3                  '<<< riga 3
hLight = RGB(255, 255, 3)   'solo per debug
dDbug = False                '
'
For K = 0 To UBound(LookArea)
    Set MArea = Range(LookArea(K))
    For I = MArea.Cells(1, 1).Row To (MArea.Cells(1, 1).Row + MArea.Rows.Count - 1)
        WWI = staRow
        For J = MArea.Cells(1, 1).Column To (MArea.Cells(1, 1).Column + MArea.Columns.Count - 1)
            myMatch = CVErr(2200)
            If Cells(I, J) = "" Then
reGet:
                myMatch = Application.Match(Cells(WWI, staCol), Cells(I, "B").Resize(1, 5), False)
                If IsError(myMatch) Then
                    Cells(I, J) = Cells(WWI, staCol)
                    If dDbug Then Cells(I, J).Interior.Color = hLight
                    Cells(WWI, staCol).Delete Shift:=xlUp
                Else
                    WWI = WWI + 1
                End If
'                If Cells(WWI, staCol) = "" And J < 7 Then Exit For
                If Cells(I, J) = "" And IsError(myMatch) Then Exit For
                If Not IsError(myMatch) Then GoTo reGet
            End If
        Next J
        If Cells(staRow, staCol) = "" And J < 7 Then Exit For
        If Not IsError(myMatch) Or (Cells(WWI, staCol) = "" And J < 7) Then
            mErr = mErr + 1: Debug.Print I, J
        End If
    Next I
    If Cells(staRow, staCol) = "" Then Exit For
Next K
'
If mErr > 0 Then ME2 = vbCrLf & "Match errors: " & mErr Else ME2 = ""
Resid = Application.WorksheetFunction.CountA(Cells(staRow, staCol).Resize(100, 1))
If Resid > 0 Then ME4 = vbCrLf & "Valori residui: " & Resid Else ME4 = ""
If Cells(staRow, staCol) = "" And J < 7 Then
    ME3 = vbCrLf & "Rimpiazzi esauriti su riga " & I
Else
    ME3 = ""
End If
MsgBox ("Completato" & ME2 & ME3 & ME4)
End Sub


Il messaggio "Completato" esce perche' indica che la macro si e' completata; la situazione e' meglio chiarita dalla parte successiva del messaggio, che corrisponde al contenuto di 3 variabili di tipo stringa:
-ME2 indica quante righe non sono state completate perche' mancavano numeri idonei; se "vuoto" (cioe' lunghezza=0) significa che il problema non si e' posto
-ME3 indica su quale riga incompleta i rimpiazzi sono terminati; se "vuoto" significa che il problema non si e' posto
-ME4 indica quanti valori sono rimasti a completamento della macro; se "vuoto" significa che non ci sono numeri residui

Spero che con queste indicazioni puoi integrare il tutto nel tuo ambbiente.

Ciao

[ikwae]

Gentilissimo Anthony… Ho provato la tua nuova macro è va benissimo la userò sia come macro all’interno del ciclo ma purtroppo devo commentare (‘) tutti i messaggi altrimenti il ciclo si blocca. E la userò, con i preziosi e graditi messaggi, come banco di prova per gli “scarti” del ciclo. Ringraziandoti per il tuo triplo lavoro, gradito è apprezzato oltre alla tua “Santa Pazienza” non mi resta che ringraziarti mille e mille volte. Rinnovo l'invito per due spaghettini sui Navigli o in alternativa, se mi autorizzi, un caffè dalle tue parti. Ma se arrivo porto grembiulino matite colorate e pennarelli oltre al portatile con un migliaio di linee di codice da risolvere ... cordialmente ikwae …

[Anthony47]

Ti avevo scritto come decodificare le situazioni di errore (vedi ME2, ME3 e ME4) proprio perche' immaginavo avresti eliminato il msgbox.
Quanto alla spaghettata, prima o poi...

[ikwae]

Sarà un doppio piacere uno per conoscerti e due perchè speghetti alla Norma o una buona Matriciana sono sempre graditi ... speriamo presto ...cordialmente ikwae