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

[ChrisCom]

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

	A	B	C	D	E	F
Date	Time	Start Date Time	End Date Time	TagName	Message Number
00:11:33	Line_C\B4Alm\B4M_039	B4M_039
00:23:43	Line_C\B4Alm\B4M_021	B4M_021
00:23:48	Line_C\B4Alm\B4M_039	B4M_039
00:29:39	Line_C\B4Alm\B4M_021	B4M_021
00:37:13	Line_C\B4Alm\B4M_188	B4M_188
00:39:56	Line_C\Alpa\A3M_154	A3M_154
00:41:49	Line_C\Alpa\A3M_143	A3M_143
00:42:14	Line_C\Alpa\A3M_143	A3M_143
00:42:35	Line_C\Alpa\A3M_143	A3M_143
00:47:54	Line_C\Alpa\A3M_143	A3M_143
01:01:44	Line_C\Alpa\A3M_150	A3M_150
01:54:55	Line_C\B4Alm\B4M_016	B4M_016
02:08:29	Line_C\Alpa\A3M_154	A3M_154

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]12/8/2014[/TD]

[TD="align: right"]12/8/14 12:11 AM[/TD]
[TD="align: right"]12/8/14 12:11 AM[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]12/8/2014[/TD]

[TD="align: right"]12/8/14 12:11 AM[/TD]
[TD="align: right"]12/8/14 12:11 AM[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]12/8/2014[/TD]

[TD="align: right"]12/8/14 12:23 AM[/TD]
[TD="align: right"]12/8/14 12:32 AM[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]12/8/2014[/TD]

[TD="align: right"]12/8/14 12:23 AM[/TD]
[TD="align: right"]12/8/14 12:24 AM[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]12/8/2014[/TD]

[TD="align: right"]12/8/14 12:29 AM[/TD]
[TD="align: right"]12/8/14 3:38 AM[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]12/8/2014[/TD]

[TD="align: right"]12/8/14 12:37 AM[/TD]
[TD="align: right"]12/8/14 12:39 AM[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]12/8/2014[/TD]

[TD="align: right"]12/8/14 12:39 AM[/TD]
[TD="align: right"]12/8/14 12:41 AM[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]12/8/2014[/TD]

[TD="align: right"]12/8/14 12:41 AM[/TD]
[TD="align: right"]12/8/14 12:41 AM[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]12/8/2014[/TD]

[TD="align: right"]12/8/14 12:42 AM[/TD]
[TD="align: right"]12/8/14 12:42 AM[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]12/8/2014[/TD]

[TD="align: right"]12/8/14 12:42 AM[/TD]
[TD="align: right"]12/8/14 12:42 AM[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]12/8/2014[/TD]

[TD="align: right"]12/8/14 12:47 AM[/TD]
[TD="align: right"]12/8/14 12:49 AM[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12/8/2014[/TD]

[TD="align: right"]12/8/14 1:01 AM[/TD]
[TD="align: right"]12/8/14 1:43 AM[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]12/8/2014[/TD]

[TD="align: right"]12/8/14 1:54 AM[/TD]
[TD="align: right"]12/8/14 1:56 AM[/TD]

</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
Timestamp

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]12/8/14 12:00 AM[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]12/8/14 12:03 AM[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]12/8/14 12:04 AM[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]12/8/14 12:05 AM[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]12/8/14 12:06 AM[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]12/8/14 12:07 AM[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]12/8/14 12:08 AM[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]12/8/14 12:09 AM[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]12/8/14 12:10 AM[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]12/8/14 12:11 AM[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]12/8/14 12:12 AM[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12/8/14 12:13 AM[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]12/8/14 12:15 AM[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]12/8/14 12:16 AM[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]12/8/14 12:17 AM[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]12/8/14 12:18 AM[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]12/8/14 12:19 AM[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]12/8/14 12:20 AM[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]12/8/14 12:21 AM[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]12/8/14 12:22 AM[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]12/8/14 12:23 AM[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]12/8/14 12:24 AM[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]12/8/14 12:26 AM[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]12/8/14 12:27 AM[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]12/8/14 12:28 AM[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]12/8/14 12:29 AM[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]12/8/14 12:30 AM[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]12/8/14 12:31 AM[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]12/8/14 12:32 AM[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]12/8/14 12:33 AM[/TD]

</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

	A	B	C	D	E	F	G
Date	Time	Start Time	End Alarm Time	Alarm Message
00:02:35	Line_C\B4Alm\B4M_124
00:09:00	Line_C\B4Alm\B4M_124

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/14/2014[/TD]
[TD="align: right"]00:00:03[/TD]
[TD="align: right"]1/14/14 12:00 AM[/TD]
[TD="align: right"]01/14/14 00:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/14/2014[/TD]
[TD="align: right"]00:02:03[/TD]
[TD="align: right"]1/14/14 12:02 AM[/TD]
[TD="align: right"]01/14/14 00:02[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1/14/2014[/TD]

[TD="align: right"]1/14/14 12:02 AM[/TD]
[TD="align: right"]1/14/14 12:03 AM[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1/14/2014[/TD]
[TD="align: right"]00:07:03[/TD]
[TD="align: right"]1/14/14 12:07 AM[/TD]
[TD="align: right"]01/14/14 00:07[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1/14/2014[/TD]
[TD="align: right"]00:08:03[/TD]
[TD="align: right"]1/14/14 12:08 AM[/TD]
[TD="align: right"]01/14/14 00:08[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1/14/2014[/TD]

[TD="align: right"]1/14/14 12:09 AM[/TD]
[TD="align: right"]1/14/14 12:09 AM[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1/14/2014[/TD]
[TD="align: right"]00:10:03[/TD]
[TD="align: right"]1/14/14 12:10 AM[/TD]
[TD="align: right"]01/14/14 00:10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1/14/2014[/TD]
[TD="align: right"]00:13:03[/TD]
[TD="align: right"]1/14/14 12:13 AM[/TD]
[TD="align: right"]01/14/14 00:13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1/14/2014[/TD]
[TD="align: right"]00:15:03[/TD]
[TD="align: right"]1/14/14 12:15 AM[/TD]
[TD="align: right"]01/14/14 00:15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1/14/2014[/TD]
[TD="align: right"]00:17:03[/TD]
[TD="align: right"]1/14/14 12:17 AM[/TD]
[TD="align: right"]01/14/14 00:17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1/14/2014[/TD]
[TD="align: right"]00:38:03[/TD]
[TD="align: right"]1/14/14 12:38 AM[/TD]
[TD="align: right"]01/14/14 00:38[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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

 

[ChrisCom]

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?

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

 

[ChrisCom]

Can anyone help me out on this?