cerca verticale in vb

Question

Salve a tutti!

semplifico visto che insieme al vostro aiuto il mio file si è complicato tantissimo e spero di restare chiaro ;)

La mia esigenza è la seguente:

 ho due fogli di lavoro il primo si chiama "Da comunicare" il secondo "test" il foglio test contiene record ordinati e la "chiave" di ricerca è in colonna A.

nel foglio "Da comunicare"  arrivano tramite macro dei record che popolano la tabella sulla seconda riga facendo scorrere i record verso il basso.

subito dopo aver inserito la nuova riga nella cella O2 mi serve impostare un cerca verticale di questo tipo

=CERCA.VERT(H2;Test!A:D;3;FALSO)

ma primo problema se non trova occorrenza mi dovrebbe restituire "blank"  e non il maledetto #N/D

perche poi altro controllo che vorrei mettere è se il valore in O2 <> blank

colora la riga da "A2:Q2" di rosso.

Ma non funziona ho fatto mille prove e mi perdo!

ho provato con se val errore IF(ISERROR(VLOOKUP ma poi mi sa che sbaglio qualcosa con gli apici o i ; ;(

la strada che stavo percorrendo è questa .... ma come si sistema?

meglio fare un ciclo che scorre tutte i record di test e se non trova nulla scrive blank mentre se lo trova scrive il valore di colonna tre e colora la riga di rosso?

ma come?

Range("O2").Select

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],test!C[-14]:C[-11],3,FALSE)"

    Range("P2").Select

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],test!C[-15]:C[-12],4,FALSE)"

    Range("O2:P2").Select

    Selection.Copy

    Range("O2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Application.CutCopyMode = False

    If Range("O2").Value <> "" Then

          Range("A2:Q2").Interior.ColorIndex = 3

          Else

        End If

Answer 1

te l'ho chiesto invece di provare perché non riesco ad afferrare bene l'utilità di usare il vba per creare formule nel foglio invece di fare i calcoli direttamente.

Ci sono contesti in cui lo si fa.

Io non conosco quello di MauroAP, Ho solo risposto alla sua domanda e alla tua.

Quello di cui si discute qui poi, non è un calcolo, ma una ricerca in una tabella fatta tramite vb e le soluzioni postate sono più veloci rispetto ad un For...Next o ad un Loop.

Answer 2

Salve a tutti!

semplifico visto che insieme al vostro aiuto il mio file si è complicato tantissimo e spero di restare chiaro ;)

La mia esigenza è la seguente:

 ho due fogli di lavoro il primo si chiama "Da comunicare" il secondo "test" il foglio test contiene record ordinati e la "chiave" di ricerca è in colonna A.

nel foglio "Da comunicare"  arrivano tramite macro dei record che popolano la tabella sulla seconda riga facendo scorrere i record verso il basso.

subito dopo aver inserito la nuova riga nella cella O2 mi serve impostare un cerca verticale di questo tipo

=CERCA.VERT(H2;Test!A:D;3;FALSO)

ma primo problema se non trova occorrenza mi dovrebbe restituire "blank"  e non il maledetto #N/D

perche poi altro controllo che vorrei mettere è se il valore in O2 <> blank

colora la riga da "A2:Q2" di rosso.

Ma non funziona ho fatto mille prove e mi perdo!

ho provato con se val errore IF(ISERROR(VLOOKUP ma poi mi sa che sbaglio qualcosa con gli apici o i ; ;(

la strada che stavo percorrendo è questa .... ma come si sistema?

meglio fare un ciclo che scorre tutte i record di test e se non trova nulla scrive blank mentre se lo trova scrive il valore di colonna tre e colora la riga di rosso?

ma come?

 

<cut>

Due alternative, quelle che utilizzo io di solito, le trovi qui:

http://www.maurogsc.eu/esempiforum12/cercavalori.zip

La prima m_1() utilizza Evaluate per scrivere nella cella B2 di FoglioLavoro il risultato relativo alla ricerca fatta sul foglio Dati in base al valore di A2 di FoglioLavoro.

La seconda m_2() è *tutta vb* per scrivere nella cella B5 di FoglioLavoro il risultato relativo alla ricerca fatta sul foglio Dati in base al valore di A5 di FoglioLavoro.

Answer 3

te l'ho chiesto invece di provare perché non riesco ad afferrare bene l'utilità di usare il vba per creare formule nel foglio invece di fare i calcoli direttamente.

Answer 4

qual'è la più veloce se i dati sono molti ?

Strana domanda. Bastava provare.

Comunque, per 20000 righe di ricerca nel foglio Dati, sono immediate entrambe.

Aggiungo una nota.

Se dovessi ripetere la ricerca per n righe della colonna A del foglio FoglioLavori, visto che si parla di *velocità*, la cosa da evitare sarebbe fare un ciclo For...Next. In questo caso ci sarebbe una terza soluzione(il codice si riferisce a 500 righe di FoglioLavoro):

Public Sub m_3()

    Dim s As String

    Dim shDati As Worksheet

    Dim sh1 As Worksheet

    With ThisWorkbook

        Set shDati = .Worksheets("Dati")

        Set sh1 = .Worksheets("FoglioLavoro")

    End With

    Application.ScreenUpdating = False

    With shDati

        s = "VLOOKUP(" & sh1.Name & "!A2," & .Name & "!A$2:D$20001,2,0)"

        s = "=IF(ISERROR(" & s & ")," & """""" & "," & s & ")"

    End With

    With sh1

        .Select

        With .Range("B2")

            .Formula = s

            .AutoFill Destination:=Range("B2:B500"), _

                Type:=xlFillDefault

        End With

        'nel caso nelle celle non voglia le formule ma i valori

        '.Range("B2:B500").Value = Range("B2:B500").Value

    End With

    Application.ScreenUpdating = True

    Set shDati = Nothing

    Set sh1 = Nothing

End Sub

Ovviamente i riferimenti ai Range si possono parametrizzare. In questo caso scrivo direttamente la formula con le funzioni di Excel nella prima cella utile e poi utilizzo AutoFill. Ho messo anche la riga di codice(commentata) nel caso non si vogliano vedere le funzioni ma i valori. Faccio notare il $ nella funzione VLOOKUP, in questo caso necessario per bloccare il riferimento corretto alla tabella Dati.

Grazie per l'attenzione e (quasi) sempre qui per ogni possibile spiegazione.

Buona giornata e buon lavoro.

Answer 5

qual'è la più veloce se i dati sono molti ?