Transformer un tableau de X colonnes en 1 colonne

Le 14/04/2015 à 09:41

Bonjour bonjour !

Je suis encore une novice dans VBA, je rencontre quelques problèmes...

Afin d'injecter un "accélérogramme" dans un logiciel, j'ai besoin de trier un tableau (de 8 colonnes pour cet exemple) en une seule colonne.

Il y a également du texte au dessus où j'aimerai récupérer des informations, mais il n'est pas utile pour transofmer le tableau en une seule colonne.

Ci dessous vous pouvez trouver un exemple (qui n'est pas complet au niveau des lignes, mais c'est pour avoir un apercu), et j'aimerai trier dès lors que l'on a la petite legende

" 12000 points of accel data equally spaced at .010 sec, in cm/sec2. (8f10.5) "

Je sais pas si vous m'avez bien compris, mais en tout cas je vous remercie d'avance

(Je precise également que c'est un fichier texte dans lequel sont ces informations)

Uncorrected Accelerogram Data

L'Aquila Italy Earthquake, 6 April 2009 RAN-DPC Italy Network Record

CSMIP Preliminary Processing (Origin: To be Determined)

88004-K3915-09 Start time: 04/06/09, 01:32:12.0 UTC (GPS)

Station No. 88004 42.345N, 13.401E K2 s/n 3915 (3 Channels)

Aquil PARK ing. RAN

Chan 1: 90 Deg

Record of Mon Apr 06, 2009 01:32:12.0 UTC Mon Apr 06, 2009 01:32:12.0 UTC

Hypocenter: To be determined. ML: To be determined.

Instr Period = .0047 sec, Damping = .700, Sensitivity = 2.50 v/g (Nominal)

No. of Points = 24000 Record Length =119.995 sec At 200 Samples/sec

Max = .330 , at 33.245 sec.

RMS calc for complete record = .

1 100 1 3 3 200 1 3 1 24 11 3915 088004 4 0

1 32 12 0 96 4 6 2009 10 3 9024000 41 40 76 360

24000 1 50 99 3915 1 43 0 1 0 0 0 0 0 0 0

0 4 0 0 0 0 0 0 18 4 76 40 1 1 0 0

3 0 2 0 0 3 5 1 0 0 3 0 0 3 0 0

0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0

0 0 0 0

.0047170 .7000000 119.99500 .0320439 1.0000000 2.4980000 .3303421 33.245000

212.00000 .000 .000 .000 5.0000000 .000 4.0000000 .000

2.5000000 -.1357398 2.5000000-999.00000 30.000000 30.000000 .000 .0509257

-1.1972310 .000 2.0000000 15.000000 42.344970 13.400950 .0042145 60.885740

.000 .000 .000 726.00000 .000 .000 .000 .000

1.0000000 .000 .000 .000 .000 .000 .000 .000

.000 .000

24000 Accelerogram points at 200 pts/sec in units of g . Format: (8f9.6)

.000190 .000182 .000187 .000181 .000183 .000187 .000184 .000191

.000184 .000182 .000184 .000185 .000190 .000185 .000192 .000187

.000184 .000184 .000180 .000197 .000182 .000184 .000203 .000183

.000190 .000183 .000185 .000200 .000182 .000193 .000189 .000184

.000186 .000185 .000190 .000187 .000192 .000184 .000188 .000186

.000182 .000189 .000189 .000192 .000182 .000187 .000189 .000185

.000187 .000185 .000191 .000188 .000184 .000185 .000189 .000186

.000180 .000186 .000189 .000185 .000181 .000185 .000190 .000188

.000185 .000188 .000190 .000186 .000195 .000188 .000185 .000192

.000185 .000181 .000182 .000189 .000181 .000183 .000193 .000185

.000191 .000189 .000179 .000191 .000195 .000186 .000188 .000187

.000187 .000192 .000193 .000192 .000185 .000186 .000190 .000186

.000184 .000189 .000186 .000189 .000188 .000182 .000198 .000186

.000183 .000196 .000188 .000192 .000186 .000185 .000190 .000188

.000188 .000185 .000186 .000191 .000191 .000186 .000191 .000186

.000185 .000184 .000174 .000192 .000190 .000182 .000195 .000179

.000188 .000196 .000186 .000197 .000186 .000190 .000190 .000184

.000192 .000188 .000192 .000188 .000183 .000183 .000189 .000190

.000185 .000191 .000189 .000186 .000183 .000186 .000188 .000185

.000188 .000183 .000178 .000190 .000185 .000186 .000190 .000188

.000189 .000181 .000186 .000181 .000187 .000190 .000187 .000195

.000182 .000187 .000192 .000187 .000187 .000189 .000188 .000185

.000192 .000188 .000185 .000187 .000181 .000190 .000189 .000183

.000192 .000190 .000189 .000190 .000185 .000183 .000187 .000186

.000185 .000185 .000189 .000186 .000181 .000186 .000181 .000185

.000188 .000184 .000188 .000182 .000186 .000190 .000182 .000185

.000179 .000182 .000192 .000186 .000190 .000189 .000192 .000189

.000181 .000188 .000181 .000191 .000187 .000182 .000198 .000189

.000189 .000187 .000185 .000184 .000185 .000189 .000184 .000195

.000189 .000182 .000192 .000185 .000182 .000183 .000188 .000187

.000186 .000192 .000179 .000181 .000187 .000186 .000189 .000192

.000183 .000181 .000190 .000180 .000195 .000183 .000178 .000197

.000177 .000185 .000188 .000186 .000193 .000185 .000189 .000184

.000184 .000183 .000179 .000186 .000189 .000185 .000188 .000183

.000187 .000197 .000183 .000187 .000189 .000184 .000180 .000186

.000193 .000186 .000190 .000182 .000182 .000185 .000187 .000191

.000180 .000183 .000184 .000186 .000187 .000186 .000184 .000183

.000192 .000182 .000193 .000195 .000183 .000193 .000186 .000194

.000190 .000177 .000190 .000193 .000189 .000184 .000190 .000189

.000186 .000189 .000187 .000188 .000183 .000191 .000186 .000178

.000190 .000184 .000182 .000188 .000186 .000186 .000186 .000186

.000186 .000184 .000184 .000183 .000178 .000182 .000192 .000189

.000183 .000183 .000189 .000190 .000181 .000185 .000186 .000183

.000187 .000181 .000189 .000183 .000183 .000201 .000179 .000181

.000186 .000180 .000187 .000186 .000190 .000186 .000189 .000186

.000187 .000190 .000184 .000188 .000188 .000189 .000188 .000187

.000185 .000185 .000185 .000190 .000185 .000179 .000188 .000180

.000185 .000186 .000176 .000189 .000185 .000188 .000190 .000188

.000190 .000181 .000190 .000187 .000184 .000185 .000181 .000189

.000179 .000185 .000192 .000188 .000190 .000182 .000187 .000190

.000184 .000187 .000185 .000186 .000185 .000184 .000188 .000182

.000186 .000182 .000189 .000193 .000179 .000190 .000188 .000187

.000185 .000187 .000188 .000185 .000190 .000182 .000190 .000189

.000179 .000184 .000180 .000183 .000183 .000187 .000191 .000177

.000185 .000186 .000181 .000193 .000180 .000182 .000190 .000178

.000186 .000185 .000179 .000190 .000189 .000182 .000193 .000189

.000183 .000190 .000179 .000186 .000189 .000184 .000193 .000184

.000185 .000189 .000190 .000190 .000181 .000192 .000195 .000187

.000186 .000190 .000190 .000186 .000186 .000188 .000191 .000184

.000183 .000188 .000183 .000188 .000189 .000183 .000180 .000188

.000196 .000184 .000185 .000189 .000183 .000193 .000186 .000179

.000186 .000184 .000190 .000186 .000184 .000195 .000186 .000184

.000193 .000182 .000187 .000189 .000184 .000190 .000183 .000182

.000187 .000185 .000187 .000189 .000186 .000188 .000192 .000187

.000185 .000195 .000184 .000181 .000183 .000183 .000196 .000186

.000185 .000191 .000193 .000184 .000180 .000193 .000178 .000185

.000187 .000185 .000192 .000186 .000193 .000185 .000188 .000195

.000191 .000194 .000185 .000185 .000188 .000186 .000186 .000190

.000194 .000186 .000187 .000188 .000178 .000184 .000192 .000192

.000189 .000189 .000186 .000181 .000189 .000186 .000185 .000187

.000185 .000186 .000186 .000190 .000186 .000185 .000189 .000191

.000188 .000186 .000190 .000188 .000190 .000192 .000184 .000185

.000190 .000178 .000188 .000187 .000180 .000200 .000187 .000186

.000189 .000184 .000192 .000181 .000186 .000183 .000178 .000197

.000186 .000186 .000188 .000182 .000191 .000181 .000188 .000185

.000179 .000195 .000191 .000194 .000187 .000184 .000190 .000184

.000189 .000186 .000181 .000185 .000181 .000181 .000188 .000187

.000184 .000182 .000190 .000190 .000182 .000188 .000190 .000188

.000190 .000180 .000186 .000194 .000182 .000185 .000192 .000185

.000182 .000189 .000181 .000187 .000193 .000184 .000183 .000181

.000185 .000178 .000188 .000190 .000183 .000189 .000181 .000187

.000190 .000184 .000182 .000188 .000186 .000184 .000194 .000186

.000190 .000190 .000186 .000190 .000190 .000188 .000188 .000195

.000183 .000180 .000189 .000185 .000188 .000185 .000186 .000187

.000183 .000193 .000189 .000183 .000190 .000186 .000184 .000185

.000189 .000191 .000190 .000189 .000181 .000186 .000196 .000187

.000189 .000194 .000190 .000184 .000185 .000192 .000185 .000187

.000190 .000187 .000189 .000189 .000188 .000185 .000185 .000186

.000187 .000192 .000187 .000185 .000192 .000187 .000184 .000188

.000187 .000188 .000189 .000189 .000186 .000187 .000191 .000186

.000186 .000190 .000187 .000184 .000182 .000187 .000183 .000188

.000191 .000186 .000189 .000182 .000183 .000190 .000190 .000193

.000180 .000185 .000192 .000188 .000189 .000181 .000192 .000190

.000186 .000184 .000187 .000192 .000173 .000183 .000184 .000186

.000192 .000189 .000196 .000184 .000184 .000187 .000187 .000192

.000188 .000192 .000194 .000185 .000185 .000188 .000192 .000195

.000182 .000192 .000193 .000187 .000186 .000183 .000188 .000183

.000187 .000186 .000190 .000187 .000186 .000194 .000184 .000193

.000186 .000183 .000193 .000182 .000186 .000186 .000195 .000189

.000184 .000192 .000190 .000186 .000174 .000184 .000184 .000182

.000188 .000185 .000190 .000184 .000185 .000185 .000184 .000186

.000186 .000191 .000184 .000190 .000192 .000181 .000193 .000194

.000185 .000183 .000181 .000192 .000188 .000179 .000187 .000190

.000182 .000185 .000188 .000183 .000187 .000187 .000186 .000185

.000183 .000189 .000190 .000187 .000192 .000190 .000185 .000187

.000182 .000187 .000191 .000187 .000185 .000183 .000187 .000189

.000191 .000187 .000186 .000189 .000189 .000183 .000184 .000190

.000185 .000186 .000187 .000191 .000187 .000186 .000188 .000184

.000187 .000182 .000186 .000185 .000178 .000184 .000186 .000185

.000181 .000189 .000187 .000184 .000194 .000189 .000188 .000183

.000183 .000187 .000187 .000191 .000176 .000186 .000192 .000179

.000190 .000178 .000185 .000191 .000183 .000191 .000185 .000187

.000184 .000187 .000185 .000185 .000190 .000183 .000190 .000185

.000184 .000194 .000186 .000186 .000188 .000184 .000189 .000182

.000182 .000189 .000180 .000183 .000191 .000186 .000188 .000190

.000184 .000188 .000180 .000187 .000192 .000182 .000193 .000190

.000185 .000186 .000190 .000188 .000184 .000187 .000183 .000188

.000178 .000185 .000193 .000181 .000194 .000190 .000191 .000189

.000183 .000195 .000181 .000185 .000186 .000179 .000194 .000177

.000182 .000195 .000185 .000189 .000187 .000191 .000192 .000183

.000189 .000188 .000190 .000189 .000184 .000195 .000185 .000180

.000190 .000185 .000184 .000190 .000192 .000186 .000190 .000187

.000183 .000190 .000182 .000189 .000191 .000187 .000189 .000179

.000187 .000187 .000186 .000185 .000182 .000190 .000183 .000191

.000186 .000184 .000193 .000187 .000193 .000184 .000183 .000186

.000182 .000187 .000183 .000185 .000184 .000190 .000194 .000186

.000182 .000185 .000188 .000188 .000182 .000179 .000189 .000186

.000189 .000189 .000182 .000187 .000182 .000183 .000184 .000189

.000189 .000191 .000190 .000179 .000185 .000186 .000191 .000188

.000187 .000191 .000181 .000189 .000185 .000178 .000190 .000188

.000188 .000184 .000183 .000194 .000185 .000178 .000188 .000186

.000187 .000181 .000185 .000193 .000176 .000189 .000191 .000181

.000188 .000186 .000190 .000188 .000186 .000186 .000184 .000186

.000185 .000188 .000183 .000185 .000191 .000184 .000188 .000189

.000182 .000190 .000183 .000186 .000190 .000190 .000187 .000175

.000193 .000188 .000183 .000192 .000184 .000182 .000183 .000195

.000192 .000190 .000190 .000189 .000188 .000179 .000194 .000188

.000183 .000193 .000184 .000183 .000190 .000186 .000186 .000191

.000190 .000191 .000188 .000187 .000189 .000186 .000188 .000183

.000186 .000186 .000189 .000194 .000178 .000189 .000184 .000181

.000191 .000185 .000192 .000184 .000185 .000188 .000191 .000189

.000183 .000188 .000184 .000186 .000186 .000187 .000181 .000191

Le 14/04/2015 à 09:59

Bonjour,

la phrase " 12000 points of accel data equally spaced at .010 sec, in cm/sec2. (8f10.5) " ne se trouve pas dans l'exemple que tu as donné.

doit-on considérer que les données sont déjà chargées sur une feuille excel (laquelle) ?

Le 14/04/2015 à 10:01

Désole j'avais regardé un autre fichier (cette ligne change selon les fichiers, car elle donne le nombre de points de l'accélérogrammes (donc le nombre de lignes))...

