Search:

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

Page 1 of 6 1 2 3 4

Search: Search took 0.29 seconds.

  1. Replies
    1
    Views
    77

    Re: Wildcard for MSQuery not Working

    hello, Trent

    Maybe the wrong wildcard for the database (being searched). Such as if MS Access, use % instead of *

    If that doesn't help, please give some more information. Like the SQL, and some...
  2. Re: How to detect missing numbers within a list?

    Hello. Not that I can think of.

    If you define precisely what is required, it can be done by code.
  3. Re: Which Range is currently being Cut or Copied ?

    You're a Champion, Jaafar
  4. Replies
    3
    Views
    277

    Re: EXCE Named Range Corruption

    I understood you have dynamic named ranges, Martel. The way to use VBA to create the ranges is to use a programmatic approach instead of the current formulas. Whatever the dynamic formula is doing,...
  5. Re: Which Range is currently being Cut or Copied ?

    I can imagine this being flaky, but.. any chance of looping through the copied information & matching up the contents against the source cells?
    I think that even for you very simple example A1:A2,...
  6. Re: Power Pivot or Power Query to show all claim lines for a customer with a specific event

    maybe this helps. This is one way to do it in a normal pivot table, or query table.



    SELECT A.[Date], A.Customer, A.Event, A.[Amt Paid]
    FROM [Data$] A
    WHERE A.[Date] IN
    (SELECT DISTINCT...
  7. Replies
    3
    Views
    277

    Re: EXCE Named Range Corruption

    Consider having VBA create/update the named ranges, as required. Then they can be simply defined (as hard ranges) without using OFFSET, MATCH, INDEX, COUNTA, etc, etc
  8. Replies
    3
    Views
    105

    Re: Filter Data from Drop Down List

    Another approach is a parameterised query. Uses no formulas & no code.
    Please google for examples. Like http://dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/
  9. Re: Which Range is currently being Cut or Copied ?

    greetings, Jaafar
    What about pasting a copy to a temporary worksheet?
    Interrogate it to check for contiguous or not ranges. Delete worksheet when done.
    regards, Fazza
  10. Re: SUMPRODUCT formula making spreadsheet run REALLY SLOW!

    For sure you're correct that those formulas are the culprit, Jenny.
    There are likely many alternative approaches (that are fast).
    To give specific advice would require understanding of the details.
  11. Replies
    6
    Views
    160

    Re: Match Function Problem

    An alternative to (VLOOKUP) formulas are queries. These work with header names (or could be 'select all', which is independent of header names). So inserting extra columns is not an issue. Changing...
  12. Re: Pivot count to exclude certain entry in grand total

    Looks achievable - maybe by a few approaches. Specific solution depends on your set up & which way you'd want to go. Best to provide details of set up.
  13. Re: how to Changes in the calculation and display of the Grand Total in the pivot table?

    to me, it is not clear what is required

    a simple & clear explanation with some sample data - that can be copied into Excel - will increase your chances of getting a solution
  14. Re: Best way to show data that can be drilled down?

    may be of interest - https://jkp-ads.com/Articles/treeview.asp
  15. Re: Combine data in columns (A-Current portfolio, B-buys, C-sells, Result-resulting portfolio)

    Being unfamiliar with pivot tables, MS Access might be a good tool.

    Though VBA within Excel might be even better - cause you don't need two different softwares. It probably is simple enough in VBA...
  16. Re: Combine data in columns (A-Current portfolio, B-buys, C-sells, Result-resulting portfolio)

    hi, Tim

    Maybe use a pivot table. It can combine data from multiple tables and not show data that nets to zero quantity.
    Or very similar using a query table. Neither needs code but you could have...
  17. Replies
    4
    Views
    154

    Re: Speed it up if you can

    tweaking that a bit, to remove looping cell-by-cell


    Sub addresscapital()

    Dim rngCells As Excel.Range

    Application.ScreenUpdating = False
    Application.Calculation =...
  18. Re: VBA Excel SQL Error "No Value Given for One or More Required Parameters"

    Maximum. That is done like this,


    SELECT MAX(some field), other fields you want
    FROM table
    GROUP BY other fields you want

    Order of fields in SQL doesn't matter, btw.

    If you want to filter,
  19. Replies
    1
    Views
    743

    Re: Best PC for calculating huge data?

    Hello

    I assume these are Excel files, and suggest the biggest difference in calculation time will be the way the calculations are done. The set up of the Excel file.

    There are old threads here...
  20. Re: Value not equaling what it should -- can't figure out why

    please see the highlighted note in https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/round-function

    in case that link has an error, I'll copy it here,


    Please...
  21. Re: VBA Excel SQL Error "No Value Given for One or More Required Parameters"

    Hi, Sam

    IIRC, that error is when a field name in the query is not found in the data.

    Suggest you carefully check field headers "First Last", "Age", and, "Salary" match between the worksheets &...
  22. Re: Maintain partial bolding of original cell when doing an INDEX/MATCH lookup

    You could use VBA instead. Issue might be speed if you have many.
    Being code, specifics would really help for anyone to assist further.
  23. Re: Value not equaling what it should -- can't figure out why

    Double gets you lots of digits in numbers. https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/data-types/double-data-type
    The sort of subtraction being done often has small...
  24. Re: Moving Axis Cross Points automatically within Excel 2010

    the macro recorder is great for this sort of thing. ALT-T-M-R to start it.

    ActiveChart.Axes(xlCategory).CrossesAt = Range("YourCell").Value2

    Please set activechart or change first reference....
  25. Re: VBA SQL Query w/WHERE Clause Condition = GetUserName() From MS ACCESS DB

    actually, it was a different thread I was thinking of. it includes some timing of different approaches

    https://www.mrexcel.com/forum/excel-questions/332472-excel-acess-via-dao.html
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4