Excel macro VBA: Individuazione di prima cella vuota nella colonna e automaticamente il riempimento

Ho due colonne, Colonna A) Colonna e (B) in un foglio di calcolo.

Colonna (A) contiene i nomi estratti da una query (ex. Brian, Bob, Fattura, ecc…) e di colonna (B) contiene uno dei tre stati (Assegnato In Corso o in Attesa di registrazione).

Tuttavia, questa query a volte tira su alcuni elementi di riga mostra “Assegnato” per lo stato, senza nome, quindi corrispondente cella che rappresenta il nome nella Colonna (a) è vuota. Così io manualmente a riempire le celle vuote con “Sconosciuto”.

Quello che voglio fare è creare una macro che trova ogni cella vuota nella colonna (a) e riempire la parola “Sconosciuto” se la cella alla sua destra contiene la parola “Assegnato”.

In modo che le condizioni sono:

  1. Cella vuota nella colonna (a)

  2. Relativo cella alla sua destra (colonna B) contiene la parola “assegnato”

Questo è il mio Codice:

Private Sub CommandButton2_Click()

    For Each cell In Columns("A")
        If ActiveCell.Value = Empty And ActiveCell.Offset(0, 1).Value = "Assigned" Then ActiveCell.Value = "Unknown"
    Next cell

End Sub   
InformationsquelleAutor user1663562 | 2012-09-11



