1. M

    Sum with Indirect

    Good morning! I would like to modify this formula in a macro so that it always looks at a specific column reference (column K) ActiveCell = "=SUM('Review Tab'!K:K)" I've changed it to this: ActiveCell = "=SUM(INDIRECT(" 'Review Tab'!K:K"))" However, VBA now thinks that everything after...
  2. H

    Create sheet after last sheet

    I have a sheet called "Costing Sheet" I have written code to put todays date - 5 days in col A1 on Sheet "Costing Sheet" and the copy the sheet after the last sheet and then to name sheet based on month and year in A1 for eg Oct-2019 I get an application defined or Object defined error and...
  3. G

    Delete all defined named ranges from a selection

    Is there a way to delete all named ranges from a selection? Here is the selection that I am trying to delete the named ranged from: Range("CompOverviewTable[[#Headers],[Competitor_one]]").Select Range(Selection, Selection.End(xlToRight)).Offset(-1, 0).Select
  4. S

    Help with CHOOSE function

    CHOOSE function has syntax CHOOSE(index_num, value1, [value2], ...) I have 100s of values (text values) to deliver to CHOOSE function. Instead of typing all those values every time, I want to keep those values as defined name. I tried to define a name and handed overt to CHOOSE, but it...
  5. A

    Help With User Defined Function

    I haver a user defined function to take the second to last word in a string and add 1 to it. When I run the macro with the function in it, I get "Sub Or Function Not Defined". When I go to the debugger, the "Rept" function is highlighted. User Defined Function: Public Function SECONDWORD(sw...
  6. G

    VBA code to define names of cells

    Hi, I am trying to design code to create a defined name for a cell based off the value of another cell. The cell that contains the name that I would like to be the defined name is in cell (“C2”). The cell that I would like to obtain the defined name is the cell above the new column create from...
  7. N

    VBA Sort a Column Named Disposition, but Disposition column will not be in defined column number it will vary

    Dear Friends, There are 43 columns Sort Column Header Named Disposition. But Disposition column will not be in defined column number (each time when we receive file columns will be shuffled)
  8. S

    Is it Possible to write a Match Formula with a Defined Name Range?

    Was trying to write a match formula with a defined name range. =MATCH("Satisfactory",Yellow, 0) This gives an N/A error. Is it possible? Thanks.
  9. G

    Create defined name based off another cells value

    Hi, I am trying to design code to create a defined name for a cell based off the value of another cell. The cell that contains the name that I would like to be the defined name is in cell (“C2”). The cell that I would like to obtain the defined name is the cell above the new column create from...
  10. S

    Count If Formula with a Defined Range

    How would you write a countif formula using a defined name range? My range...
  11. D

    Application defined or object defined error

    I had this line of code in my spreadsheet and it worked, it just printed the formula in the cell. .Range("J" & .Range("J" & .Rows.Count).End(xlUp).Row).Formula = "RC[-1]+RC[-2])" All I did was add = before the RC and it gave me an error. .Range("J" & .Range("J" &...
  12. L

    Excel workbook to PDF - cycle through drop list

    Hello! I am completely new to Macro, so please be patient with me I have a workbook - 10 tabs First Tab called Parameters Remaining 9 tabs are named by BRANDS I have a named range in cells =PARAMETERS!$B$1:$B$8 called LIST_BRANDS I have the path I would like the PDF's saved in cell $D$1 with...
  13. P


    Hi, Can any one tell me how to do conditional formatting for below scenario. I defined start date and finish date in cloumn c&d. I have a year calender starts from column J where as I defined calendar dates in Row3. Now with respective to column c & d dates, automatically XX to YY column...
  14. R

    VBA code - check if cells in range contain same value

    Dears, I would like to kindly ask you for your help regards VBA code..I'm looking for code which will be checking certain range of defined cells for values. If all values will be the same, then will execute next defined step/macro...If different, Msg Box will pop up...and defined macro will...
  15. M

    Index/Match question

    =INDEX($C4:$K4,MATCH(P$2,$C4:$K4,0)) I use this formula to match single numbers in P2 with the defined range. Is it possible to change this formula so that it works when there are comma separated 2 or 3 numbers in P2. And if one (or more) of these numbers in P2 match with the defined range, it...
  16. L

    VBA, Copy Cell formula to a defined Name

    We have a cell A1 with the formula (say) "=[Pricelist2019v2.xls]Company!$A$1:$L$22000" We want to use VBA to copy this to a Defined Name, (say) "RangeA". We have tried may possible ways of doing this without success. So suggestions appreciated as to how this may be accomplished!
  17. K

    VLOOKUP when table_array is a Table

    Is there an easy way to connect VLOOKUP from defined Table to another defined Table? Is there a better function to do this? I'm trying to get away from using relative cell/column locations. When I do a VLOOKUP in a table and reference another table, I get something like the...
  18. R

    Using SUMPRODUCT to SUM a Defined Range

    Hi all! Was hoping someone might have some more insight into how I can leverage named ranges and SUMPRODUCT. My objective is to create a business score card that contains subcategories within overall brands. So, for example, think about Brand X containing items in Subcategory A, B, and C...
  19. D

    How to delete multiple defined names in excel 2003

    how to delete multiple defined names in excel 2003. I came across this macro to do so but it is giving me a run time error. Not sure what is wrong with it. I used a excel spreadsheet from a friend that had a bunch of named cells and I don't know how to remove all of them. Please help Sub...
  20. R

    VBA fast way to delete rows in a 2-column range if the date is less than a defined date

    Hello everyone, I've defined a "Ddate" as being "Now()+14" I have a set of data on "Temp" sheet which has 2 columns. A is string and B is a list of dates. What I need to do is quickly run down these ~3,000 rows and delete any row where the value in B is less than or equal to "Ddate" I've...

Some videos you may like

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...