OK, so I'm still messing around with trying to learn VBA stuff and getting better all the time thanks to the help from here. I'm just getting into array's and I can fill in my array values and have it be dynamic, but now I'm struggling with comparing other values to what's in the array. I'm hoping you all can help.
First, here is some sample data they are in columns b-d, they will always start at row-23, but the length of the data set can and will vary.
<tbody>
</tbody>
Here is some code I have now, keep in mind I am working on just a snippet from a larger set of code, so there are a lot of extra variables at the top of the code.
What I'm trying to do is build out an Array to contain all of the values in Column-D if the value in column-B = 2100.
Then I need to compare the values in column-D back to that array when the value in column-B = 3000.
So, from my code I am building out the array without any problems, but I'm stuck on how do I do the comparison back to the array for my values. As you can see from the sample data I can have 3000 values in different places and as stated the overall list size can change from the example. If I look at all of the 3000 values and their value in column-D doesn't show up in the Array for the 2100 values, then I want to flag that 3000 Value in column-D by changing the colors of the text and background.
I appreciate any guidance you can provide, and I'm open to suggestion on building out the array if there is a better way to do it than what I have in my code as well.
Thanks,
Phil
First, here is some sample data they are in columns b-d, they will always start at row-23, but the length of the data set can and will vary.
1000 | 1.0 | GAMASUA |
2100 | 1.0 | 00000006 |
3000 | 1.0 | 00000006 |
3000 | 1.0 | 00000006 |
3000 | 1.0 | 00000006 |
3000 | 1.0 | 00000006 |
2100 | 1.0 | 00000005 |
3000 | 1.0 | 00000005 |
2100 | 1.0 | 00000003 |
2100 | 1.0 | 00000004 |
2100 | 1.0 | 00000002 |
3000 | 1.0 | 00000002 |
2100 | 1.0 | 00000000 |
3000 | 1.0 | 00000000 |
2100 | 1.0 | 00000001 |
3000 | 1.0 | 00000001 |
3000 | 1.0 | 00000003 |
3000 | 1.0 | 00000004 |
3000 | 1.0 | 00000000 |
5000 | 7 |
<tbody>
</tbody>
Here is some code I have now, keep in mind I am working on just a snippet from a larger set of code, so there are a lot of extra variables at the top of the code.
Code:
Sub TestRecordValueCheck()
'UPI = Unique Payable Identifer - from 2100 record field [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3"]#3[/URL]
'TPA = Total Payable Amount - from 2100 record field [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5"]#5[/URL]
'ITA = Invoice Total Amount - from 3000 recrod field [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8"]#8[/URL]
'CSPR = Count Submitted Payable Request - from 5000 record field [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2"]#2[/URL]
'T2100Count = A Counter for the number of time 2100 records show up
'RLN = Remittance Line number - from 3000 records field [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4"]#4[/URL]
'Rng = Range of the Cells to be checked
' ****************************************************************
Dim Cell As Range, Cell2 As Range, Cell3 As Range, UPI As Range, i As Integer
Dim ErrorCount As Integer, TPA As Currency, ITA As Currency, ITACell As Range, TPACell As Range
Dim CSPR As Integer, CSPRCell As Range, T2100Count As Integer
Dim RLN As Integer, RLNCell As Range, RLNCount As Integer
Dim Rng As Range, Dn As Range, nRng As Range
Dim nR As Range, Rng1 As Range, c As Long, R As Range
Dim WhatChanged As Range, CommaCount As Long, RawRNG As Range
Dim UPIVal As Range, array21(), j As Integer
Dim p As Integer, Pn As Range, Temp As Integer, Found As String
' ****************************************************************
' ****************************************************************
Set WhatChanged = Range("B2:B6000")
Const num = 2100
Set Rng = Range(Range("B23"), Range("B" & Rows.Count).End(xlUp))
Set RawRNG = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
T2100Count = 0
' ****************************************************************
' ****************************************************************
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("PIF Checker Output - Horz").Activate
' ****************************************************************
ErrorCount = 0
j = 1
With ThisWorkbook.Worksheets("PIF Checker Output - Horz")
' ****************************************************************
' ****************************************************************
For Each Cell In Rng
If Cell.Value = 2100 Then
T2100Count = T2100Count + 1
End If
Next
ReDim array21(1 To T2100Count)
For Each Cell In Rng
If Cell.Value = 2100 Then
array21(j) = Cell.Offset(0, 2)
j = j + 1
End If
Next
i = 1
Found = False
For i = LBound(array21) To UBound(array21)
If Cell.Offset(0, 2) = 3000 Then
If Found <> True Then
ErrorCount = ErrorCount + 1
Cell3.Offset(0, 2).Interior.Color = vbRed
Cell3.Offset(0, 2).Font.Bold = True
Cell3.Offset(0, 2).Font.Color = vbYellow
Cell3.Offset(0, -1).Interior.Color = vbRed
Cell3.Offset(0, -1).Font.Bold = True
Cell3.Offset(0, -1).Font.Color = vbYellow
Cell3.Offset(0, -1).Value = "Errors in this Row"
End If
' ****************************************************************
End With
' ****************************************************************
Application.EnableEvents = True
Application.ScreenUpdating = True
' ****************************************************************
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
What I'm trying to do is build out an Array to contain all of the values in Column-D if the value in column-B = 2100.
Then I need to compare the values in column-D back to that array when the value in column-B = 3000.
So, from my code I am building out the array without any problems, but I'm stuck on how do I do the comparison back to the array for my values. As you can see from the sample data I can have 3000 values in different places and as stated the overall list size can change from the example. If I look at all of the 3000 values and their value in column-D doesn't show up in the Array for the 2100 values, then I want to flag that 3000 Value in column-D by changing the colors of the text and background.
I appreciate any guidance you can provide, and I'm open to suggestion on building out the array if there is a better way to do it than what I have in my code as well.
Thanks,
Phil
Last edited: