View Full Version : [SOLVED:] VBA - Filling Master Template
malleshg24
11-01-2019, 02:29 PM
Hi Team,
Need your help for filling Master Template, Data is in Data Tab of attached workbook.
Currently Row third of Master Template I have filled with excel formula.
Can you plz suggest any other method for achieving the same task. like array,collection or dictionary.etc.
Thanks in advance for your help.
Regards,
mg
Paul_Hossler
11-01-2019, 03:51 PM
This is tied in with the worksheet activate event so that it always has the latest Data. It can also be a stand-alone sub
There's some more polishing that can be added, but I didn't go too far in case this isn't what you wanted
Option Explicit
Sub UpdateMaster()
Dim vData As Variant
Dim rTemplate As Range
Dim i As Long, r As Long, c As Long
Dim sStatus As String, sProduct As String, sSE As String
Set rTemplate = Cells(1, 1).CurrentRegion
vData = Worksheets("Data").Cells(1, 1).CurrentRegion.Value
For r = LBound(vData, 1) To UBound(vData, 1)
For c = LBound(vData, 2) To UBound(vData, 2)
vData(r, c) = UCase(vData(r, c))
Next c
If vData(r, 2) = "PENDING" Then vData(r, 2) = "WORK IN PROGRESS" ' inconsistent
Next r
With rTemplate
For r = 3 To .Rows.Count
.Cells(r, .Columns.Count).Value = 0
.Cells(r, .Columns.Count - 1).Value = 0
For c = 2 To .Columns.Count - 2
sStatus = UCase(.Cells(r, 1).Value)
sProduct = UCase(.Cells(1, c).Value)
If Len(sProduct) = 0 Then sProduct = UCase(.Cells(1, c - 1).Value) ' merged cells
sSE = .Cells(2, c).Value
.Cells(r, c).Value = 0
For i = LBound(vData, 1) + 1 To UBound(vData, 1)
If vData(i, 1) = sProduct And vData(i, 2) = sStatus Then
If sSE = "SOD" Then
If InStr(vData(i, 3), "PEN") > 0 Then
.Cells(r, c).Value = .Cells(r, c).Value + 1
.Cells(r, .Columns.Count - 1).Value = .Cells(r, .Columns.Count - 1).Value + 1
End If
ElseIf sSE = "EOD" Then
If InStr(vData(i, 3), "PEN") = 0 Then
.Cells(r, c).Value = .Cells(r, c).Value + 1
.Cells(r, .Columns.Count).Value = .Cells(r, .Columns.Count).Value + 1
End If
End If
End If
Next i
Next c
Next r
End With
End Sub
Two Formulas for the entire sheet. These go in B3 and C3
=COUNTIFS(Data!$A:$A,B$1,Data!$B:$B,$A3,Data!$C:$C,SOD)
=COUNTIFS(Data!$A:$A,B$1,Data!$B:$B,$A3,Data!$C:$C,EOD)
Copy Cells B3 and C3 and paste as appropriate across the table. Then select Rows 3 thru 6 of the table and press Ctrl+D
What is "SOD" and "EOD" in those formulas? Well, SOD and EOD aren't on the Data sheet, so I created two Named Formulas, SOD = "Penxxx" and EOD="Penxxx".
If you create three Names (Product ,Status, Comments)on the Data sheet, the formulas would read
=COUNTIFS(Product,B$1,Status,$A3,Comments,SOD)
=COUNTIFS(Product,B$1,Status,$A3,Comments,EOD)
What this means:
Named Ranges can be Cut and Inserted without affecting the formulas that use them.
You can edit a Named Formula and it will affect all Formulas that use that Name
If you Insert new columns into the Master Template Table, you can add the Named Formula X="xxxx", copy the old formulas, then just change "SOD" to "X"
If you change a word in the Data sheet, you only have to edit the Named Formula, not all the sheet Formulas
malleshg24
11-03-2019, 01:41 PM
Hi Paul and Sam,
Thanks for your help. I have modified little as per your code and its working.
Regards,
mg
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.