Posts by akbarnikain


    Attached is workbook "Display-All-Matches-from-Search-in-Userform-Wksht"

    I have a userform ("frmMain") and when I enter data on the userform to write to the soon as it comes to the code:

    1. With ws
    2. .Range("a" & c).Value = Me.tbFName.Value
    3. .Range("b" & c).Value = Me.tbLName.Value
    4. .Range("c" & c).Value = Me.tbLocation.Value
    5. .Range("d" & c).Value = Me.tbDept.Value
    6. End With

    Excel program exits.

    Hope someone has an answer.


    As per the attached workbook ("HighLightMax") I would like to select the cell address for each max number in the columns of the selected range of cells.
    Currently it displays the message for the max value of the cell in each column of the selected range but I am unable to select its cell address.

    Hope someone can help.


    Attached is the Workbook ("Arrays")
    I have a two fold problem
    1) When I try to run the macro "PopulatingArrayVariable" nothing happens
    2) How can I include 2 columns from "Table5" columns "A" & "B" (1 & 2) for the code:
    TmpArray = myTable.DataBodyRange.Columns(1)



    • Arrays.xlsm

      (18.84 kB, downloaded 76 times, last: )

    As per the attached Workbook "VB MultipleCriteria-2" I have an array formula in cell "F8"; viz.

    Whenever, there is a change in cells "F1:F5" my vba code displays the result in cells "F6" & "F10" however, cell "F8" does not; unless i use "F9" to refresh it.

    What is wrong in my formula in cell "F8"...hope someone has an answer.

    As per the attached Workbook "VB MultipleCriteria-Ozgrid" I am trying to use the following formula:

    [f10].FormulaArray = "=SUM(SUMIFS(Qty,product,"" = "" & [f1], supplier,"" = ""&[f2:f4],delDate,"" >= ""&[f6])"

    instead of

    [f10].FormulaArray = "=SUM(SUMIFS(Qty,product,""=""&R[-9]C, supplier,""="" & R[-8]C:R[-6]C,delDate,"">="" & R[-5]C))"

    However, I get an error message
    Run-time error '1004':
    Unable to set the FormulaArray property of the Range class

    Also, I would like the "combo boxes" ListFillRange automatically updated when columns A,B & D is changed...the change should be reflected in Columns H,I & J alphabetically sorted (vba code for this is in "Module 1")

    As per the attached Workbook "VB MultipleCriteria-Ozgrid" I am using the RC formula in the vba code for cell "F10" on my "sheet2":
    [F10].FormulaArray = "=SUM(SUMIFS(Qty,product,""=""&R[-9]C, supplier,""="" & R[-8]C:R[-6]C,delDate,"">="" & R[-5]C))"

    However, once the code has run I cannot then click anywhere on my there a solution for this?


    As per the attached Workbook "VB MultipleCriteria-Ozgrid"
    When I use the excel formula in Cell "F8" =SUM(SUMIFS(Qty,product,"="&F1, supplier,"=" & F2:F4,delDate,">=" & F5)) I get correct result.
    How can I do the same in Cell "F6" using "Application.WorksheetFunction.SumIfs.... based on multiple CRITERIA in COLUMN "F" in VBA


    I have a command button ("Unique/Srt") on each of my worksheets ("vendor" & "Chart") that extracts unique items and sorts them alphabetically and then it displays the unique and sorted items in a "msgbox" using the code... "MsgBox Join(Application.WorksheetFunction.Transpose(Range("newchart").Value), Chr$(10))" however, I do not know how to get the "msgbox" to scroll & continue to display all the items to the end.
    Also, I would like to by pass the command button and have the "Column G" on the worksheets get automatically and dynamically refreshed and updated at all times.

    Hope someone can help

    *Attached is the workbook.


    I have 2 Defined Names ("Animal" & "Names") reflecting cells in columns "B" and "F". If I have empty cells at the end and I add new data after the empty cells the Defined Names ranges does not reflect the data after the blank cells.

    Is there a way to refresh and update dynamically the Defined Names in excel VBA

    Attached is the worksheet.


    I have 2 list (Names & Animals) and it extracts unique items and then sorts them alphabetically in the adjacent columns. I also have in column "A" data validation for the 2 list Cell ("A13") represents "Names" and Cell ("A16") represents "Animal"

    When I enter data in Cell ("A13" or " A16") Data Validation checks if the data does not exist it will prompt if I want to ADD it to my respective list.

    The problem I have is if the Data Validation is in Cell "D3" and "H3" instead of "A13" and "A16"; for the "Names" and "Animal" respectively, it does not work.

    Attached is the worksheet

    Hope some one can help

    [INDENT]I have a userform for data entry and a listbox that displays the current records.
    However, I would like the listbox to display the records by date in descending order.

    Appreciate if someone can help.

    Attached is the sample workbook

    PS: I have posted this previously but unfortunately received no reply yet...hope this time someone will respond.