argument

  1. D

    Argument not optional problem If Not Intersect Private Sub Worksheet Change

    I have a woworksheet which gathers data from an external source and is continually updating - similar to stock prices. I am trying to copy a range of data in i8:i40. Have tried numerous ways but without success. Reading various forums the below seems most logical to meet my needs but i'm...
  2. G

    If+edate

    Hi all, I have a large spreadsheet pulling results from multiple tabs into one pivot table to track the status of various things. Within this, there are several different rules for several different manufacturers. I have a current formula in cell N2 which is...
  3. spencer_time

    turn sub into function with arguments

    Hello all, I have some code that works in a sub, but I need to use it several times over the course of my spreadsheets timeline. It currently only works on the active sheet, which I also need to change to be the sheet/variable that is called for in the argument when calling the function. This...
  4. Jaafar Tribak

    Get Cell Reference From Cell Address ?

    Hi dear forum members, Is there a formula that would take a cell address and return the corresponding cell reference so I can then use this reference as an argument in other fonctions such as INDEX etc... ? Looking for a Non-UDF solution. Regards.
  5. 3

    ByRef argument type mismatch

    Hi, I have the following code. However this line of code throw an error message. col_Arr = return_column(rng_column_loop, previous_Month) Error message: Compile ErrorByRef argument type mismatch The function requires range as argument. I passed in range. So why was the mismatch? thank you...
  6. muhammad susanto

    Combine 2 Argument into Single (for Lookup)

    hi expert.. i need your help, how to combine this 2 argument in single argument : INDEX($L$2:$L$31;MATCH(A17;$K$2:$K$31;0)+IF(master!F2>0;0;IF(master!F2=0;2;1))) i want this below formula can be inserted in above formula =VLOOKUP(I2;master!$A$2:$O$5;6;FALSE) i have trying to combine it but...
  7. J

    Add Hyperlinks

    Where ws is a worksheet and LR is the last row, I am trying to add a hyperlink to another location (asheet.cel.Address). The text to display is an array of data that I am trying to find. When I run this, however, I get invalid call or argument error. Any ideas? With ws...
  8. M

    Help with SUMIFS

    Hi, I have a SUMIFS formula which is working well with the exception of when the sumifs argument is false it returns 0.00 and I would like it to return a blank cell. I am thinking an additional AND formula after the initial IF argument may be the way to go but I cant get the formula to work...
  9. S

    4 argument if statement?

    hello, looking to construct a formula for a four argument statement: if A1 is > 10, then return .30 if a1 is >=8, but <=10, then return a value that takes 100 / b2, and lastly, if a1 is <= 8, then return .40 any thoughts please?
  10. N

    Argument Not Optional Syntax Error for ="=SUMIF(___

    Getting Argument Not Optional Error Marked in Red Dim ws As Worksheet Set ws = Worksheets("Sheet1") ws.Activate Mnth = Me.ComboBox1.Column(1, Me.ComboBox1.ListIndex) For i = 1 To Day(DateSerial(Year(Date), Mnth + 1, 1) - 1) ws.Cells(i, 1).Value =...
  11. R

    YTD calculation

    Please see the file attached I want to input a formula in cell C5 which will sum data from C2 through to G2. If Mar is selected in C4 then cells C2 to E2 should be added https://drive.google.com/open?id=1g38lnSEv13xtBiWygeUWddjIRLmTPzLP The second argument of the sum function should be...
  12. L

    workbook.printpreview function

    Hi I wanted to know the synatx of PrinPreview function which is part of Workbook object. So I went to object viewer and found out that excel define this function as Sub PrintPreview([EnableChanges]) I have 2 questions please 1) Why sub? the icon infront of the function is green box which...
  13. Z

    Formula Suggestion

    How do I refine this formula below. =IF(ISBLANK(A2),"",IF(ISBLANK(C2),"",IF(A2<>"",NETWORKDAYS(A2,C2),IF(C2<>"",NETWORKDAYS(A2,C2))))) Is it possible to combine the first and second if arguments and the 3rd and 4th if argument?
  14. C

    Adding an Argument to an existing IF statement HELP

    This formula,=IF(COUNTIF(Arrays!$K$24:$K$27,F4),O$1,""), works great and I arrived at it from much help on this board. The formula looks at the array in the Arrays tab (Arrays!$K$24:$K$27) and if F4 is found there it returns what is in O1 (in this case its apples). Now I want to add another...
  15. M

    Adding One Additional Argument to SUMIF

    The formula below works correctly =SUMIF(Consolidated!D:D,Table!A2,Consolidated!P:P) Basically it is saying that if the value in cell A2 of the "Table" worksheet is found in column D of the "Consolidated" worksheet, then sum the values in column P of the "Consolidated" worksheet. I would like...
  16. A

    Sumifs+sumifs+sumifs

    I'm trying to avoid stringing together multiple SUMIFS to find the total value of a cell, where two lots of criteria are matched. I have done some research and keep getting sent off an jaunty anglestowards the land of SUMPRODUCT and INDEX/MATCH - Which i've tried but it seems like I'm going...
  17. Y

    Argument not optional - clear hyperlink

    Hi I encountered the Argument not optional error while trying to run the code below. Appreciate if anyone can point out my mistake. Thanks a lot guys. Sub Clear_Hyperlink() Dim Rng As Range, Cell As Range Set Rng = ActiveSheet.Range("a1:a7") For Each Cell In Rng If...
  18. P

    SumIf not working

    Hi, I have the following SumIf formula that's not working as I expect it to: =SUMIF(QRYTasksRcvd!$A$2:$A$1779,'Daily Tracker'!D7,QRYTasksRcvd!$B$2:$AE$1779) The first argument is referring to the first column of a table of data where column A is the date. The second argument is the date I'm...
  19. T

    Pass more than one argument

    If I want to pass ONE argument to a SubRoutine, I can do this: Dim MyArray() as Variant MyArray() = Array("Apples", "Oranges") Dim i As Integer For i = LBound(MyArray(), 1) To UBound(MyArray(), 1) Call SomeSub(Arg:=i) Next i What if SomeSub requires TWO arguments? How can I...
  20. N

    About datedif() function

    I was asked for an Excel solution that will refer the difference between hire date and today in the format: "9 years, 5 months and 2 days". I came up with the following implementation: =CONCATENATE(DATEDIF(B3,B4,"y")," years, ", DATEDIF(B3,B4, "ym"), " months and ", DATEDIF(B3,B4, "md"), "...

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top