Posts by manster_mg

    Hello forum,

    I have a weekly Excel workbook with a separate Sheet for each workday. Each Sheet has data that I want to convert into a table with VBA. I recorded the code below but it will not function correctly after the first sheet is saved. The error message comes back with "a table can't overlap another table". Is there a way to count the number of current tables in the workbook and have VBA create a new table with the next Table#?

    I appreciate your feedback,

    Thank you

    Excellent that worked! But before I read your post I was playing with this and tried to set the Worksheet differently using the code below.

    The error I encountered was "object required". Isn't a Worksheet an object?

    Dim wsActiveSheet As Worksheet
    Set wsActiveSheet = Application.ActiveSheet

    Thanks and Happy New Year!

    Hi Rory, I tried your suggestion and received the same error message. The macro is being used on an excel file that is system generated and opened from email (Outlook). As far as I know there are no ranges set before I get the file and there are no other filters in use, that I can see. I'm still a novice with using VBA so I apologize if I am missing any of the basics and I probably don't have a thorough understanding of Range and Range Properties. To my thinking it's strange that the error message is requesting that a single cell needs to be selected in a range and to try again. Isn't the whole idea of using a filter based on multiple cells?

    Hello forum,

    The line that is meant to apply the autofilter to the range A5 to Q & LastRow is not executing as expected.

    The error message I get is "can't be applied to the selected range. Select a single cell in a range and try again."

    How can I correct this?

    Happy New Year!

    I keep getting an error when trying to use the code below (Compile error - expected: end of statement). If I replace the text string " weeks" with an integer the code works fine. What am I doing wrong?

    Cells(LastRow + 6, "A").Formula = "=CONCATENATE(ROUND(DAYS(A" & LastRow + 5 & ",TODAY())/7,0)," Weeks")"

    I don't understand the difference between "Automatic" and an inbuilt Excel feature. I would rather have used a solution using AutoFilter but since that seems to be inefficient would you mind showing me how to use OR in the code below for conditional formatting? I need to format cells in column C with values 7, 10, and 42. Thanks!

    I want the macro to format cells in the worksheet when the value in column A equals "30", "42" or "7". Below is the code I recorded but since my data changes day to day this will not work. I had a macro in the past that worked with the AutoFilter and it was so much more simpler but I lost it. Any help would be appreciated.

    When I use Range.End to select a range nothing is getting selected and the cursor ends up exactly 10,000 rows past the target row. What am I doing wrong? The code is posted below:

    Dim lRow As Long
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("E1" & lRow).Select

    I am using this formula for now:

    =IF(INDEX('Supplier Data'!C:C,MATCH('Supplier Eval'!A3,'Supplier Data'!A:A,0))<1000000,"zero allowed","1 per M")

    If the index match result is "zero allowed" I need to Index the value in 'Supplier Data'!D:D with same match arguments and if the returned value is >zero the formula should return -1 if <zero return +1
    If the index match result is "1 per M" I need the formula to Index 'Supplier Data'!D:D again with same match arguments and if that value is >1 per million (the index match value will be an number/integer) return a value of -1, if <1 per million return +1.
    If you can figure this out you are awesome!

    Thank you,

    Thank you gijsmo, I decided to go with the first option as it looks like I will be able to use this for many more instances. I am still a novice to VBA and Excel and I am trying to understand how the code operates. Can you tell me if my observations below are correct?
    After Dims are declared:

    Wow, I didn't think I could walk it through but this is how my brain works.
    Did I get it right?

    I have a custom function that was created to extract integers from a simple 11 character string that was alphanumeric with a hyphen. I need to modify or create a new custom function that can extract integers (with hyphens in some instances). The new string is variable in length and the integers I need to extract are on the left side of a vertical slash (vertical bar). The custom function I am currently using is posted below. If any one can help with the modification needed or script new code for the new string I am dealing with I would be very grateful.

    Here is an example of the old string and new string:
    Old String: ABC-1234567
    New string 1: 1234567-99 | TEXT MORETEXT EXTENDEDTEXT - 23.8 XX YYYY 16:9
    New string 2: 1234567 | BLAH BLAH EXTENDEDBLAH XYZ 123


    I am trying to select a range using VBA and have run into run-time error '1004': Method 'Range' of object '_Global' failed.
    If I write the code this way it works fine:

    Range("A" & lastrow  + 2).Select

    I have lastrow set this way:

    lastrow = Worksheets("Detail Inventory").Range("B5").End(xlDown).Row

    But if I expand the range by writing the code this way:

    Range("A & lastrow + 2:U47").Select

    I get the run-time error.

    Anybody have an idea what I am doing wrong?


    I have a custom function created to extract integers from a string. This initially worked fine when the strings consisted of a set format of text+integer. Now I have instances where the string consists of integers+text+integers but I only want to capture the first set of integers. I've copied the code for the function below and I'm looking for a way to modify so I can extract the first set of integers from the string.
    I've also attached a workbook as an example.