VBA Overflow Workaround

[enowapi]

Hi there,


I have a macro that will transpose data from the 'test' tab to the 'result' tab. Data in the 'test' tab are numbers -1000 to 1000 with 5 decimal places. The macro will take the table format in 'test' tab and tranpose into column format that used to be import into databases.

This macro works fine up to 1364 rows of data. But usually my files will contains over 4000 rows of data. And I keep getting an overflow error when running a large file through this macro. I am wondering if there is any workaround for this macro?

[pre]

Here's my code:
---------------------
Dim months(1 To 24) As Double
Dim monthsData(1 To 24) As Double
Dim region(1 To 3) As String

Dim row As Integer
Dim num As Integer
Dim count As Integer
Dim tweleveRow As Integer

row = 2
num = 0

Do
'add numbers of months label to months array
'add each monthly data to monthsData array
For count = 1 To 24
months(count) = Sheets("test").Cells(1, 3 + count)
monthsData(count) = Sheets("test").Cells(row, 3 + count)

'paste numbers of months label in seperate row
'paste each monthly data to associated row
Sheets("result").Cells(24 * num + (1 + count), 4) = months(count)
Sheets("result").Cells(24 * num + (1 + count), 5) = monthsData(count)
Next count

'add regions to region array
'paste region to each row for 12 times
For tweleveRow = 2 To 25
For col = 1 To 3
region(col) = Sheets("test").Cells(row, col)
Next col

For col = 1 To 3
Sheets("result").Cells(24 * num + tweleveRow, col) = region(col)
Next col
Next tweleveRow

row = row + 1
num = num + 1
Loop Until IsEmpty(Sheets("test").Cells(row, 2))

MsgBox "Transpose Completed.  Please check result."

End Sub

[/pre]

 

[SirJB7]

Hi, enowapi!

Would you consider uploading a sample file with the 4K rows of data so as we could avoid creating the dataset just for testing purposes?

Regards!

 

[Hui]

Enowapi


As SirJB7 suggested uploading a sample file will make it a lot easier to deal with

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook


I hasten to add that I think this can be simplified and speeded up massively but will need some data to work with

 

[enowapi]

Thank you SirJB7 and Hui for your help. I was able to get my code working after I change all of my variables to Double. Sorry for the late reply.

 

[SirJB7]

Hi, enowapi!

Better later than never

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!

 

[enowapi]

I recently just be able to get my code working, so just want to post what I've change so if others are reading my initial post, they would know what changed and if it worked.