CERCA.VERT in VBA su file chiuso

[alfrimpa]

Buongiorno a tutti.

Premetto che tempo fa ho già posto questo tipo di domanda su altri forum non ricevendo, purtroppo, risposte risolutive.

Il problema è questo:

Supponendo di avere la seguente formula

Codice: Seleziona tutto

=CERCA.VERT(A1;'C:\Percorso\[nomefile.xlsx]Foglio1'!A1:C3;2;FALSO)

che posta su un foglio di lavoro funziona perfettamente, come faccio a "trasformarla" in codice VBA?

In pratica, come faccio a passare come secondo parametro del VLookup la matrice comprensiva del percorso e nome del file?

Codice: Seleziona tutto

Application.WorksheetFunction.VLookup(valore, ?????, 2, false)

Grazie.

Alfredo

[Anthony47]

Per quel che ne so non puoi fare una cosa cosi' in vba (su un workbook chiuso).
Pero' potresti inserire sul foglio una formula che lo calcola per te; ad esempio con

Codice: Seleziona tutto

myVal = "AZF"
Range("M1").Formula = _
    "=VLOOKUP(""" & myVal & """,'C:\Users\Utente1\Downloads\[NomeFile.xlsm]Mese'!A1:B24,2,0)"

Ciao

[alfrimpa]

Quindi Anthony, se ho capito bene, non è possibile (con VBA) memorizzare in una variabile il risultato del CERCA.VERT su un file chiuso ed immetterla come valore in una cella del foglio?

Alfredo

[Anthony47]

Si, "per quello che ne so" non puoi fare l' operazione in vba; ma (come abbiamo visto) la puoi far fare direttamente sul foglio inserendovi la formula.

[rossomike1966]

Per quel che ne so non puoi fare una cosa cosi' in vba (su un workbook chiuso).
Pero' potresti inserire sul foglio una formula che lo calcola per te; ad esempio con

Codice: Seleziona tutto

myVal = "AZF"
Range("M1").Formula = _
    "=VLOOKUP(""" & myVal & """,'C:\Users\Utente1\Downloads\[NomeFile.xlsm]Mese'!A1:B24,2,0)"

Ciao

Ciao,
e complimenti a tutti per il forum..

ho un problema simile anche io...

Ho creato con il pulsante registra la seguente macro (perché assolutamente non sono in grado di poterla costruire dal niente) che cerca e confronta dei valori in un altro foglio excel.
La prima volta che l'ho creata ed eseguita ha funzionato correttamente, senza intoppi.
Adesso, tutte le volte che la eseguo la macro vuole sapere quale è il file su cui andare a fare il cerca.vert, facendomi però perdere tempo

Potete aiutarmi a capire come inserire letteralmente il comando citato alcuni post fa da Anthony47?
devo inserirlo nella macro?
Se si a che altezza?
Oppure è qualcos'altro?

In alternativa cos'altro può aiutarmi nel dire a questo cerca.vert quali sono i file che deve "considerare"?

Grazie a tutti, davvero!

Codice: Seleziona tutto

Sub Cerca_Vert()
'
' Cerca_Vert Macro
'

'
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-74],'[Impieghi a MLT_30.12.15 DTM.xlsx]Pivot'!R6C[-76]:R70C[-75],2,FALSE)"
    ActiveCell.Select
    Selection.NumberFormat = "_-* #,##0.0_-;-* #,##0.0_-;_-* ""-""??_-;_-@_-"
    Selection.NumberFormat = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-"
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A65")
    ActiveCell.Range("A1:A65").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-75],'[Impieghi a MLT_30.12.15 DTM.xlsx]Pivot'!R6C[-77]:R70C[-75],3,FALSE)"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A65")
    ActiveCell.Range("A1:A65").Select
End Sub


[Anthony47]

Ciao rossomike1966, benvenuto nel forum.
La macro che hai listato inserisce sul foglio attivo le formule che puntano al file "Impieghi MLT_30.12.15 DTM.xlsx", foglio "Pivot"; a quale scopo la riesegui?
Se vai sulla cella in cui la macro aveva inserito la formula, quale formula leggi?

[rossomike1966]

Ciao rossomike1966, benvenuto nel forum.
La macro che hai listato inserisce sul foglio attivo le formule che puntano al file "Impieghi MLT_30.12.15 DTM.xlsx", foglio "Pivot"; a quale scopo la riesegui?
Se vai sulla cella in cui la macro aveva inserito la formula, quale formula leggi?

Ciao, hai ragione devo spiegarti...
la eseguo due volte perchè i valori da assegnare sono due; la prima va a cercare e assegnare numeri in valore assoluto, la seconda in valore % (nel registrare la macro mi è tornato comodo farlo in due passaggi)

questa invece è la formula che leggo nella cella del foglio attivo dopo aver eseguito la macro:

Codice: Seleziona tutto

=CERCA.VERT(C4;'[Impieghi diretti_30.12.15 DTM.xlsx]Pivot'!A$6:B$70;2;FALSO)

[Anthony47]

Ci sono cose che non ho ancora capito...
La prima domanda e' "perche' rilanci quella macro, se le celle gia' contengono le formule che ti servono?" La risposta che hai dato non mi sembra appropriata...

E poi, questa discussione si intitola " CERCA.VERT in VBA su file chiuso", ma il tuo file "Impieghi diretti_30.12.15 DTM.xlsx" sembra che in quel momento sia aperto; e' così?

Comunque per rendere piu' "solida" la macro converrebbe inserire, nell'istruzione che inserisce la formula, anche il percorso del file, non solo il nome; del tipo

Codice: Seleziona tutto

ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-74],'C:\Directory\Subdir\[Impieghi a MLT_30.12.15 DTM.xlsx]Pivot'!R6C[-76]:R70C[-75],2,FALSE)"


Ciao

[rossomike1966]

E poi, questa discussione si intitola " CERCA.VERT in VBA su file chiuso", ma il tuo file "Impieghi diretti_30.12.15 DTM.xlsx" sembra che in quel momento sia aperto; e' così?

Grazie per la dritta..si il file al momento è aperto; mi sembrava di aver capito che la tua formula nei primi post ovviasse a questo...ma non ho capito come applicarla al mio file.

Puoi aiutarmi un'ultima volta per favore?
Grazie ancora e scusa per la mia ignoranza in materia..

[Anthony47]

Devi modificare la macro inserendo il percorso completo del workbook.

Mi pare che la prima formula vada in BY4 (ma e' una stima "a occhio"), quindi la tua macro potrebbe diventare

Codice: Seleziona tutto

Sub CercaVertRemoto()
With Range("BY4")
    .Formula = "=VLOOKUP(C4,'C:\Directory\Subdir\[Impieghi diretti_30.12.15 DTM.xlsx]Pivot'!A$6:B$70,2,FALSE)" 
    .NumberFormat = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-"
    .Copy Range("BY4").Resize(65, 1)
End With
'
'codice analogo per la seconda formula
'
End Sub


Ovviamente userai il tuo vero percorso del file al posto di C:\Directory\Subdir; il tipo di formattazione l'ho copiata dal codice che avevi pubblicato.
Questa mette in BY4 la formula =CERCA.VERT(C4;''C:\Directory\Subdir\[Impieghi diretti_30.12.15 DTM.xlsx]Pivot'!A$6:B$70;2;FALSO)
Se la posizione o la formula sono diverse allora devi adattare; idem devi adattare per inserire la seconda formula nella seconda parte della macro
E se ti areni allora spiega quale formula deve essere messa in quale cella e vedremo di farlo assieme.

Ciao