UPDATE: OK, I think I have simplified my question. I need code that will do the following:
- Loop through a predefined set of source cells (e.g., B6:B10 in the screenshot). The contents of those cells will represent the column headers in the output range (screenshot C14:G14). Based on the value of each source cell, I already have the formula to determine how many output rows are required (call this Z), and what the contents of the output cells should be.
- Based on the content of the first source cell, somewhere between 1-9 rows of data will be written to the output range. When the first cell is parsed, the only data that will entered in the output area will be in the first column (screenshot C15-Cx).
- For the second and all subsequent source cells, it is possible that no new rows will be added to the output range (see 3a below). It is also possible that rows will be added, with the number of new rows equal to a formula based on Z (see 3b below).
---- 3a. If Z=0 or 1 for a source cell, it means no new rows are required. But we still need at least one value to be written to all existing rows of the output range, entered in the proper output column for that source cell. So if the first source cell resulted in the value “1” being entered in C15 and Z=1 for the second source cell, we need a value entered in D15. The third source cell would need (if Z=1 for that cell) a number written to E15, and so on.
---- 3b. If Z>1 then new output rows ARE required, and things get trickier. We need to add new output rows equal to (Existing Output Rows) * Z – Existing Output Rows. So if Z=2, then all existing rows must be duplicated, with the first half (the original rows) receiving a “1” in the appropriate column, and the second half (the new rows) receiving a “2”. If Z=3, then all existing rows must be duplicated twice, with the first third (the original rows) receiving a “1” in the appropriate column, the second third receiving a “2” and the last third receiving a “3”. And so on, since the maximum possible value of Z=9.
In the screenshot I provided, it doesn’t matter that the output rows could overwrite the yellow section. I only put that section there to illustrate what the output should look like. That section will be gone in the finished product.
I am leaving the full content of the original question below, in case it’s helpful.
-↓-
ORIGINAL QUESTION: I am stuck on how to use VBA to accomplish a goal and am hoping for some guidance. My code is doing half of what I want it to do (aka, the easy part) but I don’t know where to start with tackling the second half.
The goals here are to (1) calculate the maximum number of outputs from a master Photoshop file with multiple folders and multiple layers per folder, subject to certain constraints, and (2) write a row in Excel for each set of combinations showing how many layers from each group will be used in that set. (The PSD data will eventually come in via JS, but I am not worried about that for now since I have manually entered in Excel all relevant information about the PSD folders and layers.)
Each PSD folder has a “tag” between brackets “[]” that indicates how many layers from that folder can be used in a given output file. There are 3 acceptable tag formats: 1 (meaning each output must use this number of layers in this folder) [ALL] (meaning all layers from this folder must be used in every output) [x-y] (meaning each output can contain any number layers between x and y inclusive)
My code gets through the calculation of maximum number of outputs subject to the constraints, the sizing of the output data range, and the writing of the output range row and column headings. But when it comes to writing the body of the output range, I am stuck. The sample file contains a range of yellow cells that has been manually entered to show what the dark blue cells should look like after the VBA is complete.
At this point, my code doesn’t even attempt to write anything to these cells. You can’t know what should be entered in a given cell until folder/layer data has been parsed, and at that point I’m not sure how to go back and start entering the data in output range R1C1. Seems like it will require some kind of recursive process working backwards. Since SO frowns upon uploading the workbook, I am including my code below and a screenshot of the Excel output file, which I think is really helpful to understand what I am trying to explain! In short, the VBA should make the dark blue section look just like the yellow section.
Any pointers greatly appreciated! Thanks.
Screenshot of Excel Output File
Dim ws As Worksheet
Dim c As Range 'used to reference any cell in a range
Dim Folders As Range 'named worksheet range with PSD folder names
Dim Layers As Range 'named ws range showing number of PSD layers in each PSD folder
Dim LayersMin As Range 'named ws range w/minimum visible layers per output for each folder
Dim LayersMax As Range 'named ws range w/maximum visible layers per output for each folder
Dim OutputStart As Range 'named ws cell that is 1 cell above and left of csv output range
Dim OutputFiles As Range 'named ws range where count of output files is placed
Dim OutputRange As Range 'named ws range where csv output will be placed
Dim LayerComboRange As Range 'named ws range where actual layer counts are entered (subset of OutputRange)
Dim LayerCount As Integer
Dim ComboCount As Integer 'count of output combinations/files for each layer
Dim ComboTotal As Long 'running count of output combinations/files
Dim RowCount As Integer 'running count of number of rows needed for CSV output
Dim ColumnCount As Integer 'running count of number of columns needed for CSV output (not counting first column that just says "Row1", etc.)
Dim Tag As String 'the text in each PSD folder name between the brackets
Dim Min As Integer 'used to calc min number of layers per folder
Dim Max As Integer 'used to calc max number of layers per folder
Dim x As Integer 'general counting variable
'Setup
Set ws = ActiveSheet
Set Folders = ws.Range("FolderNames") 'PSD folder names pre-entered here
Set Layers = ws.Range("Layers") 'count of layers per folder pre-entered here
Set LayersMin = ws.Range("LayersMin") 'not necessary for script, just here for tracking purposes
Set LayersMax = ws.Range("LayersMax") 'not necessary for script, just here for tracking purposes
Set OutputStart = ws.Range("OutputStart")
Set OutputRange = ws.Range("OutputRange")
Set OutputFiles = ws.Range("OutputFiles")
'Set up
OutputRange.ClearContents
OutputFiles.ClearContents
LayersMin.ClearContents
LayersMax.ClearContents
ColumnCount = 0
RowCount = 1 'will always be at least one row required in CSV output
'Loop through each PSD folder listed in Folders range
For Each c In Folders
'Get folder name & layer count in folder
ColumnCount = ColumnCount 1 'running folder/column count
OutputStart.Offset(0, ColumnCount).Value = c.Value 'enter folder name in top row of CSV output
LayerCount = Layers(ColumnCount).Value 'record number of layers in this folder
'extract tag between brackets
FromPos = InStr(c, "[")
ToPos = InStr(c, "]")
Tag = Mid(c, FromPos 1, ToPos - FromPos - 1)
'grab low and high layer count (if any) and calc rows needed
ComboCount = 0 ' reset layer combo counter
If InStr(1, Tag, "ALL") > 0 Then
ComboCount = 1
LayersMin(ColumnCount).Value = LayerCount 'enter min layers for this folder
LayersMax(ColumnCount).Value = LayerCount 'enter max layers for this folder
ElseIf InStr(1, (Tag), "-") > 0 Then
Min = CInt(Left(Tag, 1))
LayersMin(ColumnCount).Value = Min 'enter min layers for this folder
Max = CInt(Right(Tag, 1))
LayersMax(ColumnCount).Value = Max 'enter max layers for this folder
For x = Min To Max
ComboCount = ComboCount WorksheetFunction.Combin(LayerCount, x)
Next x
x = 0 'reset counter
RowCount = RowCount * (Max - Min 1)
ElseIf Tag = "1" Then
ComboCount = LayerCount
LayersMin(ColumnCount).Value = 1 'enter min layers for this folder
LayersMax(ColumnCount).Value = 1 'enter max layers for this folder
End If
'Keep running count of possible output file combinations
If ColumnCount = 1 Then
ComboTotal = ComboCount
Else
ComboTotal = ComboTotal * ComboCount
End If
Next c
'Write output data
'Define output ranges based on row & column counts
Set OutputRange = Range(OutputStart, OutputStart.Offset(RowCount, ColumnCount))
ws.Names.Add Name:="OutputRange", RefersTo:=OutputRange 'redefine ws name so data erased on next VBA run
Set LayerComboRange = Range(OutputStart.Offset(1, 1), OutputStart.Offset(RowCount, ColumnCount))
ws.Names.Add Name:="LayerComboRange", RefersTo:=LayerComboRange 'redefine ws name, not sure if necessary
'Write row headers
For x = 1 To RowCount
OutputStart.Offset(x, 0).Value = "Row" & x
Next x
x = 0 'reset counter
OutputFiles.Value = ComboTotal 'just for kicks
'NEXT STEPS: How to write data to output range (LayerComboRange)?
End Sub
CodePudding user response：
After thinking through this further, I created a much tighter Minimal Reproducable Example and shorter summary, which I posted as a new question. I am closing this question since the new one is more efficient and targeted.