VBA help! Multiple column export

[swartzfeger]

All, I'm having some issue with VBA I wrote. I tried fixing it with CHatGPT and it was one step forward, two steps back -- some things improved and a lot more broke! lol

Here's what I'm trying to do. Simple spreadsheet like this:

Batch 1	Missing #	Batch 2	Missing #	Batch 3	Missing #
1	4	11	12	21	26
2	6	12	17	22	30
3	9	13		23
4		14		24
5		15		25
6		16		26
7		17		27
8		18		28
9		19		29
10		20		30

A column ("Batch) with a range of numbers, and then the next column will track which numbers in that previous batch are missing. What I need to do is export the range of missing numbers, but prepend each missing number with the name of its associated batch (the name of the previous column. So the export would look like this:

Batch 1 - 4
Batch 1 - 6
Batch 1 - 9
Batch 2 - 12
Batch 2 - 17
Batch 3 - 26
Batch 3 - 30

The challenge -- these worksheets will always have an undetermined number of batches, so there will always be an undetermined number of "Missing #" columns. We need to find every Missing # column that's no empty, and export that number prepended with the previous column's header name.

Unfortunately, my VBA is only exporting the first "Missing #" column. ChatGPT helped a little but sometimes it only exported the final Missing # column; one time it exported all the columns, but into separate files. One time it got close and managed to grab all the missing numbers, but it only prepended the Batch number with the missing number for the first row of the Missing # column. Here's the VBA:

Sub ExportMissingData()

Dim ws As Worksheet
Dim rng As Range
Dim savePath As Variant
Dim wb As Workbook
Dim prevHeader As String
Dim fileName As String
Dim missingColumns As New Collection
Dim col As Variant
Dim i As Long

Set ws = ActiveSheet

For Each cell In ws.Range("A1").CurrentRegion.Rows(1).Cells
    If InStr(cell.Value, "Missing #") > 0 Then
        missingColumns.Add cell.Column
    End If
Next cell

If missingColumns.Count > 0 Then
    For Each col In missingColumns
        Set rng = ws.Range(ws.Cells(2, col), ws.Cells(ws.Rows.Count, col).End(xlUp))
        fileName = "Export_" & Format(Date, "mm-dd-yyyy") & "_" & Format(Time, "hhmmss") & ".xls"
        savePath = Application.DefaultFilePath & "\" & fileName
        Set wb = Workbooks.Add
        prevHeader = ""
        For i = 1 To col - 1
            prevHeader = prevHeader & " - " & ws.Cells(1, i).Value
        Next i
        rng.Copy Destination:=wb.Worksheets(1).Range("A1")
        For i = 1 To rng.Rows.Count
            wb.Worksheets(1).Cells(i, 1).Value = prevHeader & " - " & wb.Worksheets(1).Cells(i, 1).Value
        Next i
        Application.DisplayAlerts = False
        wb.SaveAs fileName:=savePath, FileFormat:=56
        wb.Close
        Application.DisplayAlerts = True
    Next col
End If

End Sub

The other issue (that I can eventually figure out and not as important) is that it prepends it like "- Batch 1 - 123" with a leading -. Any ideas? I'm starting to go cross-eyed looking at this!

 

[Herakles]

Does this do the job?

It asumes that the data starts from A1 and that the sheet is the active sheet.

Public Sub subMissingBatches()
Dim arr() As Variant
Dim i As Integer
Dim ii As Integer
Dim WsSource As Worksheet
    
    ActiveWorkbook.Save
    
    Set WsSource = Worksheets("MissingBatches")
    
    Workbooks.Add
    
    ActiveWorkbook.SaveAs filename:=Application.DefaultFilePath & "\" & "Export_" & Format(Date, "mm-dd-yyyy") & "_" & _
        Format(Time, "hhmmss") & ".xls", FileFormat:=56
                      
    arr = WorksheetFunction.Transpose(WsSource.Range("A1").CurrentRegion.Value)
            
    With ActiveSheet
        .Name = "Missing"
        .Range("A1").Value = "Missing"
        For i = LBound(arr) To UBound(arr) Step 2
            For ii = 2 To UBound(arr, 2)
                If arr(i + 1, ii) <> "" Then
                    .Range("A" & Rows.Count).End(xlUp).Offset(1).Value = arr(i, 1) & " - " & arr(i + 1, ii)
                End If
            Next ii
        Next i
        .Parent.Close savechanges:=True
    End With
            
    MsgBox "Missing Batches Logged.", vbInformation, "Confirmation"

End Sub

 

[swartzfeger]

Does this do the job?

It asumes that the data starts from A1 and that the sheet is the active sheet.

Not really, but thanks for the help! Your version is grabbing the numbers from the preceding Batch column instead of the numbers in the Missing column. Also it's only grabbing one column; it needs to loop through and search for every "Missing #" column, see if there are numbers in that column, and then export those numbers with the previous columns header prepended to it.

 

[Herakles]

I copied your data into cells A1:F11.

I got these results as you stated that you wanted.

 

[swartzfeger]

I copied your data into cells A1:F11.

I got these results as you stated that you wanted.

View attachment 90139

Let me go back and look at my book, sounds like an issue on my end... thank Herakles!

 

[swartzfeger]

I copied your data into cells A1:F11.

I got these results as you stated that you wanted.

View attachment 90139

Complete user error on my part... too embarrassed to say what I did but yes, it works perfectly, thank you!

 

[Herakles]

I'm glad that it works now.