I wrote some VBA code for Excel on a desktop. I tried to transfer it to a laptop but the ListBox keeps changing size. On startup the macro goes through all the sheets except for certain named ones. Then it goes row by row and if the cell in column 9 is blank it puts the value in the first column from that row into an array. When a sheet is done it goes to the sheet "dat" and puts the value, sheet name, and combo of the two in adjacent columns. For example: data "10" from Sheet "2011" gives a row with three values of "10" "2011" "10, 2011". The ListBox populates from the third column. But when it runs the ListBox resizes to height 15728625.75 and width 786245.25. I cannot see anything wrong and it works on the desktop. Does anyone know what is wrong?
For the record it is a Dell desktop running Vista and a HP laptop running Windows 7. Both using Excel 2007.
Code:
Private Sub Workbook_Open()
Sheets("dat").Visible = True
Sheets("dat").Select
Range(Columns(1), Columns(3)) = ""
c = Sheets.Count
For Z = 1 To c
If Sheets(Z).Name = "Sheet1" Then GoTo line1
If Sheets(Z).Name = "dat" Then GoTo line1
If Sheets(Z).Name = "bunker" Then GoTo line1
Sheets(Z).Select
a = Application.WorksheetFunction.CountA(Columns(1))
b = Application.WorksheetFunction.CountA(Columns(9))
If a - b < 1 Then GoTo line1
ReDim q(a - b) As Integer
inc = 0
For x = 2 To a
If Cells(x, 9) = "" Then q(inc) = Cells(x, 1): inc = inc + 1
Next x
Sheets("dat").Select
v = Application.WorksheetFunction.CountA(Columns(1))
For y = 1 To inc
Cells(v + y, 1) = q(y - 1)
Cells(v + y, 2) = Sheets(Z).Name
Cells(v + y, 3) = Cells(v + y, 2) & ", " & Cells(v + y, 1)
Next y
line1: 'jump out
Next Z
Sheets("dat").Visible = False
Sheets("Sheet1").Select
Cells(1, 1).Select
End Sub
For the record it is a Dell desktop running Vista and a HP laptop running Windows 7. Both using Excel 2007.