Excel VBA - Define Range of rows from known static range as start to flexible range address as end
Need help with an Excel VBA process. Below is a screenshot of the current logic with the intended goal noted in the red box. Can someone assist with helping me set criteria to define the range or rows between a static row (11 - aka RowStart) and one above a flexible end row, with the end row being determined by a cell match? Copy of file attached as well. Mod in question is "ModCreateNewUserTEST". All other Mods are working as intended and do not need changes.
chart / data range to variable ??
Hello, I draw a chart with specific data range C11:C20 like below. but I want to change data range of chart dynamically. I made data range text in cell E5 and it is B10:B100. How can I make E5 text as data range text of chart ? is it possible? I'd like to redraw chart dynamically when I change E5 cell text.
Using Target Data from another Sheet to link to Power Query Data
Hi all, I'm building a training dashboard and have used Power Query to pull through the data that comes directly from the learning system. I can show how many units each person, and then each department has completed. However, Different Departments, and then divisions within those departments have different mandatory competencies. I have these set up in another sheet in my workbook as a course database. I've set it up for Course - Tier (Mandatory or not) - Department - Division. So this table shows how many mandatory units each department must complete. My issue when trying to create relationships in that there are duplicates, as some departments have a number of the same courses. Everything else is working ok - but I need to show how many courses have been completed vs the amount of courses they need to complete each year, and in case this changes over the year (for instance if we add a new course) I want that to be dynamic instead of just saying "out of 18" for instance. Can anyone provide any guidance? I'd greatly appreciate it!
Using ClearContents on a Range Defined with Variables
I am trying to ClearContents of a range of cells using Variables AND text to define the range. I have the following code : Set WithdrawalsRange = WBSC.Range(WBSCCol.Row & "F:" & WBSCCol.Row & "AY") WithdrawalsRange.ClearContents WBSCCol.Rows = 4 that I use to perform other tasks with no problem. So I want to ClearContents of the range "4F:4AY" in the WBSC worksheet. I am struggling to come up with the right syntax. So my Set statement I want to be translated as :- Set WithdrawalsRange = WBSC.Range("4F:4AY") but I keep getting a Run-time error '1004'. Applicsation-defined or object-defined error. Would be grateful if someone could show me the error of my ways. WBSC is a Worksheet WithdrawalsRange is a Range Many thanks ChrisKPI dax logic not giving correct counts but table visual does
Hi Team, I have below dax for the Doors open kpi: Doors Open = VAR _count = CALCULATE( MAX( BMSDoorStoreJoin[CreatedOn] ), BMSDoorStoreJoin[Status] = "Open" ) VAR _siteid = MAX(BMSDoorStoreJoin[SiteId]) RETURN CALCULATE( COUNT( BMSDoorStoreJoin[CreatedOn] ), BMSDoorStoreJoin[Status] = "Open"&& BMSDoorStoreJoin[CreatedOn] = _count && BMSDoorStoreJoin[SiteId] = _siteid ) Now, this returns the value of 1 which is wrong according to filtered SiteId(StoreName). My expected output from this logic is I want to find unique count of Doors Open(Site[Sitecode]) for the max of createdon date. For example, the data below for Sitecode 472 we have Status as closed and we don't have open. So it should retun nil. but in this case it returns 1. My table visual also shows correctly that it has only closed status. When i use my kpi logic in that visual it gives correct count. Please advise! PFA file here BMS Chillers Insights.pbix Thanks in advance! SergeiBaklanCounting Cells by Color with conditional formatting
Each row represents a location. Each column is a metric that we track for each location. The metrics and goals are the same for each location. Each cell changes color based on conditional formatting. The cells of each column have the same conditions but the conditions change per column. The cells change constantly. New tabs are created monthly for each district. I need the total of the like colored cells to compute to the matching rows of the color coded columns below. Is this possible? HansVogelaar
