Problem with error type mismatch

[aaronreyna]

So several months ago, I managed to, with the help of other on this board, get a working version of a code to find the percentile of a list of XYZ files. Since that time, I went back to open up the code agan, and it now gives me an error type mismatch.

I suppose I am a little confused because the cell values that I write to text are in fact Long values, but every time I try and run the code, it breaks and tells me that I have defined the wrong type of variable. (i.e. excel seems to think that a long value is not a long value).

[VBA]Sub run_Click()
Application.ScreenUpdating = False
Starttime = Timer
'Clear Contents
Range("A2:B20000").ClearContents '"A2:B100000"
Dim i As Long, nd As Long, MaxBuilding As Long, MinBuilding As Long
Dim File_name As String, inc As Long, j As Long, volume As Long
Dim TempDens() As Double
inc = 1
'For j = 0 To inc
Do
ReDim TempDens(1 To 2, 1 To 1) 'ReDim TempDens(1 To 2, 0 To 0)
If Sheet1.Cells(inc, 17).Value = "" Then Exit Do
File_name = Sheet1.Cells(inc, 17).Value
'fetch data from the file
Open File_name For Input As #1
Do
If EOF(1) Then Exit Do
nd = nd + 1
ReDim Preserve TempDens(1 To 2, 1 To nd) 'ReDim Preserve TempDens(1 To 2, 0 To nd)
' Input #1, Temp(nd), Dens(nd)
Input #1, TempDens(1, nd), TempDens(2, nd)
Loop
Close #1
'Display values on the worksheet
With Sheets("Export_Output1")
.Range("A2").Resize(nd, 2) = Application.Transpose(TempDens)
nd = 0
Erase TempDens
'create a file
'Open "c:\z_unit_write.txt" For Append As #2
'Open "C:\Documents and Settings\Pascal\Desktop\z_unit_write.txt" For Append As #2
Open "c:\Quad_45122E6105.txt" For Append As #2
MaxBuilding = .Range("h12")
MinBuilding = .Range("k12")
volume = .Range("m13")
Write #2, MaxBuilding, MinBuilding, volume, File_name
Close #2
'clear Contents
.Range("A2:B20000").ClearContents 'A2:B100000
End With
inc = inc + 1
Loop
'Next j
MsgBox (Timer - Starttime)
Application.ScreenUpdating = True
Stop
End Sub[/VBA]

It always breaks in the same place [VBA]MaxBuilding = .Range("h12")[/VBA]

That cell value is always an integer since the file it reads in is an integer.

Why would this be?


aaron

attached is my woorkbook.

[Bob Phillips]

It's a bit dfficult to test it without any of the appropriate text files to work with.

[aaronreyna]

HA HA HA! Sorry about that! I will attach it to the original thread. So the files are read in on the right hand side of the workbook under the Q column, I believe. The script just iterates through each and loops untill it is finished reading each one in. To make it faster, I tured off auto calc and turn on auto save to update the wb.

aaron

*EDIT: it has been uploaded under Buildings.zip

[p45cal]

On your spreadsheet, H12 has a Percentile formula in, which has a result in the range 0 to 1, (and that cell is formatted as General, so that doesn't reduce it to a Long either) therefore it can't be a long (an integer with a wider range of possible values than the integer data type).

Don't Dim that variable as Long, use Single or Double or leave it as a Variant (undefined).

[aaronreyna]

p45cal,

I tried running MaxBuilding, MinBuilding and Volume through as integer, single, and double with the same result. I even moved the cell it copies out to text as the one right below Min and Max on the spreadsheet so as to not directly copy that formula out (in row 13). Maybe I am missing something?

thanks,

aaron

[aaronreyna]

HA! Thanks P45cal

I juts added another lind to my code, DIMed the max, min, and volume as a double and... PRESTO! it worked. I owe you a e-beer!

aaron