Ici c'est la ligne "24000 Accelerogram points at 200 pts/sec in units of g . Format: (8f9.6)"

Les cellules se lisent également de gauche à droite...

J'aimerai bien pouvoir ouvrir le fichier texte qui comprend toutes ces données, mais je pense que c'est trop compliqué pour moi...

Donc disons que l'on copie-colle le fichier texte dans la première feuille excel

Le 14/04/2015 à 10:23

Bonjour,

voici une version qui suppose que le fichier texte est déjà ouvert et chargé. sélectionner la feuille avec les données et lancer la macro "test" par alt-F8

Sub test()
    Set re = Range("A:A").Find("Accelerogram", lookat:=xlPart)
    If re Is Nothing Then MsgBox "text Accelerogram non trouvé": Exit Sub
    pl = re.Row + 1 'première ligne de données
    dl = Cells(Rows.Count, 1).End(xlUp).Row 'dernière ligne de données
    destl = dl ' 1ere ligne de destination des données pour les colonnes à copier
    Range(Cells(pl, 1), Cells(dl, 1)).TextToColumns Destination:=Cells(pl, 1), DataType:=xlFixedWidth ' on sépare les données en colonnes
    For i = 2 To 8 'on copie les colonnes sous la colonne A
        Range(Cells(pl, i), Cells(dl, i)).Copy Cells(destl + 1, 1)
        destl = Cells(Rows.Count, 1).End(xlUp).Row
    Next i
    Range(Cells(pl, 2), Cells(dl, 8)).Delete shift:=xlToLeft ' on supprime les colonnes inutiles
    Range(Cells(pl, 1), Cells(destl, 1)).Sort key1:=Cells(pl, 1), order1:=xlAscending, Header:=xlNo ' on trie les données en colonne A
End Sub
Le 14/04/2015 à 10:36

Merci beaucoup c'est super !

Tu viens de me sauver !

Bonne continuation et encore merci !

Rechercher des sujets similaires à "transformer tableau colonnes colonne"