VBA two dynamic ranges, different sheets--insert problem using arrays

C

ChrisCom

New Member
Joined
Jan 11, 2015
Messages
6
Hello everyone, i'm new to the group posting, but not new reading all the great solutions. I've been working on this insertion of data for while now and cannot get it. In the past i've created a bunch of do and if then's to make it work, but it was not an array and it literally took over an hr to run. I want to run this using dynamic array's, but cannot figure out why the array keeps messing up.

What i've gotten to work in the past is to check the time of the production item created, compared it to the alarm file times. If a product was made before the alarm, insert a blank row above the alarm line, and fill with a product and a few zero's on the other columns to show it was a product, not an alarm. On the other side, if an alarm time is before the product, move down one row, keep the alarm line and time intact, and recheck to see if a product was made, keep looping until there's another product made, then repeat teh insertion of the product.

What I have:
1-An equipment alarm file that's produced every 24 hours (length changes daily), that captures alarms of that particular machine.
2-A production file that records said production from that machine. But creates production between alarms (and sometimes will produces while an alarm is active. This production number range is also dynamic.

First shown is the excel alarm file range, I use column C as my compare for the production time.
Excel 2010
ABCDEF
1DateTimeStart Date TimeEnd Date TimeTagNameMessage Number
212/8/201400:11:3312/8/14 12:11 AM12/8/14 12:11 AMLine_C\B4Alm\B4M_039B4M_039
312/8/201400:23:4312/8/14 12:11 AM12/8/14 12:11 AMLine_C\B4Alm\B4M_021B4M_021
412/8/201400:23:4812/8/14 12:23 AM12/8/14 12:32 AMLine_C\B4Alm\B4M_039B4M_039
512/8/201400:29:3912/8/14 12:23 AM12/8/14 12:24 AMLine_C\B4Alm\B4M_021B4M_021
612/8/201400:37:1312/8/14 12:29 AM12/8/14 3:38 AMLine_C\B4Alm\B4M_188B4M_188
712/8/201400:39:5612/8/14 12:37 AM12/8/14 12:39 AMLine_C\Alpa\A3M_154A3M_154
812/8/201400:41:4912/8/14 12:39 AM12/8/14 12:41 AMLine_C\Alpa\A3M_143A3M_143
912/8/201400:42:1412/8/14 12:41 AM12/8/14 12:41 AMLine_C\Alpa\A3M_143A3M_143
1012/8/201400:42:3512/8/14 12:42 AM12/8/14 12:42 AMLine_C\Alpa\A3M_143A3M_143
1112/8/201400:47:5412/8/14 12:42 AM12/8/14 12:42 AMLine_C\Alpa\A3M_143A3M_143
1212/8/201401:01:4412/8/14 12:47 AM12/8/14 12:49 AMLine_C\Alpa\A3M_150A3M_150
1312/8/201401:54:5512/8/14 1:01 AM12/8/14 1:43 AMLine_C\B4Alm\B4M_016B4M_016
1412/8/201402:08:2912/8/14 1:54 AM12/8/14 1:56 AMLine_C\Alpa\A3M_154A3M_154

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Baler1
​




Next is the production that's produced for that same time frame. For now its just column A, position 1. But normally it would be column C, position 7 in a different workbook (I can handle the correct location of this array later).

Excel 2010
A
1Timestamp
212/8/14 12:00 AM
312/8/14 12:03 AM
412/8/14 12:04 AM
512/8/14 12:05 AM
612/8/14 12:06 AM
712/8/14 12:07 AM
812/8/14 12:08 AM
912/8/14 12:09 AM
1012/8/14 12:10 AM
1112/8/14 12:11 AM
1212/8/14 12:12 AM
1312/8/14 12:13 AM
1412/8/14 12:15 AM
1512/8/14 12:16 AM
1612/8/14 12:17 AM
1712/8/14 12:18 AM
1812/8/14 12:19 AM
1912/8/14 12:20 AM
2012/8/14 12:21 AM
2112/8/14 12:22 AM
2212/8/14 12:23 AM
2312/8/14 12:24 AM
2412/8/14 12:26 AM
2512/8/14 12:27 AM
2612/8/14 12:28 AM
2712/8/14 12:29 AM
2812/8/14 12:30 AM
2912/8/14 12:31 AM
3012/8/14 12:32 AM
3112/8/14 12:33 AM

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>
BaleCreate1​




Here's what the data is supposed to look like when it's completed--ignore the fact that this alarm and production data was on the 14th.
Excel 2010
ABCDEFG
1DateTimeStart TimeEnd Alarm TimeAlarm Message
21/14/201400:00:031/14/14 12:00 AM01/14/14 00:000
31/14/201400:02:031/14/14 12:02 AM01/14/14 00:020
41/14/201400:02:351/14/14 12:02 AM1/14/14 12:03 AMLine_C\B4Alm\B4M_124
51/14/201400:07:031/14/14 12:07 AM01/14/14 00:070
61/14/201400:08:031/14/14 12:08 AM01/14/14 00:080
71/14/201400:09:001/14/14 12:09 AM1/14/14 12:09 AMLine_C\B4Alm\B4M_124
81/14/201400:10:031/14/14 12:10 AM01/14/14 00:100
91/14/201400:13:031/14/14 12:13 AM01/14/14 00:130
101/14/201400:15:031/14/14 12:15 AM01/14/14 00:150
111/14/201400:17:031/14/14 12:17 AM01/14/14 00:170
121/14/201400:38:031/14/14 12:38 AM01/14/14 00:380

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3​



Now for the code (I've ' out some lines to just work on this in one file, the actual location of the data source is ' out):
/code
Sub Array_Test()
Dim AlarmArray As Variant
Dim BaleCreateArray As Variant
Dim i As Long

' Alarmcellcount = Sheets("Baler1").Range("A1").Offset(Sheets("Baler1").Rows.Count - 1, 0).End(xlUp).Row
' Balecellcount = Sheets("BaleCreate1").Range("A1").Offset(Sheets("BaleCreate1").Rows.Count - 1, 0).End(xlUp).Row

Sheets("Baler1").Select
AlarmArray = Sheets("Baler1").Range("C1:C3000") 'Sheets("Baler1").Range(Cells(1, Alarmcellcount), Cells(Alarmcellcount, 1)).Value
With Sheets("Baler1")
AlarmArray = (Sheets("Baler1").Range("A" & .Rows.Count).End(xlUp).Row)
End With

Sheets("BaleCreate1").Select


BaleCreateArray = Sheets("BaleCreate1").Range("A1:A3000")
With Sheets("BaleCreate1")
BaleCreateArray = (Sheets("BaleCreate1").Range("A" & .Rows.Count).End(xlUp).Row)
End With
i = 2

For i = 2 To UBound(AlarmArray, 1)

Sheets("Baler1").Select
Cells(i, 1).Activate
If AlarmArray(i, 1) = "" And BaleCreateArray(i, 1) = "" Then
Exit Sub
ElseIf AlarmArray(i, 1) > BaleCreateArray(i, 1) And AlarmArray(i, 1) <> BaleCreateArray(i, 1) Then

If i = 2 And Cells(i, 7).Value <> 0 Then
ActiveCell.Resize(1).EntireRow.Insert
Cells(i, 1).Value = Sheets("WetEndInfo").Cells(3, 6).Value
Cells(i, 3).Value = Sheets("BaleCreate1").Cells(i, 1).Value
Cells(i, 4).Value = Sheets("Baler1").Cells(i, 3).Value + 1 / 1520
Cells(i, 1).Value = Workbooks(binsheet).Sheets("WetEndInfo").Cells(3, 6).Value
Range(Cells(i, 5), Cells(i, 9)).Value = 0
Cells(i, 28).Value = 1
Cells(i, 30).Value = 0 'Workbooks(binsheet).Sheets("Baler1Counts").Cells(h, 11).Value
Cells(i, 31).Value = 0 'Workbooks(binsheet).Sheets("Baler1Counts").Cells(h, 12).Value
Cells(i, 32).Value = 0 'Workbooks(binsheet).Sheets("Baler1Counts").Cells(h, 13).Value
Cells(i, 33).Value = 0 'Workbooks(binsheet).Sheets("Baler1Counts").Cells(h, 14).Value
Cells(i, 34).Value = 0 'Workbooks(binsheet).Sheets("Baler1Counts").Cells(h, 15).Value
Cells(i, 35).Value = 0 'Workbooks(binsheet).Sheets("Baler1Counts").Cells(h, 16).Value
ReDim Preserve AlarmArray(1 To UBound(AlarmArray) + 1) As Variant


End If
If i = 2 And Cells(i, 7).Value = 0 And AlarmArray(i, 1) > BaleCreateArray(i, 1) And Cells(i + 1, 7).Value <> 0 Then
ActiveCell.Offset(1).Activate
ActiveCell.Resize(1).EntireRow.Insert
Cells(i + 1, 3).Value = Sheets("BaleCreate1").Cells(i, 1).Value
Cells(i + 1, 4).Value = Sheets("Baler1").Cells(i, 3).Value + 1 / 1520
Cells(i + 1, 1).Value = "date" 'Workbooks(binsheet).Sheets("WetEndInfo").Cells(3, 6).Value
Range(Cells(i + 1, 5), Cells(i, 9)).Value = 0
Cells(i + 1, 28).Value = 1
Cells(i + 1, 30).Value = 0 'Workbooks(binsheet).Sheets("Baler1Counts").Cells(h, 11).Value
Cells(i + 1, 31).Value = 0 'Workbooks(binsheet).Sheets("Baler1Counts").Cells(h, 12).Value
Cells(i + 1, 32).Value = 0 'Workbooks(binsheet).Sheets("Baler1Counts").Cells(h, 13).Value
Cells(i + 1, 33).Value = 0 'Workbooks(binsheet).Sheets("Baler1Counts").Cells(h, 14).Value
Cells(i + 1, 34).Value = 0 'Workbooks(binsheet).Sheets("Baler1Counts").Cells(h, 15).Value
Cells(i + 1, 35).Value = 0 'Workbooks(binsheet).Sheets("Baler1Counts").Cells(h, 16).Value
ActiveCell.Offset(1).Activate

ElseIf AlarmArray(i, 1) > BaleCreateArray(i, 1) And AlarmArray(i, 1) <> BaleCreateArray(i, 1) Then
ActiveCell.Resize(1).EntireRow.Insert
Cells(i, 1).Value = "starting date" 'Sheets("WetEndInfo").Cells(3, 6).Value
Cells(i, 3).Value = Sheets("BaleCreate1").Cells(i, 1).Value
Cells(i, 4).Value = Sheets("Baler1").Cells(i, 3).Value + 1 / 1520
Cells(i, 1).Value = "date" 'Workbooks(binsheet).Sheets("WetEndInfo").Cells(3, 6).Value
Range(Cells(i, 5), Cells(i, 9)).Value = 0
Cells(i, 28).Value = 1
Cells(i, 30).Value = 0 'Workbooks(binsheet).Sheets("Baler1Counts").Cells(h, 11).Value
Cells(i, 31).Value = 0 'Workbooks(binsheet).Sheets("Baler1Counts").Cells(h, 12).Value
Cells(i, 32).Value = 0 'Workbooks(binsheet).Sheets("Baler1Counts").Cells(h, 13).Value
Cells(i, 33).Value = 0 'Workbooks(binsheet).Sheets("Baler1Counts").Cells(h, 14).Value
Cells(i, 34).Value = 0 'Workbooks(binsheet).Sheets("Baler1Counts").Cells(h, 15).Value
Cells(i, 35).Value = 0 'Workbooks(binsheet).Sheets("Baler1Counts").Cells(h, 16).Value
ActiveCell.Offset(1).Activate

End If
End If
Next i

End Sub


​
Can anyone help with this mess? Array's are not my strongpoint, but I would think one can be created to work here...
Thanks in advance,
Chris
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Found this code in this forum, but it only captures and inputs the C column. How can this be modified to capture the entire row in the first array?

Code: 
Sub test3()
' hiker95, 10/09/2014, ME810227
Dim rng1 As Range, rng2 As Range, c As Range, k, n As Long, lr As Long
With Sheets("Baler1")
  Set rng1 = .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row)
End With
With Sheets("Baler1Create")
  Set rng2 = .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row)
