+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    Registered User
    Join Date
    11-24-2016
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    6

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

    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...

    Please Login or Register  to view this content.
    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
    Last edited by 6StringJazzer; 06-16-2021 at 08:43 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

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

    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.

    Please Login or Register  to view this content.
    Last edited by 6StringJazzer; 06-16-2021 at 08:51 PM.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    11-24-2016
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    6

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

    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?

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

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

    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:

    Please Login or Register  to view this content.
    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:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-24-2016
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    6

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

    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!
    Attached Files Attached Files

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

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

    First, I discourage the global use of

    Please Login or Register  to view this content.
    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:

    Please Login or Register  to view this content.
    Last, we want to remove the first semi-colon, only if there is one.

    Here is the corrected and tested code:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    11-24-2016
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    6

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

    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!

  8. #8
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

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

    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.

  9. #9
    Registered User
    Join Date
    11-24-2016
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    6

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

    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?
    Attached Images Attached Images
    Last edited by HurdlurMack2013; 06-18-2021 at 02:42 AM.

  10. #10
    Registered User
    Join Date
    11-24-2016
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    6

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

    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

  11. #11
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

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

    Quote Originally Posted by HurdlurMack2013 View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Extracting hashtags and mentions from textual column
    By alexM123 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-23-2020, 01:37 PM
  2. [SOLVED] Insertion of Random Hashtags in each row of a column
    By WhatsGig in forum Excel General
    Replies: 4
    Last Post: 08-16-2017, 09:09 AM
  3. Macro to insert cell comments from a list in another sheet
    By Bambina in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-02-2015, 09:02 AM
  4. [SOLVED] check column headers against sheet names if sheet not exist create it
    By AkaTrouble in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-13-2015, 05:59 PM
  5. Replies: 0
    Last Post: 05-20-2014, 03:23 AM
  6. Transfer comments from Sheet 1 to Sheet 2 as list
    By Welshandproud1 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-16-2014, 06:52 AM
  7. Add comments to cells using VBA (comments from list)
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-15-2010, 12:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1