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

  • Problem in vba vlookup
    Dear All Master, The problem I mean is as follows : 1. I want to modify the vba code because it takes too long/very slow to vlookup in VBA code...
  • VBA Vlookup
    Please for help. Why it`s not works? [CODE=vba]Sub Check_equpment1_click() Worksheets("Production_program").Activate Dim ans1 As Integer ans1 =...
  • Nesting INDEX MATCH into SUMIFS
    I currently have several formulas pulling in a SUMIFS from a data sheet, beginning with =SUMIFS('datasheet'!$N:$N, 'datasheet..... but as I am...
  • Extracting number from a range randomly
    Hello Expert, please kindly advise what is the best method to extract numbers from a range randomly I have a series of numbers in the range of A1...
  • Problem with If Formula
    I am using the following formula in cell A5. I would like if N5 is blank then A5 also return blank. If N5 is 0 or is greater than zero then equals...
  • Is there a way to make the day's name show up automatically in a header?
    I have a form I print out every day..and in the top right custom header I have the &[DATE], which automatically gives the current date in top...
Top