Funzione in VBA

Hai un problema con Excel?

Nuova discussione

Funzione in VBA
di Alessandro (utente non iscritto) data: 14/10/2012 11:41:40
Salve, sono ancora qui con un mio secondo problema...
Allora la questione è la seguente:

Antefatto (poco interessante, anzi molto noioso...)
Nel foglio "RIEPILOGO" ho una tabella di una colonna dalla cella C5 alla cella C10, in cui nella cella C5 ho una funzione SE che inserisce il contenuto della cella C5 del foglio "ITA-A", nella cella C6 ho la funzione che fa la stessa cosa con la cella C6 e via dicendo. La funzione è semplicemente =SE('ITA-A'!C5="";"";C5), per la cella C5 e =SE('ITA-A'!C6="";"";C6) per la cella C6 etc etc. Il foglio "ITA-A" prende dei dati da un sito web tramite una query. Siccome talvolta quando aggiorno i dati tramite query, mi vengono degli errori e mi cancella la formula (me la ritrasforma come =SE('ITA-A'!RIF#!="";"";RIF#!), ho previsto un tasto che tramite il copia ed incolla mi ripristini la formula corretta. Quindi ho creato un foglio "Foglio1" dove sempre nella cella C5 ho reinserito quella formula di prima (che comunque viene sballata come la precedente quando la query aggiorna male). Siccome ho notato che l'errore di cattivo aggiornamento, me lo fa o sulla prima oppure sull'ultima della tabella (la cella C10) e mai su entrambe contemporaneamete, ho studiato una funzione di correzione =SE(ERRORE.TIPO(C5)<0;C5;C10).

Domanda
Come inserire la formula macro VBA sapendo dunque che nel foglio Foglio1 ho nella cella C5 e nella cella C10 le formule SE di cui parlavo prima (=SE('ITA-A'!C5="";"";C5) & SE('ITA-A'!C10="";"";C10)) e nella cella C6 ho la formula di correzione =SE(ERRORE.TIPO(C5)<0;C5;C10)?

Sperando di essere stato chiaro quel tanto che basta da capire cosa intendo fare...

Private Sub CommandButton6_Click()
' Nel foglio Foglio1 nella cella C6 ho inserito la formula =SE(ERRORE.TIPO(C5)<0;C5;C10)
Sheets("Foglio1").Select
Range("C6").Select
' Questo codice me lo sono inventato sicuramente
' Dovrebbe capire quale delle due formule usare se quella nella cella C5 o nella cella C10
ActiveCell.Formula = "=If(Error.Type(C5)<0;""C5"";""C10"")"
' Qui copia e poi incolla nel foglio RIEPILOGO nelle celle da C5 a C10
Selection.Copy
Sheets("RIEPILOGO").Select
Range("C5:C10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

di Alessandro (utente non iscritto) data: 14/10/2012 11:53:36
Scusate per il doppio messaggio... non funziona lo stesso, ma per maggiore chiarezza la formula corretta è =SE(ERRORE.TIPO(C5)>0;C10;C5), quella precedente non so se mi convinceva anche se è l'esatto opposto di questa...

Private Sub CommandButton6_Click()
' Nel foglio Foglio1 nella cella C6 ho inserito la formula =SE(ERRORE.TIPO(C5)>0;C10;C5)
Sheets("Foglio1").Select
Range("C6").Select
' Questo codice me lo sono inventato sicuramente
' Dovrebbe capire quale delle due formule usare se quella nella cella C5 o nella cella C10
ActiveCell.Formula = "=If(Error.Type(C5)>0;""C10"";""C5"")"
' Qui copia e poi incolla nel foglio RIEPILOGO nelle celle da C5 a C10
Selection.Copy
Sheets("RIEPILOGO").Select
Range("C5:C10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

di Vecchio Frac data: 14/10/2012 12:18:57
A naso:
- ci sono troppe virgolette
- se usi Excel italiano la formula va scritta in italiano
- se usi Excel inglese non ci vuole punto e virgola ma virgola
- la funzione ERRORE.TIPO() vuole un numero di errore oppure un riferimento a una cella che contiene un valore di errore, da valutare, ad esempio SE(ERRORE.TIPO(A2)=3;...;...): consulta la Guida per i valori di errore disponibili
mi riferisco ovviamente a:
ActiveCell.Formula = "=If(Error.Type(C5)>0;""C10"";""C5"")"

Sul resto della logica del programma, è da capire perchè una query remota non deve restituirti i valori corretti (bensì Rif; è come se i valori on fossero più disponibili).

di Alessandro (utente non iscritto) data: 14/10/2012 19:47:26
Aggiornamento del problema:
Innanzitutto ho capito come si devono inserire le formule in VBA (basta sfruttare il registratore e farsele "tradurre" da lui) e dunque quella riga incriminata diventerebbe:
ActiveCell.FormulaR1C1 = "=IF(ERROR.TYPE(R[-2]C[-2])>0,R[3]C[-2],R[-2]C[-2])"
e così la macro parte esegue e finisce il lavoro senza errori... ma non è ciò che mi serve... mi ricopia infatti la formula Se(errore.tipo etc etc spostandone i riferimenti in base alle varie righe e da un solo errore #RIF! ne ottengo tanti quante sono le righe della mia tabella

allora ho provato una stringa del genere
If Range("C5").Select = "#RIF!" Then Range("C10").Select
anche questo codice mi giunge al termine senza darmi errori, ma come prima il risultato non è ciò che volevo ottenere, sempre la sfilza di #RIF! nelle celle della tabella...

ma in pratica potrei stare vicino alla soluzione dell'enigma io vorrei che con quella riga di codice la macro capisse che se trova nella cella C5 un errore (#RIF!) mi deve selezionare la cella C10 e copiare quella non più la C5

riscrivo il codice come l'ho formulato adesso che ripeto funziona senza darmi errore, ma mi visualizza #RIF! in tutte le celle perchè copia la funzione =SE('ITA-A'!#RIF!="";"";#RIF!)

Private Sub CommandButton6_Click()
' Nel foglio Foglio1 non specifico più quale cella selezionare in partenza perchè vorrei che scegliesse tra le due possibili
Sheets("Foglio1").Select
If Range("C150").Select = "#RIF!" Then Range("C159").Select
' Qui copia e poi incolla nel foglio RIEPILOGO nelle celle da C5 a C10
Selection.Copy
Sheets("RIEPILOGO").Select
Range("C5:C10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

di Vecchio Frac data: 14/10/2012 20:40:21
A me sembra un ripiego, più che una soluzione :)
Comunque in VBA puoi testare se una cella contiene un valore di errore con l'istruzione IsError.
Supponi di avere una formula che dà errore in A1, ad esempio =6/0
In VBA, l'istruzione "IsError([a1])" restituisce True.

Per aderire al tuo codice:
If IsError([C150]) Then [C159].Select
di HarryBosch data: 15/10/2012 00:59:05
Come dice VecchioFrac, anche a me sembra un ripiego...
Visto che usi il vba, invece di controllare se la formula ti restituisce errore o peggio ancora, rischia di cancellarsi perché sovrascritta da dati in importazione, perché dopo la query non calcoli direttamente con il vba_style ( ) quello che ti serve? E quindi riporti solo il valore.

Per esempio, dalla cella C5 alla cella C10 una cosa del tipo sotto;
resta solo da capire che formula usi e come trascriverla
For i = 5 to 10 Sheets("ITA-A").cells(i, 3) = "Tua_Formula" next i

di Alessandro (utente non iscritto) data: 15/10/2012 10:34:39
Grazie per le risposte, ma diciamo che ho risolto stravolgendo ulteriormente la situazione con il codice che riporto sotto. Praticamente funziona per quello che desideravo facesse, cioè nel Foglio1 seleziona la cella C5, mi scrive la formula =Se('ITA-A'!$C$5="","";'ITA-A'!$C$5) in quella cella in modo assoluto (ho fatto così perchè altrimenti mi sballava le risultanze), poi cancella i segni di $, quindi me la ricopia nelle celle da C5 a C10 del RIEPILOGO. Diciamo che io essendo all'inizio, ma veramente all'inizio, cerco di giungere all'obbiettivo prefissato anche percorrendo le stradine laterali...

Piuttosto non per stressarvi, ma come notava giustamente Vecchio Frac:
[citazione]Sul resto della logica del programma, è da capire perchè una query remota non deve restituirti i valori corretti (bensì Rif; è come se i valori on fossero più disponibili).
Ho capito il problema, dunque la query che usi mi importa una tabella di 6 colonne e 10 righe, la query è impostata su mantiene la formattazione del foglio e NON allargare le colonne in base al contenuto, perchè le ho dimensionate io per come mi servono. Alle volte quando aggiorno i dati (soprattutto la sera), la tabella sul sito web è composta solo da sempre 6 colonne, ma da 1 a 5 o 6 righe, ed è in quel caso che talvolta mi sballa le formule che sono poste alla destra della query, dividendo i dati di una riga su due righe anche non sottostanti l'una all'altra (per esempio una riga che è sempre l'ultima sul sito web da cui importo la query, viene spezzata in due parti e i primi dati vengono inseriti nella 5a riga della mia tabella, poi la 6a riga e la 7a riga mi sballa le formule perchè rimangono bianche (ecceziona fatta per l'errore sulle formule) e nell'8a riga mi pone la seconda metà dei dati... sembrerebbe quasi come se volesse riempire per forza l'intero contenuto della mia tabella, ma non avendo dati sufficienti li spezzetta e li ridistribuisce

adesso vi riempio di domande (come se non l'avessi già fatto anche prima...)
1) Esiste un modo per fare che la query quando si aggiorna non stravolga il formato della mia tabella?
2) Esiste un modo per non far vedere o anche solo per importare non tutte e 6 le colonne dal sito web, ma per esempio solo le prime 4? (gli altri dati non mi interessano).
3) Esiste un modo (macro o altro) per creare un elenco dei link delle query presenti all'interno di un foglio excel, anziche doversele spulciare una per una tramite le proprietà del menù dati - connessioni?

Grazie per l'infinita pazienza.

Sheets("Foglio1").Select
Range("C5").Select
ActiveCell.FormulaR1C1 = "=IF('ITA-A'!R5C3="""","""",'ITA-A'!R5C3)"
Cells.Replace What:="$", Replacement:=""
Selection.Copy
Sheets("RIEPILOGO").Select
Range("C5:C14").Select
ActiveSheet.Paste

di Vecchio Frac data: 15/10/2012 13:17:38
Prima di analizzare, con pazienza, l'intero problema, ti faccio notare che è molto poco ortodosso sostituire il $ nelle formule come hai fatto tu...
Pertanto ti suggerisco questo metodo, certamente molto più excelliano ^_^
In pratica, e come giustamente richiedi, utilizzo un riferimento relativo alla cella attuale. Solitamente si inseriscono i valori di riga e colonna tra parentesi quadre per indicare lo scostamento dalla cella attuale. In questo caso coincidono con zero e si possono omettere. Tuttavia la formula genera, giustamente, un riferimento circolare.
[C5].FormulaR1C1 = "=IF('ITA-A'!RC="""","""",'ITA-A'!RC)"

1) Nel menu "importa dati esterni" ci sono le opzioni per la formattazione e il layout. Avvia un registratore di macro, togli la spunta alla formattazione automatica, quindi analizza il codice e recupera solo le righe che ti interessano; in particolare:
With Selection.QueryTable
.PreserveFormatting = False
.AdjustColumnWidth = False
End With

2) anche qui, avvia un registratore di macro, esegui manualmente queste operaiozni, ferma il registratore e analizza il codice

3) non lo so. Come detto altrove, il mio Excel 2003 non fornisce la proprietà Connection per ThisWorkbook.
di HarryBosch data: 15/10/2012 13:58:03
- Per quanto riguarda il punto 3), con Excel 2007 puoi ricavarti il nome delle connessioni presenti, con un codice del tipo sotto; ovviamente l'uso è da adattare alla propria esigenze.

- Per quanto riguarda il punto 2, che credo non sempre di facile risoluzione in fase di importazione (almeno a livello personale), dovresti verificare la proprietà ".Web Tables = ...", per stabilire le tabelle da importare.
Di solito io importo la tabella completa, ed aggiungo dopo la query qualche riga di codice per modificarla a piacere, eliminando successivamente le colonne o le righe inutili
Sub connessioni_presenti() Dim cn As Variant Dim x As String For Each cn In ThisWorkbook.Connections x = cn.Name Next End Sub

Vuoi Approfondire?