Invalid character | Visual Basic for Applications

Invalid character
di roberto21 data: 15/01/2015 13:00:46
Magari è una domanda scema, ma.. Ho trovato questo workaround per utilizzare la funzione Vlookup per farmi restituire un valore alla SINISTRA della colonna di ricerca,cosa impossibile con vlookup "normale":

=VLOOKUP(F2,CHOOSE({1,2},$B$2:$B$7,$A$2:$A$7),2,False)

In sostanza, se ho ben capito, CHOOSE inverte le due colonne, per cui se F2 è nella seconda colonna e il valore che mi serve è nella prima con questo trucco ottengo il risultato desiderato. Ora, la domanda è: posso usare questa formula in VBA? Cioè, posso scrivere:

codice = WorksheetFunction.VLookup(ComboBoxNomeConto.Value, CHOOSE({1,2},$B$2:$B$102,$A$2:$A$102),2,0)

ove codice è definito come stringa ?. Così com'è, ricevo un "compilation error, invalid character {", per cui ritengo debba esserci qualche errore nella scrittura della formula "nestata" (NB: lo statement con solo Vlookup senza CHOOSE funziona benissimo). Finora non ho trovato una soluzione, magari qualcuno (come spesso capita) vede subito l'errore scemo?

di scossa data: 15/01/2015 13:53:25
La soluzione più semplice è utilizzare il metodo Evaluate nella forma prolissa:

Application.Evaluate("VLOOKUP(F2,CHOOSE({1,2},$B$2:$B$7,$A$2:$A$7),2,False)")

o compatta:

[VLOOKUP(F2,CHOOSE({1,2},$B$2:$B$7,$A$2:$A$7),2,False)]

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

Public Sub prova()
  'prolissa
  MsgBox Evaluate("VLOOKUP(F2,CHOOSE({1,2},$B$2:$B$7,$A$2:$A$7),2,False)")
  'compatta
  MsgBox [VLOOKUP(F2,CHOOSE({1,2},$B$2:$B$7,$A$2:$A$7),2,False)]
End Sub

di roberto21 data: 15/01/2015 15:33:48
Grazie mille per la pronta risposta. Sfortunatamente, non MI funziona: provando così

teststring = ComboBoxNomeConto.Value
MsgBox Application.Evaluate("VLOOKUP(teststring,CHOOSE({1,2},$B$2:$B$102,$A$2:$A$102),2,0)")

mi vien fuori codice errore 13, type mismatch. Teststring contiene quello che deve (una stringa scelta su ComboBox).

teststring = ComboBoxNomeConto.Value
MsgBox Application.Evaluate("VLOOKUP(teststring,CHOOSE({1,2},$B$2:$B$102,$A$2:$A$102),2,0)")

di scossa data: 15/01/2015 16:20:00

teststring è una variabile quindi .....

MsgBox Evaluate("VLOOKUP(" & teststring & ",CHOOSE({1,2},$B$2:$B$7,$A$2:$A$7),2,False)")

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di roberto21 data: 15/01/2015 16:34:54
Giusto. Corretto l'errore, ma stesso runtime 13 error,type mismatch.

MsgBox Application.Evaluate("VLOOKUP(" & teststring & ",CHOOSE({1,2},$B$2:$B$102,$A$2:$A$102),2,0)")

MsgBox Application.Evaluate("VLOOKUP(" & teststring & ",CHOOSE({1,2},$B$2:$B$102,$A$2:$A$102),2,0)")

di scossa data: 15/01/2015 16:40:17
Scusa, gli apici avviamente vanno raddoppiati:

