Sizing dynamic arrays based on data within a worksheet

MarkVMcCullagh

Board Regular
Joined
Oct 22, 2002
Messages
72
I have a worksheet that contains date/time data in Columns A-D
I have managed to load these into a VB static array. Once some calculations have been done the answers are posted back to elsewhere on the worksheet using a second array.

What I would like to do is generate a dynamic array that matches the number of rows of data involved. I am speaking about the first array ie. firstrange. The reason I want to convert this to dynamic rather than static is that it is more efficient and hopefully it won't crash when it encounters a row without data.

For your info columns A and C contain dates and columns B and D contain dates. Basically what I am doing is (C+D)-(A+B) for each row.

This is my first attempt at VB so I know my code will look rough. :p


Sub CategoriseCalls()
Dim firstrange As Variant
Dim secondrange As Variant
Dim i As Long
Dim VarMY As Date
firstrange = Range("a2:d65532").Value ' Select all cells in Column a-d
ReDim secondrange(1 To UBound(firstrange, 1), 1 To 2)
Var4 = 4 / 24 '4 hours
Var6 = 6 / 24 '6 hours
Var8 = 8 / 24 '8 hours
For i = 1 To UBound(firstrange, 1)
If (firstrange(i, 4) + firstrange(i, 3)) - (firstrange(i, 2) + firstrange(i, 1)) <= Var4 Then
secondrange(i, 1) = 4
Else
If (firstrange(i, 4) + firstrange(i, 3)) - (firstrange(i, 2) + firstrange(i, 1)) <= Var6 Then
secondrange(i, 1) = 6
Else
If (firstrange(i, 4) + firstrange(i, 3)) - (firstrange(i, 2) + firstrange(i, 1)) <= Var8 Then
secondrange(i, 1) = 8
Else
secondrange(i, 1) = 9
End If
End If
End If
VarMY = DateValue(Month(firstrange(i, 1)) & "/" & Year(firstrange(i, 1)))
secondrange(i, 2) = VarMY
Next i
Range("j2").Resize(UBound(firstrange, 1), 2).Value = secondrange
MsgBox "Completed"
End Sub
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

TommyGun

MrExcel MVP
Joined
Dec 9, 2002
Messages
4,202
Just check the length of your list before assigning it to an array. Test this to see if it works as needed.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CategoriseCalls()
    <SPAN style="color:#00007F">Dim</SPAN> firstrange()
    <SPAN style="color:#00007F">Dim</SPAN> secondrange()
    
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
        
    <SPAN style="color:#00007F">Dim</SPAN> VarMY <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>

    lastRow = Range("A65536").End(xlUp).Row
    
    firstrange = Range("a2:d" & lastRow).Value <SPAN style="color:#007F00">' Select all cells in Column a-d</SPAN>
    
    <SPAN style="color:#00007F">ReDim</SPAN> secondrange(1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(firstrange, 1), 1 <SPAN style="color:#00007F">To</SPAN> 2)
    
    var4 = 4 / 24 <SPAN style="color:#007F00">'4 hours</SPAN>
    var6 = 6 / 24 <SPAN style="color:#007F00">'6 hours</SPAN>
    var8 = 8 / 24 <SPAN style="color:#007F00">'8 hours</SPAN>

    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(firstrange, 1)
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> (firstrange(i, 4) + firstrange(i, 3)) - (firstrange(i, 2) + firstrange(i, 1))
            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> <= var4: secondrange(i, 1) = 4
            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> <= var6: secondrange(i, 1) = 6
            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> <= var8: secondrange(i, 1) = 8
            <SPAN style="color:#00007F">Case</SPAN> Else: secondrange(i, 1) = 9
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    
        VarMY = DateValue(Month(firstrange(i, 1)) & "/" & Year(firstrange(i, 1)))
        
        secondrange(i, 2) = VarMY
    <SPAN style="color:#00007F">Next</SPAN> i

    Range("j2").Resize(UBound(firstrange, 1), 2).Value = secondrange

    MsgBox "Completed"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
hi!
try this one!
Code:
dim firstrange()
dim tmp as variant
set  tmp= Range("a2:d" & range("a65536").end(xlup).row)
firstrange=tmp.value
This would create an array of size equal to the size of the cell having content.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,256
Messages
5,600,556
Members
414,388
Latest member
Pkmep4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top