Sub to check for Hashtags in comments column using list in different sheet

[HurdlurMack2013]

Hi,

I'm new to VBA so hoping to get some help.

I found a sub to extract hashtags out of a cell (column J, in sheet 2) and place into another cell (column S, in sheet 2). The code references a list of hashtags in another tab within the workbook (column A, in sheet 3).

The sub originally referenced the same sheet when looking at the list of Hashtags but I want it to to look at another sheet. I thought I needed to declare and set the two sheets (Sht2 for "Sheet2" and Sht3 for "Sheet3") and made adjustments to the code to reference these sheets and cells. It seems to compile but nothing is appearing in the column when I attempt to run the macro.

Would greatly appreciate someone pointing out exactly what I'm doing wrong and a possible solution, thanks.

Here's my code...

Sub Tags()

    Dim wrdLRow As Integer
    Dim wrdLp As Integer
    Dim CommentLrow As Integer
    Dim CommentLp As Integer
    Dim fndWord As Integer
    
'   Declare sheets that we will be referencing
    Dim Sht2 As Sheets
    Dim Sht3 As Sheets

    On Error Resume Next 'Suppress Errors... for when we don't find a match
    
'   Define worksheets that contain data
'   ignore Set Sht = Sheets("Sheet1")
    Set Sht2 = Sheets("Sheet2")
    Set Sht3 = Sheets("Sheet3")
    
    'Get last row for hashtags based on column A in sheet3 (aka Hastag Lookup)
    wrdLRow = Sht3("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row
    
    'Get last row for posts based on column J in sheet2 (aka All Merged Data)
    CommentLrow = Sht2("Sheet2").Cells(Rows.Count, "J").End(xlUp).Row
    
    'Loop through lists and find matches....
    For CommentLp = 2 To CommentLrow
    
        For wrdLp = 2 To wrdLRow

            'Look for hashtags...
            fndWord = Application.WorksheetFunction.Search(Sht3("Sheet3").Cells(wrdLp, "A"), Sht2("Sheet2").Cells(CommentLp, "J"))
            
            'If we found the hashtag....then
            If fndWord > 0 Then
                Sht2("Sheet2").Cells(CommentLp, "S") = Sht2("Sheet2").Cells(CommentLp, "S") & "; " & Sht2("Sheet2").Cells(wrdLp, "S")
                fndWord = 0 'Reset Variable for next loop
            End If
            
        Next wrdLp
        
        Sht2("Sheet2").Cells(CommentLp, "S") = Mid(Sht2("Sheet2").Cells(CommentLp, "S"), 3, Len(Sht2("Sheet2").Cells(CommentLp, "S")) - 2)

    Next CommentLp

End Sub

Moderator's note: Thank you for your first post in nearly 5 years! Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because it's your first post. --6StringJazzer

[6StringJazzer]

Try these changes. I can't test it without your file but I see these issues:

1. You declared these as Sheets. Worksheet is the correct type. I don't even know what happens if you try to use Sheets as a data type; it is probably treated as a collection.
2. Once you declare a Worksheet variable you just use that variable. You don't have to also include the sheet name.
3. You need to qualify Rows, otherwise it will refer to the default sheet instead of the one you mean.

Worksheets("Sheet2")
Sheets("Sheet2")

are the same as just

Sht2

but Sht2("Sheet2") doesn't mean anything.

Sub Tags()

    Dim wrdLRow As Integer
    Dim wrdLp As Integer
    Dim CommentLrow As Integer
    Dim CommentLp As Integer
    Dim fndWord As Integer
    
'   Declare sheets that we will be referencing
    Dim Sht2 As Worksheet
    Dim Sht3 As Worksheet

    On Error Resume Next 'Suppress Errors... for when we don't find a match
    
'   Define worksheets that contain data
'   ignore Set Sht = Sheets("Sheet1")
    Set Sht2 = Sheets("Sheet2")
    Set Sht3 = Sheets("Sheet3")
    
    'Get last row for hashtags based on column A in sheet3 (aka Hastag Lookup)
    wrdLRow = Sht3.Cells(Sht3.Rows.Count, "A").End(xlUp).Row
    
    'Get last row for posts based on column J in sheet2 (aka All Merged Data)
    CommentLrow = Sht2.Cells(Sht2.Rows.Count, "J").End(xlUp).Row
    
    'Loop through lists and find matches....
    For CommentLp = 2 To CommentLrow
    
        For wrdLp = 2 To wrdLRow

            'Look for hashtags...
            fndWord = Application.WorksheetFunction.Search(Sht3.Cells(wrdLp, "A"), Sht2.Cells(CommentLp, "J"))
            
            'If we found the hashtag....then
            If fndWord > 0 Then
                Sht2.Cells(CommentLp, "S") = Sht2.Cells(CommentLp, "S") & "; " & Sht2.Cells(wrdLp, "S")
                fndWord = 0 'Reset Variable for next loop
            End If
            
        Next wrdLp
        
        Sht2.Cells(CommentLp, "S") = Mid(Sht2.Cells(CommentLp, "S"), 3, Len(Sht2.Cells(CommentLp, "S")) - 2)

    Next CommentLp

End Sub

[HurdlurMack2013]

Hi Jeff,

Thanks for the quick response. I made the changes you've highlighted in red.
FYI - with just the changes made (in red), when I run it I am now getting semi colon's inplace of the hashtags e.g. ;;

Sorry, not clear on what you mean by #3. I suppose that's sort of the main challenge I'm not understanding.

I've tried to attach the file for you to review but getting an Invalid File message when trying to upload. It's a 36kb MS Excel Macro Enabled Template, can't imagine that's an issue as the list of supported/allowed file types.

Is there another way to get this to you?

[6StringJazzer]

Template file types are not supported. You can zip it then upload the zip file. Or you can open the template and save it as a .xlsm file and upload that.

On #3:

Here is the line of code I'm talking about:

    wrdLRow = Sht3.Cells(Rows.Count, "A").End(xlUp).Row

Rows is a property of Sheet or Range. If you do not tell VBA what sheet are referring to, it will use the default.

• If the code is in the module for a worksheet (e.g., Sheet1), that worksheet is the default
• If the code is in a standard module (e.g., Module1) then the ActiveSheet is the default.

In your case, it is doubtful that Sheet3 is the default, so it will use the wrong number of rows. Generally it is safer to qualify such references, and show explicitly what you want to refer to:

    wrdLRow = Sht3.Cells(Sht3.Rows.Count, "A").End(xlUp).Row

[HurdlurMack2013]

Hi Jeff,

Uploaded a Zip file, hopefully it attached to this post correctly.

I see what you're saying now re #3.

This code is in a Module folder separate to the MS Excel Object folder, in the Project Explorer pain.

Thanks in advance - looking forward to getting this working!

[6StringJazzer]

First, I discourage the global use of

On Error Resume Next

This will hide all errors, not just the one you expect.

Second, if you use InStr instead of Search, you are using a built-in VBA function so it may be faster, and it will not raise an error if the string is not found. So you can remove the line shown above.

In the following line of code you are referring to the wrong sheet and the wrong column, corrected in red here:

                Sht2.Cells(CommentLp, "S") = Sht2.Cells(CommentLp, "S") & "; " & Sht3.Cells(wrdLp, "A")

Last, we want to remove the first semi-colon, only if there is one.

Here is the corrected and tested code:

Sub Tags()

    Dim wrdLRow As Integer
    Dim wrdLp As Integer
    Dim CommentLrow As Integer
    Dim CommentLp As Integer
    Dim fndWord As Integer
    
'   Declare sheets that we will be referencing
    Dim Sht2 As Worksheet
    Dim Sht3 As Worksheet

    'On Error Resume Next 'Suppress Errors... for when we don't find a match
    
'   Define worksheets that contain data
'   ignore Set Sht = Sheets("Sheet1")
    Set Sht2 = Sheets("Sheet2")
    Set Sht3 = Sheets("Sheet3")
    
    'Get last row for hashtags based on column A in sheet3 (aka Hastag Lookup)
    wrdLRow = Sht3.Cells(Sht3.Rows.Count, "A").End(xlUp).Row
    
    'Get last row for posts based on column J in sheet2 (aka All Merged Data)
    CommentLrow = Sht2.Cells(Sht2.Rows.Count, "J").End(xlUp).Row
    
    ' Initialize
    Sht2.Range("S:S").ClearContents
    Sht2.Range("S1") = "Tags"
    
    'Loop through lists and find matches....
    For CommentLp = 2 To CommentLrow
    
        For wrdLp = 2 To wrdLRow

            'Look for hashtags...
            fndWord = InStr(1, Sht2.Cells(CommentLp, "J"), Sht3.Cells(wrdLp, "A"))
            
            'If we found the hashtag....then
            If fndWord > 0 Then
                Sht2.Cells(CommentLp, "S") = Sht2.Cells(CommentLp, "S") & "; " & Sht3.Cells(wrdLp, "A")
                fndWord = 0 'Reset Variable for next loop
            End If
            
        Next wrdLp
        
        If Left(Sht2.Cells(CommentLp, "S"), 2) = "; " Then
           Sht2.Cells(CommentLp, "S") = Mid(Sht2.Cells(CommentLp, "S"), 3, Len(Sht2.Cells(CommentLp, "S")) - 2)
        End If
        
    Next CommentLp

End Sub

[HurdlurMack2013]

Awesome, this is working like a charm.

Have to admit this VBA stuff is hard but I'm going to make an effort to learn.

Really appreciate your help Jeff, fantastic work.

Cheers!

[6StringJazzer]

Glad to help. VBA is a programming language and people treat it like, oh, I know Excel, I'll just learn to code. But I spent many years as a professional programmer and am still learning how to best use VBA.

[HurdlurMack2013]

Yeah, I spent a couple of years trying to learn to code but I had to abort due to family reasons, wish I'd stuck with it, might go back one day.

FYI - I tried that sub on the actual dataset I'm working with and it threw up an error - see screenshot.

Just adding that when I hover over 'Sht2.Rows.Count' in the error it shows a popup saying 'Sht2.Rows.Count = 1048576'... this seems to be counting all rows when there are actually only 515000 rows of data.

I should also highlight that column J (containing the comments featuring hashtags) also contains blanks cells between rows. Not sure if this matters.

Please note that I'm now referring to Sheet1 (All Merged Data) and Sheet2 (Hashtag Lookup) sheets, and have amended the variables to point to these sheets.

Hoping you can help again?

[HurdlurMack2013]

Hi Jeff,

Hoping you can have another look at this one.

As noted above, it's breaking when I bring it into the actual file I'm working on... not sure what's happening as copy and pasted and amended sheet names.

Thanks in advance

[6StringJazzer]

there are actually only 515000 rows of data.

Declare CommentLrow as Long instead of Integer. The max value of Integer is 32.767 and 51.500 is bigger than that. (The million isn't the problem.)

Just about any time you declare a variable to represent rows or columns it should be Long. I just use integer for loop counters and other things that are guaranteed to be under the size limit.