Transpose undefined arrays

[Davorito]

Hello guys,

I would like to know if it's possible to transpose one worksheet into another. The original worksheet has 200 rows with undefined number of columns.

Checked online and only found operations on same worksheet and i'm not familiar with the excel functions

 

[RoryA]

Yes it's possible. Do you actually mean you want the data transposed, or just copied/moved?

 

[hiker95]

Davorito,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

Because of the size of your raw data worksheet, screenshots will not work/display correctly in the MrExcel display area.

To start off, and, so that we can get it right on the first try:

3. Can we see your raw data workbook/worksheets?

In your workbook, display your raw data worksheet, and, in the results worksheet (manually formatted by you) display what the results should look like.

You can post your workbook/worksheets to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com

 

[Davorito]

Case:


Public Sub Example1()


'dialog result
Dim intDialogResult As Integer


'path selected by user
Dim strPath As String


'single line of data from the text file
Dim strLine As String


'string seperated by the delmiter
Dim arrString() As String


'curren row in excel sheet
Dim i As Integer


Dim FileName As String


'disallow user from selecting multiple files
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False


'remove previous filters
Call Application.FileDialog(msoFileDialogOpen).Filters.Clear


'display the open file dialog
intDialogResult = Application.FileDialog(msoFileDialogOpen).Show


'if the user selected a file
If intDialogResult <> 0 Then

'path selected by the user
strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)


'close the file index #1 if its already opened
Close #1

'open the file for reading
Open strPath For Input As #1



i = 1
'loop while the end of file has not been reached

While EOF(1) = False
'read one line of data from the text file

Line Input #1, strLine

'split string based on delimiter
arrString = Split(strLine, " ")

'I only need the 6th column values
ActiveCell(i, 2) = arrString(6)
i = i + 1

Wend
End If
Close #1


ActiveCell.Offset(0, 1).Activate


End Sub


Basically what it does is it takes values from 6th column from a .txt file and puts them next to each other each time the macro runs.


Problem 1: I want to store them horizontally (up to 200 values in each column from .txt file)


Problem 2: I want to set the first cell as the file name

Yes it's possible. Do you actually mean you want the data transposed, or just copied/moved?

Davorito,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

Because of the size of your raw data worksheet, screenshots will not work/display correctly in the MrExcel display area.

To start off, and, so that we can get it right on the first try:

3. Can we see your raw data workbook/worksheets?

In your workbook, display your raw data worksheet, and, in the results worksheet (manually formatted by you) display what the results should look like.

You can post your workbook/worksheets to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com

 

[hiker95]

Davorito,

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[RoryA]

Basically something like:

ActiveCell.Value = strPath
i = 1
'loop while the end of file has not been reached

While EOF(1) = False
'read one line of data from the text file

Line Input #1, strLine

'split string based on delimiter
arrString = Split(strLine, " ")

'I only need the 6th column values
ActiveCell.Offset(0, i).Value = arrString(6)
i = i + 1

Wend
End If
Close #1


ActiveCell.Offset(0, 1).Activate