blackbeginnings
New Member
- Joined
- Nov 5, 2013
- Messages
- 1
Because I am a novice to VBA and learning from posts found on this forum, I am trying to understand some VBA code that I found on this site (many thx jindon) that comes very close to what I am needing. I have attached a sample worksheet that illustrates the before and after.
What I am trying to accomplish is two-fold. I have a spreadsheet with 14 columns on the Customer worksheet of raw data (see attachment of sample data). What I would like to accomplish is to have a macro that can be ran during our first quarter that covers the months of Oct – Dec that will copy the data in columns A, D, E, M, F, K, and N located on the Customer Sheet and place that information in the respective columns A, B, C, D, E, L, and M on the Updated worksheet that has 13 columns. I think that’s the easy part for me to understand.
What I am having trouble with is developing a macro that I can run in the second quarter that will perform a search on the company name (column B on Updated worksheet) and compare that to the company name on the Customer worksheet (column D). If a duplicate record is found, then I need it to take the information found in columns M, F, K, and N on the Customer worksheet and update columns F, G, L, and M on the Update worksheet. If the comparison does not find a match, then add a new record on the next available blank row on the Updated worksheet and copy columns A, D, E, M, F, K, and N located on the Customer Sheet and place that information in the respective columns A, B, C, D, E, L, and M on the Updated worksheet.
This process will need to be repeated for each of the remaining quarters. The only difference is if the search finds duplicate company names, the information located in columns M, F, K, and N on the Customer worksheet will be placed in columns H, I, L, M for Qtr 3 and J, K, L, and M for Qtr 4 on the Updated worksheet.
Below is the code I referenced above that comes very close to what I am needing but it does not update the specific columns as outlined. However, it does add the new record if a duplicate is not found. Any assistance and/or guidance is greatly appreciated from one and all. Because I am such a novice, it would also be helpful to me in my learning process if comments explaining what the code is doing are included. Thank you in advance.
<tbody>
</tbody>
<tbody>
</tbody>
What I am trying to accomplish is two-fold. I have a spreadsheet with 14 columns on the Customer worksheet of raw data (see attachment of sample data). What I would like to accomplish is to have a macro that can be ran during our first quarter that covers the months of Oct – Dec that will copy the data in columns A, D, E, M, F, K, and N located on the Customer Sheet and place that information in the respective columns A, B, C, D, E, L, and M on the Updated worksheet that has 13 columns. I think that’s the easy part for me to understand.
What I am having trouble with is developing a macro that I can run in the second quarter that will perform a search on the company name (column B on Updated worksheet) and compare that to the company name on the Customer worksheet (column D). If a duplicate record is found, then I need it to take the information found in columns M, F, K, and N on the Customer worksheet and update columns F, G, L, and M on the Update worksheet. If the comparison does not find a match, then add a new record on the next available blank row on the Updated worksheet and copy columns A, D, E, M, F, K, and N located on the Customer Sheet and place that information in the respective columns A, B, C, D, E, L, and M on the Updated worksheet.
This process will need to be repeated for each of the remaining quarters. The only difference is if the search finds duplicate company names, the information located in columns M, F, K, and N on the Customer worksheet will be placed in columns H, I, L, M for Qtr 3 and J, K, L, and M for Qtr 4 on the Updated worksheet.
Below is the code I referenced above that comes very close to what I am needing but it does not update the specific columns as outlined. However, it does add the new record if a duplicate is not found. Any assistance and/or guidance is greatly appreciated from one and all. Because I am such a novice, it would also be helpful to me in my learning process if comments explaining what the code is doing are included. Thank you in advance.
Code:
Dim a, i As Long, ii As Integer, z As String
Dim n As Long, AB(), F_P(), x As Long, e
a = Sheet("WeeklyJob").Range("a1").CurrentRegion.Resize(,16).Value
ReDim AB(1 To UBound(a,1), 1 To 2)
ReDim F_P(1 To UBound(a,1), 1 To 11)
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For i = 1 To UBound(a,1)
z = a(i,4) & ";" & a(i,5)
If Not .exists(z) Then
n = n + 1
For ii = 1 To 13
If ii < 3 Then
AB(n,ii) = a(i,ii)
Else
F_P(n, ii - 2) = a(i, ii + 3)
End If
.add z, n
End If
Next
a = Sheets("Master").Range("a1").CurrentRegion.Resize(,16).Value
For i = 1 To UBound(a,1)
z = a(i,4) & ";" & a(i,5)
If .exists(z) Then
x = .item(z)
For ii = 1 To 13
If ii < 3 Then
a(i,ii) = AB(x, ii)
Else
a(i, ii + 3) = F_P(x, ii - 2)
End If
Next
.remove z
End If
Next
Sheets("Master").Range("a1").CurrentRegion.Resize(,16).Value = a
If .count > 0 Then
ReDim a(1 To .Count, 1 To 16) : n = 0
For Each e In .keys
x = .item(e) : n = n + 1
For ii = 1 To 13
If ii < 3 Then
a(n, ii) = AB(x,ii)
Else
a(n, ii + 3) = F_P(x, ii - 2)
End If
Next
Next
Sheets("Master").Range("a" & Rows.Count).End(xlUp)(2) _
.Resize(n, 16).Value = a
End If
End With| CUSTOMER DATA | |||||||||||||
| ID # | FName | LName | Company | Region | # of Empl | # of Hrs | Current | Processed | Returns | As of Date | Initial Level | Current Level | Qtr |
| 1 | Jane | Doe | XYZ Sales | Midwest | 21 | 30 | Yes | 15 | 13 | 10/1/2013 | Low | Low | 1 |
| 2 | David | Smith | Direct Biz | Mid-Atlantic | 52 | 100 | Yes | 49 | 6 | 10/15/2014 | Low | Low | 1 |
| 3 | David | Jones | Express | Northeast | 23 | 16 | No | 23 | 3 | 11/3/2013 | Low-Medium | Low-Medium | 1 |
| 4 | Mike | Black | XYZ Sales | Pacific | 31 | 31 | No | 24 | 8 | 12/18/2013 | Medium | Medium | 1 |
| 5 | Mary | Moore | ABC Biz | Southeast | 20 | 12 | Yes | 21 | 15 | 12/5/2013 | Low | Medium | 1 |
| 6 | Michelle | Jones | Express | Northeast | 47 | 51 | Yes | 36 | 13 | 12/19/2013 | Medium | Medium | 1 |
| How the above data table looks in Quarter 1 | |||||||||||||
| ID # | FName | LName | Company | Region | # of Empl | # of Hrs | Current | Processed | Returns | As of Date | Initial Level | Current Level | Qtr |
| 1 | Jane | Doe | XYZ Sales | Midwest | 21 | 30 | Yes | 15 | 13 | 10/1/2013 | Low | Low | 1 |
| 2 | David | Smith | Direct Biz | Mid-Atlantic | 52 | 100 | Yes | 49 | 6 | 1/15/2014 | Low | Low-Medium | 2 |
| 3 | David | Jones | Express | Northeast | 23 | 16 | No | 23 | 3 | 11/3/2013 | Low-Medium | Medium-High | 1 |
| 4 | Michelle | Jones | Express | Northeast | 47 | 51 | Yes | 36 | 13 | 3/6/2014 | Medium | Medium-High | 2 |
| 5 | Mike | Black | XYZ Sales | Pacific | 31 | 63 | Yes | 44 | 2 | 1/8/2014 | Medium | High | 2 |
| 6 | Mary | Moore | ABC Biz | Southeast | 20 | 10 | Yes | 203 | 1 | 3/27/2014 | Low | Medium-High | 2 |
| 7 | Sam | Wright | Right Co | Southwest | 10 | 10 | Yes | 100 | 1 | 3/13/2014 | Low | Low-Medium | 2 |
| How the above data table looks in Quarter 2 |
<tbody>
</tbody>
| UPDATED DATA | ||||||||||||||
| Qtr 1 | Qtr 2 | Qtr 3 | Qtr 4 | Optional Fields | ||||||||||
| ID # | Company | Region | Current Level | # of Emp | Current Level | # of Emp | Current Level | # of Emp | Current Level | # of Emp | As of Date | Qtr | FName | LName |
| 1 | XYZ Sales | Midwest | Low | 21 | 10/1/2013 | 1 | Jane | Doe | ||||||
| 2 | Direct Biz | Mid-Atlantic | Low | 52 | 10/15/2014 | 1 | David | Smith | ||||||
| 3 | Express | Northeast | Low-Medium | 23 | 11/3/2013 | 1 | David | Jones | ||||||
| 4 | XYZ Sales | Pacific | Medium | 31 | 12/18/2013 | 1 | Mike | Black | ||||||
| 5 | ABC Biz | Southeast | Medium | 20 | 12/5/2013 | 1 | Mary | Moore | ||||||
| 6 | Express | Northeast | Medium | 47 | 12/19/2013 | 1 | Michelle | Jones | ||||||
| This is how the above table should look after update in Quarter 1 | ||||||||||||||
| Qtr 1 | Qtr 2 | Qtr 3 | Qtr 4 | Optional Fields | ||||||||||
| ID # | Company | Region | Current Level | # of Emp | Current Level | # of Emp | Current Level | # of Emp | Current Level | # of Emp | As of Date | Qtr | FName | LName |
| 1 | XYZ Sales | Midwest | Low | 21 | Low | 21 | 10/1/2013 | 1 | Jane | Doe | ||||
| 2 | Direct Biz | Mid-Atlantic | Low-Medium | 52 | Low-Medium | 52 | 1/15/2014 | 2 | David | Smith | ||||
| 3 | Express | Northeast | Medium-High | 23 | Medium-High | 23 | 11/3/2013 | 1 | David | Jones | ||||
| 4 | XYZ Sales | Pacific | Medium | 31 | High | 31 | 1/8/2014 | 2 | Mike | Black | ||||
| 5 | ABC Biz | Southeast | Medium | 20 | Medium-High | 20 | 3/27/2014 | 1 | Mary | Moore | ||||
| 6 | Express | Northeast | Medium-High | 47 | Medium-High | 47 | 3/6/2014 | 2 | Michelle | Jones | ||||
| 7 | Right Co | Southwest | Low-Medium | 10 | 3/13/2014 | 2 | Sam | Wright | ||||||
| This is how the above table should look after update in Quarter 2 |
<tbody>
</tbody>
