scope

  1. L

    VBA: How to delete Name Range based on Name + Scope

    Hi, I need a code to delete some duplicate Name Ranges which have identical names but have different scopes. For example, my file has two "List_Tabs" Name Ranges; I want to keep the one that has a Scope = Workbook and delete the one where the Scope refers to tab name "Info". How do you delete...
  2. T

    Excel VBA Range Scope for Find/Replace

    Greetings, I am interested in doing a Find/Replace on a specific range. Controlling the scope to Worksheet vs Workbook seems to be my issue. Doing a regular replace produces inconsistent results: Selection.Replace _ what:="abc", Replacement:="xxx", _ LookAt:=xlPart...
  3. R

    My VBA creates named ranges with special characters (!) - how can I delete that?!

    Hi all - please advise. How do I delete named ranges with special characters. Or prevent my vba from creating named range with special characters? My goal is to duplicate a sheet (eg Sheet A) that contains name ranges to another sheet (eg Sheet B). Due to duplicated named ranges - the name...
  4. M

    Bar Chart - Amending bar colour automatically

    Hi I have set up automated timeline (Gant Style) workbook for colleagues whichworks fine but they have now asked me if I can have the bar colour changeautomatically if a task is past due e.g. Green while it is in scope and redwhen it is Out of Scope. This is not something I have been asked for...
  5. M

    Understanding scope

    In order to have a variable visible to all controls in a particular form, does it need to be global? Is there a way to declare a variable to have the scope of all procedures triggered by controls in a from, or would you need to put all the code for the subroutines in a module, with a module...
  6. M

    Data Type Mismatch

    Hello, I am using the code below to add a record, but I’m getting a run-timeerror ‘3464’: Data type mismatch in criteria expression”. What am I doingwrong? Is it the spaces? CurrentDb.Execute "INSERT INTO archive_RET_Accumulated([REF_NUM],[Exam ID], [Exam Name], [Exam Agency], [OCC SL No]...
  7. M

    Quick formula question

    Hello I have a 40k line spreadsheet of data. I need help with a formula to put in AF3 to AF40,000 so for the first line it will look at columns AC3,AD3,AE3,AF3 and if the term "Out of Scope" is present in one or more of the cells, it returns "Out of Scope" and if not "In Scope". Thanks Mark
  8. M

    Formula help

    I need to get up to speed with formulas, but I'm battling to meet a deadline to draw out key info on a 30,000 row spreadheet. Basically I currently have a formula in U2 as follows: =IF(E2="","In Scope","Out of Scope") I want to expand it do do other things but have no idea where to start...
  9. C

    UserForm Level Variables Scope Question

    I have several variables declared at the top of my UserForm (outside of any procedure), some of them are ranges, ListObjects, and PivotTables. Is it necessary to use Set variable = nothing before closing the UserForm to remove them from memory, or will they automatically go out of scope when...
  10. T

    Hidden named ranges

    I have created a named range called MyNamedRange (scope Sheet1) referring to cell A1 of Sheet1. Then I hid all named ranges as follows: Dim nm As Name For Each nm In ThisWorkbook.Names nm.Visible = False Next nm To my surprise, I can now create...
  11. N

    Solve for Min Sum and Full Scope

    Hello, I'm working on building an analysis tool and have hit a bit of a roadblock. I have a data set for a list of vendors in rows and type of scope across the columns. I also have a "Preliminary" vendor in the uppermost row of values which represents a budget. Both the count of vendors and...
  12. A

    Ignore result IF

    Hello all, I have 3 columns with YES NO in first 2 columns and status in the 3rd. I'm using COUNTIF yes in both first 2 columns but I want to ignore the row if the status is 'Out of Scope' OR 'Complete' =COUNTIF('B. General' !$E:$F, "yes") <tbody> E F G Answer 1 Answer 2 Status YES NO...
  13. B

    Question on Scope

    Hi, I have two questions about scope. 1. Can two modules have the same subroutine names other than the first one called from the user form? Ie, I have two user forms with a button on each. I want one button to call the subroutine on one module and the second button to call a subroutine on the...
  14. C

    If function that returns message box error if true or creates file if false

    Hi I have a macro that populates a word document template from a table in excel. Users press a report generator button and it creates the file. However, for the cases as shown below where the user selects "compliance", "scope", or "vendor" in a specific cell i want the code to return a message...
  15. T

    help building an expression

    Hi! I need help building an expression for a field in a query I'm designing. I have columns of dates and types of reviews. Example: SiteID SiteDate Purpose 03500000 05/09/13 Initial Full Scope 03500000 08/17/15 Periodic Full Scope 03500001 05/09/13 Initial Full...
  16. C

    Problem with duplicated range names when copying worksheet

    I am creating an automated work log/invoicing system for a motor repair company. Work is entered into a job sheet and I have various cells where I have set data validation using lists (customer ID, vehicle reg etc). These reference entries in Excel tables. For example, the Customer ID cell is...
  17. A

    VBA Assign Range Names Automatically (Workbook Scope /Worksheet Scope)

    Excel friends, I would like to assign names to some specific cell references located within a range using a version of this code. It currently does not assign the names correctly. Can somebody help me by fixing it. Thanks! Sub AssignNametoRefs() Dim Rgn As Range Dim RefNames As String Dim...
  18. C

    Mutliple Criteria in Access query

    I am trying to write a query for multiple criteria in the query. Can someone please tell me what I am doing wrong? SELECT Deviations_Request.AppId, Deviations_Request.AppName, Deviations_Request.Datastdname, Deviations_Request.Devreqsubmitted, Deviations_Request.Nonadoptcomment...
  19. C

    If Statement Issue

    Below is the query I am working on. I am having an issue with my IIF Statement. Please tell me what I am doing wrong? I am trying to get the query to place a "DQIP" in the Deviations_Request.Information field if the Deviations_Request.Nonadoptcomment = "Taiwan is non GC country, DQIP is not in...
  20. M

    VBA code to change Word Comment SCOPE color

    Hello, So I have a desire to change the color of the SCOPE of my comments in word via VBA. I understand that changing comment author's changes the RANGE of the comment, but it does not change the SCOPE color.... anyone know how to achieve this? Thanks!

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top