Collate Data from csv files to excel sheet

I still see the the "Subscript out of range error". Can you recommend any settings i should do on my excel 2007 to get this working? Because, if it is working there and not in my machine, there might be difference somewhere. I am using windows7 and Excel2007.

Cheers!

Hi

In which line the error occurs ?

Hi,

Script stops at " k(n, j) = d(r, c) " and shows Subscript out of range error. As i see, the script is opening a .csv file randomly from the folder i specify, and the first cell(A1) of that worksheet is active. Also the first cell(A1) in excel sheet is active too. I can send you a recorded webex session if you would like to have a look.

Thanks,
Dhiraj

Hi Dhiraj,

Are you working on the real data or on the sample CSVs you posted here ?

Anyway replace the For Next statement with the following.

Code:

---

For c = 1 To UBound(d, 2)
    If Len(Trim$(d(1, c))) Then
        j = dic.Item(Trim$(d(1, c)))
        On Error Resume Next
        k(n, j) = d(r, c)
        If Err.Number <> 0 Then
            MsgBox "Total Columns so far " & dic.Count & vbLf & _
                    "Counter :" & n & vbLf & _
                    "Current Col:" & j
            Err.Clear: On Error GoTo 0
            Exit Sub
        End If
    End If
Next

---

Let me know what the MsgBox says once it error out.

Hi,

Sorry for late reply. I see the MsgBox as "Total Columns so far 13, Counter : 0, Current Col: 1".
Also i have tried the same script on my original data and also the data which is uploaded earlier. But no luck :(

Thanks,
-Dhiraj

Hi,

It works fine here. I'm not sure why it fails to work at your end. Anyway please find attached and try again.

Wow.. This worked well. I can see the data is collated. But, if we observe the collated file, all the columns of files got collated well but the data is collated as one below the other.

I guess, i will look look further into the script.

Thank you.
-Dhiraj