2018-10-22         Phyllis

How to add an if statement in a DAX formula measure?

I am new to DAX and have created two measures to get the total pay per employee then lookup that total pay into a table and return a value in my Power Pivot.Total Pay Measure:NMRPaySum:=SUMX(Pay,[Regular Pay]+[Overtime Pay]+[Other Pay])Range Lookup Measure:SSSContributionEE :=CALCULATE ( VALUES ( SSSContribution[EE] ), FILTER ( SSSContribution, [NMRPaySum] >= SSSContribution[Lower Bound] && [NMRPaySum] <= SSSContribution[Upper Bound] ))However, I need the range lookup to only calculate if the employee type is satisfied.The logic f...

 excel                     3 answers                     30 view
 2018-10-22         Venus

Expanding Zip Codes From County Level For Multiple Vendors

I will try and explain this as best as possible.I have a table in excel/powerbi that lists the servicing coverage area for a new product we are releasing at work.I have a secondary table that contains state, county, zip code, fips codes, lat, long of all counties in the United States.The first table referenced above contains a partner ID, partner state, partner county -coverage, several survey response questions, as well as the pricing information for the particular product. The issue I've run into is the need to expand each listed county (currently about 16,000 records) do...

 excel                     1 answers                     79 view
 2018-10-22         Tyler

How to stop password prompt using DAO in Excel VBA and Access

I am using DAO to run queries on a password protected Access database using vba in Excel, occasionly while running the sub an instance of Access is opened up along with a window asking for the database password, not entering a password and pressing cancel makes no difference, the query still runs with the output displayed, is there any way to stop access opening up and asking for a password?Dim MyDatabase As DAO.DatabaseDim MyQueryDef As DAO.QueryDefDim MyRecordset As DAO.RecordsetDim DB_Name As StringDim cond As StringDim pWord As StringDim wb As Workbook: Set wb = ThisWor...

 excel                     2 answers                     57 view
 2018-10-22         Ted

Excel VBA: Paste Contents From Other Workbook Into Active Workbook Sheet2

As the title says, the below is my attempt at (within my active workbook) opening another workbook, copying its contents, then pasting it within my current workbook's 2nd sheet. But I'm unable to pass the act of pasting. I've been unable to find relevant examples online to this particular case.Sub SL()Dim x As WorkbookSet x = Workbooks.Open("C:\Stuff.xlsx")x.Sheets("SheetName").Cells.CopyActiveWorkbook.Sheets("Sheet2").Cells.PasteSpecialEnd SubCan someone just explain what I should be putting in there, please? I believe the following will give you a helping hand to achie...

 excel                     1 answers                     60 view
 2018-10-22         Lena

VBA add signature to email that uses RangetoHTML (rng to Range) Function

I have the following code and it creates the email almost perfectly. What it doesn't do is maintain the default signature that is visible before it pastes the RangetoHTML results.How do I get my signature back?This is almost entirely drawn from Ron de Bruin's code samples and as I said, it all works very well except for the signature bit. This is an Outlook created signature, so I do have an htm copy of it locally. I did experiment and found that nothing, not even additional text or another string, will appear after the ".body = Selection.Paste". No, changing that to "....

 excel                     1 answers                     61 view
 2018-10-22         Brook

Rows to columns in excel with names and dates

I wondered if anybody could help me out with a small problem. I am working on a big spreadsheet that contains names and the dates but i need to change a layout. To do this manually would take me an ages and I wondered if there is a quicker way. This is what I have:Column A Column B column C John Smith start date1 end date1John Smith start date2 end date2John Smith start date3 end date3Jane Doe start date1 end date1Jane Doe start date2 end date2Jane Doe start...

 excel                     1 answers                     63 view
 2018-10-22         Megan

creating an excel vba loop to list horizontally, dates between a start and finish date for each set of vertical dates

I have a list of start and finish dates Cols J & K this list is dynamic. I need to list the individual dates between the two dates to a row (P13) to the right hand side of each set of dates horizontally. The Exit Sub code is to stop the row updating and jump to the next row rather than exiting the sub. I have acheived this for one row, the code I am using is:Dim StartDate As DateDim EndDate As DateDim NoDays As IntegerIf Range("L13") <> "No" Then Exit SubEnd IfStartDate = Range("J13").ValueEndDate = Range("K13").ValueNoDays = EndDate - StartDate + 1Range("P13")...

 excel                     1 answers                     64 view
 2018-10-22         Sandy

increment a column going row wise usering sum product function Excel

Table1 datai need sumproduct of c1 with c2 , c1 with c3 and so on wile scrolling down the formula like table 2some thing like this Paste the below formula to any cell where you would want C1+C2, then drag it down:=SUM($D$5:$D$12)+SUM(OFFSET($D$4,1,ROW(1:1),8))What this does is it first sums D5:D12, which is the sum of your C1, then makes use of OFFSET to SUM C2 values in your first cell. Once you drag this down, the ROW(1:1) will move to ROW(2:2), which adjusts the OFFSET to capture the C3 values. This is as well true when you drag it down again for the C4 values. ...

 excel                     1 answers                     67 view
 2018-10-22         Myron