End With
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For Each c In rng1
    If c <> "" Then
      If Not .Exists(c.Value) Then
        .Add c.Value, c.Value
      End If
    End If
  Next
  For Each c In rng2
    If c <> "" Then
      If Not .Exists(c.Value) Then
        .Add c.Value, c.Value
      End If
    End If
  Next
  k = Application.Transpose(Array(.Keys))
  n = .Count
End With
With Sheets("Sheet3")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  If lr > 5 Then .Range(.Cells(1, 1), .Cells(lr, 1)).ClearContents
  With .Cells(1, 1).Resize(n, 1)
    .NumberFormat = "@"
    .Value = k
  End With
  .Range("A1:A" & n + 5).Sort key1:=.Range("A1"), order1:=1
  .Columns(1).AutoFit
  .Activate
End With


End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Help with rank and difference.
Replies
3
Views
59
Fluff
Fluff
N
  • Question
Need Formula help
Replies
5
Views
358
AhoyNC
A
S
  • Solved
Average in excel
Replies
3
Views
64
etaf
etaf
P
  • Solved
Find Max of Year
2
Replies
12
Views
184
bobsan42
bobsan42
S
  • Question
need ranking
Replies
5
Views
75
Cubist
Cubist

Forum statistics

Threads
1,214,973
Messages
6,122,534
Members
449,088
Latest member
RandomExceller01

Share this page

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
Back
Top