Search:

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

Page 1 of 6 1 2 3 4

Search: Search took 0.04 seconds.

  1. Re: Problems converting large vba project to add in

    I am not sure I fully understand the problem, but don't forget you can always reference ActiveWorkbook and Activesheet, which will be (should be) the objects your code is running in. You can also...
  2. Re: How to dynamically change connection string (different windows/office) ?

    I think you mean the Workbook_Open event.
  3. Replies
    8
    Views
    192

    Re: Worksheet Formula as VBA Code

    In what way did they not work?
  4. Replies
    8
    Views
    192

    Re: Worksheet Formula as VBA Code

    Try this


    With Range("AL5:AL" & Cells(Rows.Count, "AL").End(xlUp).Row)
    .Formula = "=IFERROR(INDEX('Wharf Schedules'!C2,MATCH('Data'!RC41&RC43,'Wharf Schedules'!C:C&'Wharf...
  5. Re: Problems converting large vba project to add in

    Of course you can package all of that, you can package anything in an addin. The stuff you don't want visible you can make as Private, as Functions, or just add Option Private Module at the start of...
  6. Replies
    4
    Views
    168

    Re: Basic Array/Sorting Question

    Why not just a simple COUNTIF

    =COUNTIF(F$24:F$43,F44)
  7. Re: Problems converting large vba project to add in

    I would create an addin and add a custom ribbon, or a custom group on an existing ribbon, to provide access to my functions. That is typically the way I do it, and I can write big addins as well.
  8. Re: Problems converting large vba project to add in

    Depends what exactly you mean by functions. Do you mean custom formulas, UDFs, or do you mean methods that will work on a user's particular data?
  9. Replies
    2
    Views
    116

    Re: Difference between weeks

    Seems to simple, but do you mean this?

    ...
  10. Replies
    5
    Views
    217

    Re: SUMPRODUCT question

    Try this


    =SUMPRODUCT(B3:I6*(B1:I1="May YTD")*(A3:A6="Store A"))
  11. Re: Variable not defined error/ Command Button

    Should it also be up not down?


    iRow = Sheets("Data").Cell(Sheets("Data").Rows.Count, "A").End(xlUp).Row + 1
  12. Replies
    33
    Views
    9,300

    Re: Ranking with SUMPRODUCT to ignore blanks

    Works fine on my machine.
  13. Replies
    33
    Views
    9,300

    Re: Ranking with SUMPRODUCT to ignore blanks

    This works I believe


    =IF(AND(E2<>"",F2<>"",G2<>""), SUMPRODUCT(--(E2=$E$2:$E$6),--(G2<$G$2:$G$6))+1,"")
  14. Replies
    1
    Views
    111

    Re: Create Sheet to index scores for a club

    This should do it


    Public Sub Reformat()
    Dim ws As Worksheet
    Dim lastrow As Long
    Dim nextrow As Long
    Dim targetrow As Long
    Dim i As Long
    Application.ScreenUpdating = False
  15. Re: Return true if all values are found in an array

    Public Function MatchAll(ByRef Target As Range, ByRef Values As Range) As Boolean
    Dim cell As Range

    MatchAll = True
    For Each cell In Values.Cells

    If...
  16. Replies
    33
    Views
    9,300

    Re: Ranking with SUMPRODUCT to ignore blanks

    Post an example of your data and required results.
  17. Replies
    33
    Views
    9,300

    Re: Ranking with SUMPRODUCT to ignore blanks

    Try OR instead of AND
  18. Re: Return true if all values are found in an array

    In VBA, do you want it as part of a bigger routine, or do you want a UDF that returns true or false to the worksheet.
  19. Re: Power Query (noob-ish) struggling with changing Field names.

    I can't see Column1 anywhere in that code. Which step errors?
  20. Re: Return true if all values are found in an array

    Assuming your array is in A1:A6, and the values in B1:B3, you could use this array formula


    =COUNT(MATCH(B1:B3,A1:A6,0))=COUNT(B1:B3)
  21. Re: Power Query (noob-ish) struggling with changing Field names.

    Try this


    let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Documents\Bookings\Contract Bookings\20190718.xlsm"), null, true),
    #"1" = Source{[Name="20190718"]}[Data],
    Cols =...
  22. Replies
    2
    Views
    146

    Re: VBA Saving excel with some constrains

    Try this


    Dim FilePath As String
    With Application.FileDialog(msoFileDialogSaveAs)

    .InitialFileName = Range("A1").Value
    If .Show Then

    ...
  23. Re: Pivot table not recognising date. Power pivot, normal data...

    Can you post the workbook somewhere for us to look at (obfuscate the data if sensitive)?
  24. Re: Can't get VBA to create a Calculated Field in Pivot Table

    Slight tweak to include field name works for me


    With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("VAT Value")
    .Orientation = xlDataField
    .Position = 2
    .Function = xlSum...
  25. Re: Transfering Customized Ribbion to new machine.

    You can export the ribbon setup on one machine, File>Options>Customize Ribbon>Import/Export, and then import on the other.
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4