VBA Concatenate Cells from two columns based on criteria present in a third

Question

I'm currently trying to combine two sets of data from two different columns based upon another set of data being present within a third column.

In this case I need basic country tags from column G (Strings of three letter codes) to be joined to the front of the string present in column H providing the string "Missing Audio" or "Missing Audio/Subs" is present in column F

My current fixed variable is that all data will start on the sixth row of their various columns however they will run to an undetermined point.

I've got various pieces of attempted code I'd be happy to post, but none of them seem to work and I'm beginning to get the impression that I've over complicated the process in my head and its creating a form of coders block! Any help offered would be massively appreciated

As Asked here is my attempt, trying to pull in a basic Concatenate script with the variable requirements:

Sub Notestidy()

Dim w1 As Worksheet
Dim c As Range, AC As String
Dim rng As Range, rng2 As Range, iRow As Integer, iCol As Integer, i As Integer

Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set rng = w1.Range("G6", w1.Range("G" & Rows.Count).End(X1up))
Set rng2 = w1.Range("H6", w1.Range("H" & Rows.Count).End(X1up))

For Each c In w1.Range("F6", w1.Range("F" & Rows.Count).End(xlUp))
  AC = .String("Missing Audio", "Missing Audio/Subs")
  On Error Resume Next
  If c = Range Then
    Dim varConctnt As Variant
    For iRow = 1 To rng.Rows.Count: rng2.Rows.Count
    For iCol = 1 To rng.Columns.Count: rng2.Columns.Count
    If Not rng(iRow, iCol).Value = vbNullString Then
      varConctnt = varConctnt & ", " & rng(iRow, iCol).Value
    End If
Next iCol
If varConctnt = vbNullString Then MsgBox "Empty Array": GoTo skip1
MsgBox Mid(varConctnt, 2)
varConctnt = ""

skip1:
Next iRow

Application.ScreenUpdating = True

End Sub

New Code Attempt:

> Sub Notestidy()
> 
> Dim w1 As Worksheet Dim rng As Range Dim FirstRow As Integer
> 
> 
> 
> Set w1 = Worksheets("Sheet1") FirstRow = 0
> 
> 
> Set rng = w1.Range("F:F").Find(What:="Missing Audio",
> LookIn:=xlValues, _   LookAt:=xlPart, SearchDirection:=xlNext,
> MatchCase:=False) While Not rng Is Nothing   If FirstRow <> rng.Row
> Then
>     If FirstRow = 0 Then
>       FirstRow = rng.Row
>     End If
>     w1.Range("H" & rng.Row) = w1.Range("G" & rng.Row) & w1.Range("H" & rng.Row)
>     rng.FindNext After:=rng   Else
>     Set rng = Nothing   End If Wend
> 
> Application.ScreenUpdating = True
> 
> End Sub

Answer 1

If I have interpreted your question correctly, I think you may have overcomplicated it!

Sub Notestidy()    

    Dim cc, sc, nc As String
    Dim ws As Worksheet

    Set ws = Sheet1

    cc = "G"
    sc = "H"
    nc = "F"

    Dim i, max As Integer
    max = ws.UsedRange.Rows.Count

    For i = 2 To max

        If ws.Range(nc & i).Value = "Missing Audio" Or ws.Range(nc & i).Value = "Missing Audio/Subs" Then

            ws.Range(sc & i).Value = ws.Range(cc & i).Value & " " & ws.Range(sc & i).Value

        End If

    Next i

    Set ws = Nothing
End Sub

Edit: Or as per FreeMan's answer utilising the Find method:

Sub Notestidy()

    Dim w1 As Worksheet
    Dim result As Range
    Dim FirstRow As Integer

    Set w1 = Worksheets("Sheet1")
    FirstRow = 0

    With w1.Range("F:F")
        Set result = .Find(What:="Missing Audio", LookIn:=xlValues, LookAt:=xlPart)

        If Not result Is Nothing Then
            FirstRow = result.Row
            Do
                w1.Range("H" & result.Row) = w1.Range("G" & result.Row) & w1.Range("H" & result.Row)
                Set result = .FindNext(result)
            Loop While Not result Is Nothing And result.Row <> FirstRow
        End If
    End With

    Set w1 = Nothing
    Set result = Nothing

End Sub

Answer 2

A couple of pointers to get you started:

• never use Application.ScreenUpdating = False until your code works. It only hides what's going on and make it much more difficult to figure out issues
• There are very rare and specific times you want to use On Error Resume Next, and this isn't one of them. All it does is mask the fact that a run-time error occurred. It allows you to handle specific errors that you expect might happen. That's not what you're after here.
• Assignment of worksheets & ranges to variables instead of using Active... is excellent!
• .String is not a valid method or object, and when not included in a With...End With block, the leading-dot notation is invalid
• Combining multiple statements on a single line with the : is valid, and some people do it. My personal opinion is that it makes code more difficult to read and mentally parse, even though the compiler has no issue with it. This is especially true when you combine some code on one line with :, then have additional code to be executed in that code block that's not combined on that one line.
• Proper and consistent indentation will help you figure out your code blocks and where to end a block that's begun.
• Built in functions, like .Find() are much, much faster than looping through blocks of cells looking for things.

so...

Sub Notestidy()

Dim w1 As Worksheet
Dim rng As Range
Dim FirstRow as Integer

'Application.ScreenUpdating = False

Set w1 = Worksheets("Sheet1")
FirstRow = 0

'find "Missing Audio" in column F
set rng = w1.Range("F:F").Find (What:="Missing Audio", LookIn:=xlValues, _
  LookAt:=xlPart)              'look at some additional parameters here
While Not rng is Nothing       'if we found it somewhere
  If FirstRow <> rng.row Then  'we haven't wrapped back around to the first found item
    If FirstRow = 0 then       'this is our first time through the loop
      FirstRow = rng.row       'Store off the first found row
    End If
    'Assign H = F & H for this row. 
    w1.Range("H" & rng.row) = w1.Range("F" & rng.row) & w1.Range("H" & rng.row) 
'------------------------
'update this line:
    Set rng = rng.FindNext After:=rng  'find the next occurrence
  Else
    set rng = nothing          'we've wrapped around the search, let's get out
  End If
Wend

Application.ScreenUpdating = True

End Sub

A couple of final notes:

• Be aware that .Find() will use whatever parameters were last set in the search dialog box and that parameters set in code will be reflected in the dialog box. There's nothing wrong with this, you just need to understand that anything you don't explicitly set when calling it from code will leave you using whatever random setting happened to be from your last search. You may want to look at the MS Docs on .find() for all the parameters.
• While on the topic of .Find() it will wrap around and find the first occurrence again. If you're not replacing what you're finding, you'll end up in an infinite loop, so you have to store off a marker of some sort (FirstRow, in this case) so you know when you get back there.
• On the w1.Range("H" & rng.row) = line, you can add some sort of delimited in there between the country code (Col F) and the string in Col H if you want. I didn't know what you may have wanted, so I just slammed them together.
• Step through your code in the debugger using the F8 key to execute 1 line of code at a time. That will help you understand what's going on in your code.