Search:

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

Page 1 of 6 1 2 3 4

Search: Search took 0.04 seconds.

  1. Replies
    7
    Views
    211

    Re: Need help with CONCAT function

    Try this


    =SUBSTITUTE(B53,"Dale",IF(personal_info!C9=0,"Name",personal_info!C9))
  2. Re: Checking a Directory for a new file name that contains specific text

    Try this



    Public Sub test()
    MsgBox CheckFileExists("C:\Users\Bob\Documents\Projects\_8 Community\Forums\VBAExpress", "65382")
    End Sub
    Public Function CheckFileExists(ByVal Path As...
  3. Re: Formula to pull up column/row head based on cell in data set

    Try

    =INDEX(B1:E1,MIN(IF(B2:E5=10,COLUMN(B1:E1)-MIN(COLUMN(B1:E1))+1,"")))

    =INDEX(A2:A5,MIN(IF(B2:E5=10,ROW(A2:A5)-MIN(ROW(A2:A5))+1,"")))

    both array formulas
  4. Re: USER FORM : ListBox population based on another ListBox value selection [VBA]

    This should do it


    Dim rng As Range
    With ActiveSheet

    Select Case Me.ListBox1.Value

    Case "Rated Current_Order set 1": Set rng = .Range(.Range("G3"),...
  5. Replies
    5
    Views
    205

    Re: adding missing dates

    You could do it with Power Query, but here is a VBA solution


    Sub AddDates()
    Dim firstdate As Date, lastdate As Date
    Dim numrows As Long, numdates As Long, numinsert As Long
    Dim lastrow As...
  6. Replies
    3
    Views
    812

    Re: Is Power Query what I need?

    In the example that you give, it would be simple to write some VBA that adds a helper column with a formula that tests whether that exception criteria is met or not, and then delete those rows that...
  7. Re: Sequential Numbering Positive/Negative Amounts

    Yes


    =IF(A2>0,"POS"&TEXT(COUNTIF($B$1:$B1,"POS*")+1,"000"),"NEG"&TEXT(COUNTIF($B$1:$B1,"NEG*")+1,"000"))
  8. Re: Query Load to -Option of Pivot table report not coming

    You want to add it to the datamodel as well to get that option in the pivot.
  9. Replies
    5
    Views
    440

    Re: Parameter in ODBC PowerQuery

    The query might be slower, I guess it depends upon how much data you have. I am not sure if query folding would apply, my guess is that with an ODBC query it does not, so maybe doing it in the query...
  10. Replies
    5
    Views
    440

    Re: Parameter in ODBC PowerQuery

    You could pull in all of the data from the data from the ODBC query, omit the WHERE clause. Filter he columns for some value, just to generate the M code. Then set variables to the values in the...
  11. Replies
    1
    Views
    419

    Re: Excel power query

    In the code generated when you load and transform the files, you should see a step called 'Removed Other Columns' or similar. That should have a Table.SelectColumns function, just add ', "Folder...
  12. Re: Get & Transform with a parameter from Access query

    Pull back all of the data from Balances and then use Power Query to filter to your required CorpId.
  13. Replies
    2
    Views
    780

    Re: Converting IF statement to Power BI

    same as Excel really.

    if [Field 1] <> [Field2] then if [Field 1] > 0 then [Field 1] else [Field2] else if [Field2] = 0 then "N/A" else [Field2]
  14. Thread: new name

    by theBardd
    Replies
    4
    Views
    131

    Re: new name

    ActiveSheet.ExportAsFixedFormat _


    Type:=xlTypePDF, _


    filename:=Activesheet.Range("C2").Value, & ".pdf" _


    OpenAfterPublish:=False, _
  15. Thread: EOM formula

    by theBardd
    Replies
    10
    Views
    256

    Re: EOM formula

    I would recommend that you decide what should happen if there something other than nnEOM or nnFI in J4, and we build the formula to act accordingly.
  16. Thread: new name

    by theBardd
    Replies
    4
    Views
    131

    Re: new name

    Is C2 on that sheet?



    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    filename:=Activesheet.Range("C2").Value, _
    OpenAfterPublish:=False, _
    IncludeDocProperties:=True,...
  17. Thread: VBA Request

    by theBardd
    Replies
    1
    Views
    106

    Re: VBA Request

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo wsc_exit

    Application.EnableEvents = False

    If Not Intersect(Target, Me.Range("B:B, D:D, F:F")) Is Nothing...
  18. Thread: EOM formula

    by theBardd
    Replies
    10
    Views
    256

    Re: EOM formula

    I guess it would be


    =IF(RIGHT(J4,2)="FI",C4+SUBSTITUTE(J4,"FI",""),IF(RIGHT(J4,3)="EOM",EOMONTH(C4,0)+SUBSTITUTE(J4,"EOM","")))
  19. Replies
    2
    Views
    111

    Re: IFS statement with Data Validation

    Data Validation doesn't auto-populate, it just checks that the input is valid, or provides a list to select from.
  20. Thread: EOM formula

    by theBardd
    Replies
    10
    Views
    256

    Re: EOM formula

    How about this?


    =IF(RIGHT(J4,3)="EOM",EOMONTH(C4,0)+SUBSTITUTE(J4,"EOM",""))
  21. Thread: Vba loop

    by theBardd
    Replies
    29
    Views
    349

    Re: Vba loop

    Do you know how to build a loop?


    Do you know how to terminate the loop?

    Do you know how to use the inputbox?

    Do you know how to use variables?

    Do you know how to increment the variable?
  22. Replies
    6
    Views
    176

    Re: Dynamic Average Selling Price Pivot

    I am failing to see why a pivot doesn't work for this.
  23. Replies
    5
    Views
    215

    Re: Using SUMPRODUCT to SUM a Defined Range

    Are the names similar size?

    Can you post a sample of the data and tell us what result you want to make it clearer?
  24. Re: SQL Query on PowerPivot Data Model through VBA?

    As far as I am aware, whilst you can do a lot of stuff with the connections in VBA, the actual model is not exposed to VBA (and remember, it is an OLAP cube!).

    You could build your dashboard using...
  25. Thread: counting

    by theBardd
    Replies
    13
    Views
    388

    Re: counting

    Sub countmontecarlo()
    Dim cell As Range
    Dim lastrow As Long
    Dim n As Long
    Dim i As Long
    With ActiveSheet

    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

    ...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4