Search:

Type: Posts; User: Misca; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.09 seconds.

  1. Re: Excel / powerquery formula to compare dates/time when other value is matching.

    Here's a piece of M-code that groups rows by Shipping number and extracts the min & max time stamps + calculates a column to check if the duration between the scans is more than an hour:
    let ...
  2. Re: Copy the data in a Pivot Tabe to another tab

    Jon Peltier wrote an excellent article on how to reference Pivot Table Ranges in VBA:

    https://peltiertech.com/referencing-pivot-table-ranges-in-vba/

    Depending on your PT structure you may have...
  3. Re: Code for deleting rows not working correctly

    I believe the trick is to make your code work from the bottom to the top.

    Try something like:
    Sub DelRows()

    Dim LastRow As Long
    Dim cRow As Long


    LastRow = Cells(Rows.Count,...
  4. Re: Adjust holiday (Xmas) break in between the project.

    When you're saying "days" do you mean "workdays" or days in general ( = including weekends)? If it's workdays you're talking about you can use the WORKDAY formula. The formula syntax is =WORKDAY...
  5. Re: Uploading new reports into a PowerQuery table

    It's usually a good practice to try to get your data as close to the "source" as possible. If you can't connect directly to the database you're getting your data from, have it dumped in a folder and...
  6. Replies
    1
    Views
    311

    Re: DAX, how to last value in the period total

    If your Month is actual date you should get what you're after with something like:

    Last Day's Head Count:=CALCULATE(sum(Table1[Headcount]);FILTER('Calendar';'Calendar'[Date]=max(Table1[Date])))
    ...
  7. Thread: Numbers

    by Misca
    Replies
    6
    Views
    151

    Re: Numbers

    I'd say your best option would be formatting the cells as numbers and reduce the decimals to 0.
  8. Re: macro to convert negative values with Brackets

    Sounds like what you're really after could be number formatting. Putting your values inside brackets would turn your number into text.

    You can google for Custom number formats or search for...
  9. Replies
    1
    Views
    116

    Re: Combine 2 column in pivot table

    Just drag the Address field next to the Name field.

    If the Address appears on the column A change the Report Layout (on the Design tab of the PivotTable Tools) to Tabular Form.
  10. Replies
    4
    Views
    75

    Re: Clear contents for duplicates in range

    Try something like:

    Sub RemoveDuplications()

    Dim X As Long


    For X = 37 To Range("A" & Rows.Count).End(xlUp).Row
    If Application.WorksheetFunction.CountIf(Range("A36:A" & X), Range("A" &...
  11. Replies
    12
    Views
    178

    Re: Show blank if "x" or "X", else run a VLOOKUP

    Welcome to the message board!

    Excel isn't too picky about letter casing so you don't have to worry about the "x" or "X" part. That being said all you have to do is combine your two formulas:
    ...
  12. Replies
    3
    Views
    124

    Re: VBA enter password to unhide worksheet

    I might try something like:
    Sub ToggleSheetVisibilityWithPassword()

    Dim WS As Worksheet
    Dim MyPassWord As String
    Dim EnteredPassword As String


    'The worksheet:
    Set WS = Sheets(2)
  13. Replies
    7
    Views
    457

    Re: VBA Pivot Items in Power Pivot

    I have both PTs on Sheet1. PT1 is the one I want to filter and PT2 has the filter values (from my dimension table) in rows with the rowcount (any measure should do) in the values. The measure should...
  14. Replies
    7
    Views
    457

    Re: VBA Pivot Items in Power Pivot

    So far the only solution to my problem seems to be the helper pivot table that had the pivot items from the first pt on the row field. It's not the prettiest solution in the world but it does the...
  15. Replies
    1
    Views
    390

    Re: Date axis on graphs

    I seem to have almost solved my problem by changing the date values from my date table to the date value of my values table and then grouping my date values into buckets of 7 days.

    Not totally...
  16. Replies
    1
    Views
    390

    Date axis on graphs

    I've got a basic star schema data model in Power BI. In my Dates table I have "the usual" columns for date, year, month (name + number) and ISOWeek. I've created the date hierarchy that I want to...
  17. Replies
    3
    Views
    150

    Re: Default zero number in a cell

    This piece of code chenges the value of set range to 0:

    Range("A1:C25").Value = 0
    I'm sure you know / will figure our how to adjust the range of cells to match your needs.
  18. Re: add the same number until it exceeds another and return that number vba

    Sounds like you have headers above your values. So far you've said your values start from A1.

    There's an easy fix for this: Set the Rng to start from cells(2,1)
  19. Re: add the same number until it exceeds another and return that number vba

    If this your table it looks like your numbers are formatted as text.

    The code in my macro checks the value is greater than zero. The value of text is always zero. Try this instead:
    Sub...
  20. Re: add the same number until it exceeds another and return that number vba

    Noticed a tiny bug in my code: I used "smalled than" instead of "greater than"

    Also came to think that my code will not work correctly with negative numbers and decimals.
  21. Re: add the same number until it exceeds another and return that number vba

    Welcome to the message board!

    Here's how I'd solve this:
    Sub MultiplyValues()

    Dim c As Range
    Dim Rng As Range


    With ActiveSheet
  22. Re: one Column with 6000 rows convert into 6 columns into 1000 rows based on conditions

    Sounds like something you should do with Power Query (or "Get and Transform" as it's currently called):

    You can find Power Query from the Data tab of your Excel ribbon. Use the "Get Data" button...
  23. Replies
    5
    Views
    196

    Re: how to reference the same row

    The Application.Caller in the second one returns the name of the button clicked. If the column makes any difference you can check it as well:

    Dim R As Long
    Dim C As Integer


    With...
  24. Replies
    1
    Views
    107

    Re: Getting an average between dates

    By far the easiest solution would be to use the AVERAGEIFS formula:

    =AVERAGEIFS(B2:B22,A2:A22,">="&D3,A2:A22,"<"&E3)

    This would return the average of values in B2:B22 if the date in A2:A22 is...
  25. Replies
    5
    Views
    196

    Re: how to reference the same row

    If you know the name of the button you'll get the row from something like
    ActiveSheet.Shapes("Button1").TopLeftCell.Row

    If there's more than one button you'll need to adjust the macro to...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4