3 Replies
  1. 2

    Benvenuto a MODO.

    Provare questo codice. Sarà un lavoro un po ‘ più veloce e si dovrebbe ottenere quello che vuoi.

    Aggiornamento: Fatto il codice più a prova di proiettile!

    Private Sub CommandButton2_Click()
    
    Dim cel As Range, rngFind As Range, rngFilter As Range
    Dim wks As Worksheet
    
    Set wks = Sheets("sheet1")
    
    With wks
    
        '-> Error check to make sure "blanks" exist
        Set rngFind = .Range("A1:A" & .Range("B" & Rows.Count).End(xlUp).Row).Find("", lookat:=xlWhole)
    
        If Not rngFind Is Nothing Then
    
            Set rngFilter = .Range("A1:B" & .Range("B" & Rows.Count).End(xlUp).Row)
    
            rngFilter.AutoFilter 1, "="
    
            '-> Error check to make sure "assigned" exists for blank cells
            Set rngFind = .Columns("B:B").SpecialCells(xlCellTypeVisible).Find("Assigned", lookat:=xlWhole)
    
            If Not rngFind Is Nothing Then
            '-> okay, it exists. filter and loop through cells
    
                rngFilter.AutoFilter 2, "Assigned"
    
                Set rngFind = Intersect(.UsedRange, .UsedRange.Offset(1), .Columns(1)).SpecialCells(xlCellTypeVisible)
    
                For Each cel In rngFind
    
                    If cel.Offset(0, 1).Value = "Assigned" Then cel.Value = "Unknown"
    
                Next cel
    
            End If
    
        End If
    
    End With
    
    
    End Sub
    • Ho un Oggetto errore con .Text, ma funziona con .Value. Potrebbe essere la mia versione di Excel, potrebbe essere il codice.
    • Grazie per la risposta rapida. Tuttavia, il codice non sembra funzionare. Esso mette in evidenza “Set rng = Range(Range(“A1”), Range(“A” & Righe.Conteggio).End(xlUp)).SpecialCells(xlCellTypeBlanks)”
    • Non .Text è una proprietà readonly. È necessario utilizzare .Value
    • Perché non fare doppio filtro? Col per Un Vuoto e poi la colonna B per “ricevuto” in un colpo solo?
    • il mio male sul .Text vs .Value – grazie per l’appassionato occhi
    • re: ‘Perché non ha un doppio filtro di esso?` – Vedere la mia aggiornato risposta!!!
    • vedere il mio codice aggiornato – inoltre, non si dispone di celle vuote nella colonna a?
    • No che non è un doppio filtro. Volevo dire questo, Ad esempio, ` ActiveSheet.Range(“$A$1:$B$9″).AutoFilter Field:=1, Criteria1:=”=” ActiveSheet.Range(“$A$1:$B$9″).AutoFilter Field:=2, Criteria1:=”<>”
    • Le Celle Vuote sono nella Colonna (a) così, per esempio, la Cella (a2).valore = bob amd Cella (b2).Valore = Assegnato, e la Cella (a3).valore = vuoto e cella (b3).Valore = assegnato. Voglio trova tutte le celle nella colonna a vuoto di valori e la cella accanto ad essa è la parola assegnato(come le cellule (a3) (a4)
    • la mia modificato la risposta dovrebbe funzionare. Si riceve un errore?
    • che è quello che faccio. Aspettiamo che controlla la presenza di “Ricevuto” nella colonna B prima di applicare il 2 ° filtro. Davvero, dovrebbe controllare per spazi vuoti nella colonna a primo…
    • Ok, quindi senza errori di pop-up quando si esegue la macro, ma nulla accade per il foglio di calcolo. Il Vuoto di Valori rimangono vuoti
    • assicurarsi Set wks = Sheets("sheet1") si riferisce alla scheda di cui avete bisogno per lavorare con. Così, potrebbe essere necessario modificare "sheet1" per qualunque sia il nome del foglio che si sta utilizzando 🙂
    • L’ho fatto, la sua è stata impostata wks = Sheets(“Foglio3”) e ancora niente
    • hmmm… cosa succede se si filtra spazi, manualmente? Funziona? In altre parole, sono i vostri spazi davvero “spazi” o excel non leggerle come spazi -> che succede a volte quando si tira dati provenienti da altre fonti. Se si può postare un link per i dati di origine (o fittizio, è che sarebbe grande.
    • Heres un link a un file Fittizio mimicing il foglio di calcolo effettivamente dl.dropbox.com/u/104835256/Dummy.xls
    • Ah! Vedo cosa succede. Tutti gli spazi sono alla fine della colonna A, che rende questa linea Set rngFind = .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row).Find("", lookat:=xlWhole) mancano del tutto, poiché si trova l’ultima riga utilizzata nella colonna A. Quindi, per essere sicuri possibilità che la linea che ho appena fatto riferimento a questo: Set rngFind = .Range("A1:A" & .Range("B" & Rows.Count).End(xlUp).Row).Find("", lookat:=xlWhole) che utilizza la colonna B per ottenere la vera e propria ultima riga utilizzata.
    • si deve sapere che Readify la soluzione è quella di andare qui.
    • Grazie Scott, la tua versione funziona perfettamente :). Readify la soluzione funziona, ma la macro non fa realmente soddisfare la seconda condizione (solo cellule bersaglio che hanno “assegnato” alla sua destra) riempie la parola sconosciuta per tutte le celle vuote nella Colonna A. Ma comunque lo scopo di questa Macro è quello di imparare di più su di VBA in excel. Comunque, grazie di nuovo.
    • sì, vedo che riempie tutta la colonna di celle vuote, ma che possono essere facilmente modificati. Comunque, hai ragione. E hai sicuramente imparato molto, sembra! Penso che il punto stavo cercando di fare è Readify la soluzione più efficiente (e più facile da leggere!)

  2. 8

    Non c’è bisogno di loop qui, sfruttare eccelle costruito in metodi che verrà eseguito più velocemente.

    Private Sub CommandButton2_Click()
    
        Application.ScreenUpdating = False
    
        With ActiveSheet.UsedRange
            .AutoFilter Field:=1, Criteria1:=""
            .AutoFilter Field:=2, Criteria1:="Assigned"
    
            If WorksheetFunction.CountBlank(.Columns(1)) > 0 Then
                If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
                    .Columns(1).SpecialCells(xlCellTypeBlanks).Value = "Unknown"
                End If
            End If
    
            .AutoFilter
        End With
    
        Application.ScreenUpdating = True
    
    End Sub
    • + 1 Che è quello che ho suggerito in precedenza 🙂
    • +1 — questo è davvero un po ‘ più veloce rispetto alla versione che ho postato con un loop. Ho cronometrato sia in un foglio con oltre un milione di righe. Questo codice eseguito in 2 secondi sul mio portatile, il mio ciclo eseguito in 5. Su un campo, con un paio di migliaia di righe di essi sia stato eseguito in meno di un secondo e non riuscivo a capire la differenza. Ho notato un problema con questo, però — si blocca se non ci sono spazi vuoti nella colonna 1.
    • di Catering, io di solito non preoccuparsi di fornire la gestione dell’errore nel mio codice di esempi COSÌ. Non perché sono pigro! ma in generale penso che ci sono solo a molte incognite di produzione degna di codice e penso, in particolare, è al di fuori dell’ambito desiderato del forum COSÌ. Se qualcuno vi chiede se sono felice di aiutare, voglio aggiornare la risposta.
    • Molto bello! Penso che è una buona cosa da mettere nel codice, dopo tutto, non è improbabile che un utente fa clic su un pulsante due volte. Apprezzo anche imparare la tecnica. Nel corso degli anni ho fatto il viaggio dal Registratore di selezione per l’Iteratore e ora sono sulla mia strada per il livello successivo: Autofilterer!
    • Sì, si dovrebbe assolutamente essere nel codice non c’è dubbio. Ma penso che l’idea in MODO che è quello di fornire una soluzione ad un problema specifico, di non fornire completamente funzionante blocchi di codice. L’OP ha bisogno di prendere il campione di prova e poi modificarlo in base alle esigenze tra cui l’aggiunta di un errore di gestione. Non dovrebbe essere un cut & paste di lavoro. Ben fatto sulla progressione attraverso i ranghi! 😉 Una tecnica che uso è quello di pensare a me stesso: “se io non sono in grado di utilizzare VBA come potrei farlo con excel?”, quindi utilizzare VBA per eseguire il metodo di elaborare e utilizzare VBA per riempire eventuali spazi vuoti che non è possibile semplicemente utilizzando excel.
    • Hai eseguito l’idea di Sid? Le sue risposte sono spesso completamente funzionante blocchi di codice con i colpi di schermo e approfondita in stile blog spiegazioni. Io non sono d’accordo con il tuo approccio, ma credo che dicendo “l’idea che COSÌ” è troppo ampia di un pennello da utilizzare.
    • +1 – molto più semplice e facile da capire poi quello che ho fatto. Stesso concetto, una migliore esecuzione. @SiddharthRout – ora vedo di cosa si stava suggerendo.
    • Tuttavia, il codice deve essere modificato per soddisfare le esigenze degli utenti di solo riempire gli spazi vuoti cellule in usedrange con “sconosciuto”, non l’intera colonna.
    • Hai preso il mio commento fuori contesto. Ho detto “credo che l’idea”. Che significa “la mia interpretazione”. Non mi aspettavo gli altri a fare lo stesso come me e come Sid risponde alle domande è rispettosamente il suo business. Molti utenti post “dammi il codice” stile di domande. Preferisco aiutare coloro che hanno solo bisogno di un suggerimento nella giusta direzione, non ho molto tempo libero. Il speciallcells metodo genera un errore se non sono trovate cellule. Vi aspettavate me per fornire la gestione degli errori, ho spiegato perché l’ho fatto. Se questo è il codice di produzione sarebbe probabilmente essere di 10 righe in più a seconda delle esigenze.
    • Nel mio test non solo per riempire le celle vuote non l’intera colonna. E solo le celle con “Ricevuto” nella colonna adiacente. Forse l’OP non è stato con il mio codice modificato o mi manca qualcosa? E grazie per i commenti che avete fatto per quanto riguarda utilizzando il mio codice.
    • Ho fatto fraintendere il tuo commento. Il chiarimento è una delle ragioni per cui mi piace COSÌ … i diversi approcci che portano ad un significativo miglioramento delle conoscenze e standard di codifica. Non preferisco Sid approccio il vostro. Sono semplicemente diversi, e che sia il lavoro.
    • Ah, sì, ora vedo come si riempie solo le celle vuote in usedrange nella colonna A. penso che ho letto male ieri 🙂

  3. 1

    Se avete solo bisogno di fare questo un paio di volte si potrebbe

    1. formattare l’intervallo utilizzato come una tabella
    2. sulla colonna di Un filtro per mostrare solo “(Vuote)”
    3. colonna B filtro per mostrare solo “assegnato”
    4. selezionare tutte le celle risultante nella colonna B
    5. premere alt + : per selezionare solo le celle visibili
    6. premere F2
    7. tipo “unknown”
    8. premere ctrl + invio

    Brutti dati dovrebbe essere di buon ora!

    Ovviamente questo è un non-vba soluzione, ma se si può evitare di codifica è probabilmente il migliore.

    • +1 per la non-soluzione vba
    • Io di solito Ordinare l’Elenco, Scorrere verso il basso, e quindi inserire la Parola Sconosciuta per gli Elementi Vuoti. Ho creato una Macro che consente di estrarre i dati rilevanti da un separato WS e ordina. Ora ho bisogno di tutti è quello di creare questa seconda macro e questo processo sarà letteralmente essere fatto in due clic. Come posso inserire un foglio di excel in questo modulo? o devo inviarlo direttamente a te?
    • Quindi i dati che si sta lavorando è assemblato in modo dinamico è già? e devono essere assemblati in modo dinamico in futuro (non è un tempo di funzionamento)? Se hai bisogno di questo, allora si dovrebbe perseguire Scott risposta. Il mio metodo è la soluzione rapida, se non si vuole coinvolgere VBA.
    • Inoltre, non è consigliabile (o davvero possibile per quanto ne so) per il post completo file COSÌ. Si potrebbe risolvere il problema per voi di avere lavorato in background, ma rimuove la soluzione di dominio pubblico. Futuro la gente non sarà in grado di trarre vantaggio, come sarebbe necessario.
    • è possibile utilizzare http://www.dropbox.com come un file percorso di discesa. Poi si può postare il link qui, COSÌ.
    • Heres un link a un file Fittizio mimicing il foglio di calcolo effettivamente dl.dropbox.com/u/104835256/Dummy.xls

Lascia un commento