I am working on a VBA code which partially works. There are 3 worksheets in the workbook, the VBA/formulas use 2 of them: "Sheet1" (Table name: tbl_data) and "QCH Entity Additions" which contains the formulas, when they are set-up, but it is not currently in table format. There is a 2nd table on the "QCH Entity Additions" sheet called tbl_QCH_Key.
B9 Formula =UNIQUE(TOROW(tbl_data[Doc ID]), TRUE)
B7 Formula =XLOOKUP(B9,tbl_data[Doc ID],tbl_data[Description],"-",0,1)
B8 Formula =XLOOKUP(B9,tbl_data[Doc ID],tbl_data[Document Category],"-",0,1)
A10 Formula =SORT(UNIQUE(FILTER(tbl_data[Applicable QMS Entity], tbl_data[Applicable QMS Entity]<>"")))
And in the "range area", which spills to all columns with a Doc ID (in row 9), and all QMS Entities listed in Column A (Beginning at Cell A10). I am running the code out of my personal workbook, I was going to save as an XLSM with the code, but I could not figure out how to REPLACE a file once I attached it. So the code is below:
Sub QCHFormulas010()
' Turn off automatic calculations and screen updating to optimize performance
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim ws As Worksheet
Dim lastRowA10 As Long
Dim lastColumnB9 As Long
Dim i As Long, j As Long
' Assuming "QCH Entity Additions" is the active sheet
Set ws = ActiveSheet
' Enter the dynamic formulas in cells B9 and A10
ws.range("B9").Formula2 = "=UNIQUE(TOROW(tbl_data[Doc ID]), TRUE)"
ws.range("A10").Formula2 = "=SORT(UNIQUE(FILTER(tbl_data[Applicable QMS Entity], tbl_data[Applicable QMS Entity]<>"""")))"
' Calculate only the cells B9 and A10 to update their spill ranges
ws.range("B9:A10").Calculate
' Find the last column with data in row 9 based on the spill range of B9
lastColumnB9 = ws.Cells(9, ws.Columns.Count).End(xlToLeft).Column
' Find the last row with data in column A based on the spill range of A10
lastRowA10 = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Update the XLOOKUP formulas in cells B7 and B8 to spill to the right
For i = 7 To 8
For j = 2 To lastColumnB9
ws.Cells(i, j).Formula2 = "=XLOOKUP(ws.Cells(9, j), tbl_data[Doc ID], tbl_data[" & ws.Cells(i, 1).Value & "], ""-"", 0, 1)"
Next j
Next i
' Loop through each cell in the range starting at B10 to update the XLOOKUP formula with matrix dependency
For i = 10 To lastRowA10
For j = 2 To lastColumnB9
ws.Cells(i, j).Formula2 = "=IF(AND(ws.Cells(i, 1)<>"""", ws.Cells(9, j)<>""""), XLOOKUP(1, (tbl_QCH_Key[QMS Entity]=ws.Cells(i, 1)), tbl_QCH_Key[Display], """"))"
Next j
Next i
' Calculate only the "QCH Entity Additions" sheet to update the XLOOKUP results
ws.Calculate
' Turn on calculations and events
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
I assume that the file you attached is just a sample since it contains no macros or are you using another workbook to run the code you've posted?
You're putting VBA code inside a formula string, which won't work. I don't really understand the second loop as you are returning the same value to every column for each row, but I suspect you don't need to loop at all as you are dealing with two spill ranges and could probably just put one formula in B10 and let it spill across and down as needed.
@Hans Hallebeek,
Yes, This macro would run from my Personal workbook. That is why the code was not in the sample file I supplied but was in my post. Basically this file is created by using a macro to copy sheets from the template file. I attempted it a few ways and what happens is that the filename of the template became part of the formulas, which I do NOT want. Plus there will be users that enter data into Sheet1 (tbl_data) in the Applicable in MAP and Future Implementation Method columns. They are connected to the formulas on the QCH Entity Additions sheet and the data will change as the users provide input. So all of this must be done after the workbook is created and not have any connection to the template file. I hope that answers your questions
@Velouria,
I am doing this the way I could information to do it. I am only intermediate at best as far as VBA goes. And this one, is way more complicated than anything I have ever done. I am not opposed to removing the loop, I just don't know a better way to accomplish what I need to.
The proble is that when you use a template file this gets 'imbedded' in the reference to a button or filed, you will have to edit the formula's used and remover every occurrence to the template file since this also includes the path to your personal macrobook or the path to where you have the tamplete flie saved.
It's quite a job.
If you look at how these are built after the macro has run you'll see that you will have to remove quite some text from the from the references.
I'll see if I can replicate it here so that you see what I am trying to explian/convey, sounds more compliacted than it is though
@Hans Hallebeek,
I understand what you mean. I have seen the path that is connected to the template. I was just hoping there is some way around this. As this particular instance multiple people are using this template to generate a custom file for clients. And then this additional macro that is dynamic and spills would need to run (from the personal workbook of the analyst that ran the custom file). So I am looking for a miracle that will never happen, eh? Well at least knowin g that will prevent me from wasting any more time.
You can build it into the template file (if its'macro enabled) to correct this when the new file is gerated. You have to cross-check if the filename is not the same as the template's file name.
Will take some coding a testing but I've managed. Asides from using the personal macrobook I always use an AddIn that contains all the code and that serves as a trigger and motor for all the necessary code and this AddIn can be placed in the same folder all usres hace avvess to making it unnecessary to edit their own personal macrobooks or settings.
I hope my explanation helps a little. I'd be grald to help and make suggestions if I knew more about the templae file etc
@Hans Hallebeek,
I only have until Friday at my current company. I have been attempting to finish up as much as possible prior to the end of my contract. Thanks again so much. Next time we speak, I will have a new job!
Okay, congratulations for your new job.
You know where to find us