MsgBox Application.Evaluate("VLOOKUP(""" & teststring & """,CHOOSE({1,2},$B$2:$B$7,$A$2:$A$7),2,False)")

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di roberto21 data: 15/01/2015 17:01:36
Scusa, capisco che sto rompendo... Giusta anche questa correzione, e ora pare che funzioni...
Mi sono spaventato quando ho beccato lo stesso errore, ma ho avuto un dubbio... e stavolta il problema è legato al fatto che la stringa di ricerca può contenere dei blanks... e in questo caso il problema rimane. Ma questo è un altro discorso.

MsgBox Application.Evaluate("VLOOKUP(""" & teststring & """,CHOOSE({1,2},$B$2:$B$102,$A$2:$A$102),2,0)")

Grazie 1000

di scossa data: 15/01/2015 20:10:28
cit. roberto21: "....e stavolta il problema è legato al fatto che la stringa di ricerca può contenere dei blanks... e in questo caso il problema rimane"

Gli spazi non interferiscono: il codice sotto funziona perfettamente (se "beta gamma" è presente in una cella della colonna B).

Quindi o posti il tuo file coi dati originali o si perde solo tempo.

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

Public Sub prova()
  Dim teststring As String
  teststring = "beta gamma" 'presente in B5
  MsgBox Application.Evaluate("VLOOKUP(""" & teststring & """,CHOOSE({1,2},$B$2:$B$7,$A$2:$A$7),2,False)")
End Sub

di roberto21 (utente non iscritto) data: 15/01/2015 22:30:48
Nessun problema, allego il worksheet con le due colonne che uso... ma ho fatto le mie prove prima di postare l'ultimo messaggio, e
1) teststring = "A Tavola in compagnia" ===> run time error 13 type mismatch
2) teststring = "Ballo" ===> codice OK

Per completezza, va detto che teststring viene ottenuta tramite un combobox su una user form, popolata all'origine con i dati della colonna B. L'utente sceglie il nome del conto, e da quello con vlookup risalgo al codice, che è in colonna A. Riporto il codice qui sotto.
Grazie per l'attenzione, non intendevo far perdere tempo a nessuno

Private Sub UserForm_Initialize()
   
    Dim ws As Worksheet
    Dim i, LastRow As Long
        
    CommandInserimento.Enabled = False
    Worksheets("Pianodeiconti2").Activate
    Set ws = Worksheets("Pianodeiconti2")
        
    LastRow = ws.Range("A2").End(xlDown).Row
    With ComboBoxNomeConto
                
        For i = 2 To LastRow
            .AddItem Range("NomiConti").Cells(i, 2).Value
         
        Next i
        
    End With

    Me.TextBoxDate.Value = Format(Date, "Short Date")
    Me.ComboBoxNomeConto.SetFocus
End Sub


Private Sub ComboBoxNomeConto_Exit(ByVal Cancel As MSForms.ReturnBoolean)
' Prendere e visualizzare il codice numerico
Dim codice, teststring  As String
On Error GoTo errore
With Worksheets("Pianodeiconti2")
    teststring = Me.ComboBoxNomeConto.Value
   codice = Application.Evaluate("VLOOKUP(""" & teststring &  """,CHOOSE({1,2},$B$2:$B$102,$A$2:$A$102),2,False)")
    
End With
Me.TextBoxCodiceConto = codice

Exit Sub
errore:
    MsgBox Err.Number & " Conto sconosciuto"
End Sub

di scossa data: 16/01/2015 10:44:28
cit. roberto21: "... non intendevo far perdere tempo a nessuno ... "

Il codice sottoriportato, copiato nel tuo file (in un modulo standard) funziona perfettamente, quindi il problema sta nella tua combobox e/o userform.
Ma tu hai pensato bene di postare un file .xlsx privo di qualsiasi Userform e di qualsiasi codice, e di certo non spetta a noi creare userform e relativi controlli per cui ..... .

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

Public Sub prova()
  Dim teststring As String
  teststring = "A Tavola in compagnia" 'presente in B5
  MsgBox Application.Evaluate("VLOOKUP(""" & teststring & """,CHOOSE({1,2},$B$2:$B$102,$A$2:$A$102),2,False)")
End Sub

di roberto21 (utente non iscritto) data: 16/01/2015 13:11:56
ok, prendo atto. Continuo a provare per scovare l'errore. Allego il file completo che sto usando per lo sviluppo: ovviamente il codice di gestione è incompleto, ci sto lavorando ora.
Grazie per la cortese attenzione. Ripeto, non intendo far perdere tempo a nessuno.

di scossa data: 16/01/2015 14:21:14
Perché ComboBoxNomeConto ha due colonne?

Puoi correggere le proprietà ColumnCount e BoundColumn mettendo a 1 il valore, oppure se vuoi manternere le 2 colonne (?) devi correggere:

codice = Application.Evaluate("VLOOKUP(""" & Me.ComboBoxNomeConto.Value & """,CHOOSE({1,2},$B$2:$B$102,$A$2:$A$102),2,False)")
in
codice = Application.Evaluate("VLOOKUP(""" & Me.ComboBoxNomeConto.Text & """,CHOOSE({1,2},$B$2:$B$102,$A$2:$A$102),2,False)")

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di Vecchio Frac data: 16/01/2015 14:25:39
cit. "Ripeto, non intendo far perdere tempo a nessuno."
---> Ma quello che voleva dirti scossa, probabilmente, è che chi apre una discussione, posta un problema, chiede spiegazioni, ecc., lancia una bottiglia nel mare: qualcuno la raccoglierà se ha voglia di occuparsene,se ne ha la capacità, la competenza, e il tempo per farlo :)
Perciò chi offre una risposta ha già impiegato parte del proprio tempo e delle proprie risorse per cercare di aiutare. Non ha "perso tempo".
Sta peraltro a chi pone un quesito mettere gli altri nelle condizioni di poterlo aiutare: la mancanza di codice, di allegati, di spiegazione dettagliata dell'esigenza, del risultato atteso non fanno altro che moltiplicare i post di richiesta delucidazioni e, in questo senso sì, si ha la "perdita di tempo".

di roberto21 (utente non iscritto) data: 16/01/2015 16:45:01
Ci starò più attento, a volte le cose sembrano ovvie a chi fa la domanda, è evidente che chi tenta di rispondere ha bisogno del background. Ho pensato che bastasse il codice, visto che era un errore di runtime, e mi sbagliavo. Scusate e grazie (cenere sul capo).
Il comboBox in questione ha due colonne perchè è rimasto così da una delle mie prove per bypassare l'inconveniente. Visualizzo due colonne nel campo, codice e nome nell'ordine in cui sono, ma il campo alla fine contiene solo il codice, non entrambi i valori, per cui risolverei automaticamente il problema (il campo mantiene solo il primo valore, cioè il codice, che è quello che mi serve). Ora cerco di decidere quale soluzione sia più elegante: la prova con text al posto di value la faccio comunque. Grazie ancora