How to filter, count sum and loop in the same Sub

I have table like one in the picture (only bigger). In column A some numbers duplicate. I need to filter column A and count sum of column B. Like in the second picture.If sum of column B is bigger than 90, then count that as one.I need to do the same procdure with each unique number in column A.In short: I need to know how many unique column A ID's have sum of column B bigger than 90.I figured to use "For Each Next", but since I can't figure out how to make code use only unique values, I copied whole column A to different Worksheet, removed duplicates and used it as refere...

 excel                     1 answers                     65 view
 2018-10-22         Marsh

Show formula with values instead of reference in EXCEL

I have similar problem as described in this topic:Show formula of a cell, but values instead of referencesBut I need to show as in formulas directly in excel sheet. Thanks for answers in advance! Using @SiddharthRout Function...First; Place the Function as a module in your workbook. Under the Developers Tab, select Insert_Module, Copy and paste the Function.Second; Save the below sub in the Project window in your ThisWorkbook folder...Sub ShowFormulValues()Dim lRow As LonglRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row Range("B1:B" & lRow).Formula = "=ConvertToV...

 excel                     1 answers                     65 view
 2018-10-22         Heloise

Appropriate Excel VBA loop function?

apologies as I'm quite new with this. I have this VBA macro: Sub ScoreTest()Dim score As Integer, result As Stringscore = Range("D2").ValueIf score >= Range("E2").Value Then result = "pass"Else result = "fail"End IfRange("F2").Value = resultEnd SubOf course, this only works for Row 2. I edited the macro to apply to rows 2 til 16, but that was just me copy pasting so it looked like so: Sub ScoreTest()Dim score As Integer, result As Stringscore = Range("D2").ValueIf score >= Range("E2").Value Then result = "achieved"Else result = "failed"End IfRange("F2").V...

 excel                     1 answers                     66 view
 2018-10-22         Anna

Scrape website with XML HTTP request with Excel VBA: wait for the page to fully load

I'm trying to scrape a product price from a webpage using Excel VBA. The following code is working when using VBA Internet Explorer navigate request. However I would like to use an XML HTTP request instead to speed up the scraping process. In the IE request code I tell the application to wait for 3 seconds to have the page fully load and be able to scrape the product price. If this line is not included it won't find the price.I tried to change this with an XML HTTP request (see the second code) but without success. No price output was found. It seems that the code tries to ...

 excel                     1 answers                     47 view
 2018-10-22         Lance

COUNT column based on criteria, with one of the criterias being the newest date

I'm trying to make a rapport that shows the number of different responses the different sellers get in excelCustomerID Respons Seller Date 1 YES Jensen 08.08.2018 1 NO Chris 12.08.2018 2 YES Jensen 01.08.2018 3 NO Jensen 05.08.2018If I have this dataset and I make a COUNT IFS function I get thisSeller Yes NOJensen 2 1Chris 0 1However since the costumer contacted us again and changed his yes to a no Jensens yes should be canceled out.In my mind this would be easy to solve is the count only counts the newest re...

 excel                     1 answers                     10 view
 2018-10-22         Humphrey

Excel vba: global variables are assigned when workbook is opened; get erased if an error occurs

I have a workbook that has global variables declared:Public var1 As LongPublic var2 As LongPublic var3 As Long...Public varN As LongI also have a workbook open sub that assigns values to global variables when a workbook is opened:Private Sub Workbook_open() Application.Calculation = xlCalculationAutomatic Call calculateRows Call assignVariables ThisWorkbook.Worksheets("Filling").Protect "somepass", UserInterfaceOnly:=True MsgBox ("DONE Workbook_open")End SubIt works fine. However, I also have a worksheet selection change sub:Private Sub Worksheet_SelectionCha...

 excel                     1 answers                     10 view
 2018-10-22         Sebastiane

Set an excel button to open a url shown in a cell

I would like to create a button to open a url that is shown in a excel cell. the url is generated by the user filling in some information so i'd like to put a button next to the end url which will take the user directly to the page they requested.could someone please help with this question?ThanksIan You could use the solution from there Open an html page in default browser with VBA?:Public Sub ButtonClick() ThisWorkbook.FollowHyperlink (Range("B2").Value) 'TODO: change cell!End Sub [XXX]

 excel                     1 answers                     12 view
 2018-10-22         Eve

VBA: Use of "On Error" when calling a sub in another workbook,

I am building a process capable of sequentially launching and running subroutines in other multiple Excel workbooks. This is controlled by a 'master' batch launcher. If there is any error in any one of these spreadsheets being run, the launcher needs to quit the run of that workbook and move onto the next workbook in the batch. This process must be continuous without user input, so any error dialogue boxes need to be suppressed.Simplified to its core essence, my code is as such:For I = 1 To NumberOfSubsToRun On Error GoTo ErrorHandler Application.Run (SpreadsheetName ...

 excel                     1 answers                     14 view

Page 1 of 1021  |  Show More Pages:  Top Prev Next Last