Search:

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

Page 1 of 20 1 2 3 4

Search: Search took 0.09 seconds.

  1. Replies
    3
    Views
    59

    If the data is sorted in date order as it appears...

    If the data is sorted in date order as it appears to be in the sample, then you can use a regular formula:
    =INDEX(G4:G55,MATCH(TODAY(),D4:D55,1)+1)
  2. It would be: Set rData = Sheets("Other...

    It would be:

    Set rData = Sheets("Other tab").Range("E2:F5")
  3. Perhaps this: Private Sub...

    Perhaps this:

    Private Sub WorkSheet_Change(ByVal Target As Range)

    Dim oldval As String
    Dim newval As String
    Dim Values() As String
    ...
  4. Replies
    11
    Views
    427

    All formulas should update unless you have manual...

    All formulas should update unless you have manual calculation turned on.

    No function that requires a range (i.e. won't accept an array) will work if the source workbook is closed. That includes...
  5. Replies
    5
    Views
    177

    Well I could have gone for 32768 but I think...

    Well I could have gone for 32768 but I think 1E+100 looks much more scientific... ;)
  6. Replies
    11
    Views
    427

    Just as an FYI, you would make the workbook a lot...

    Just as an FYI, you would make the workbook a lot easier to maintain if the Line names in column C of the Sample sheet matched those on the Notes Data sheet.
  7. Replies
    5
    Views
    177

    Change column H so it only contains the keywords,...

    Change column H so it only contains the keywords, then use:
    =LOOKUP(1E+100,SEARCH($H$14:$H$17,E2),$I$14:$I$17)

    See attached file.

    @Maud: there's no need to make that UDF volatile.
  8. Replies
    1
    Views
    88

    You can sort each field by the data field which I...

    You can sort each field by the data field which I think accomplishes what you want.
  9. Replies
    2
    Views
    89

    It would be: =AVERAGEIF(E2:OFFSET(E11,-1,0),">0")

    It would be:
    =AVERAGEIF(E2:OFFSET(E11,-1,0),">0")
  10. Comment out this line: On Error GoTo Fehler...

    Comment out this line:

    On Error GoTo Fehler
    and then run the code again which should allow you to debug. It may be simply that your default workbook doesn't have 3 sheets (the code just assumes...
  11. Replies
    7
    Views
    403

    Not directly but you can fake it with some...

    Not directly but you can fake it with some additional tables and sparklines and the camera tool. See attached.
  12. Replies
    2
    Views
    97

    Try this on a copy of your real workbook: ...

    Try this on a copy of your real workbook:


    Sub foo()
    Dim rDelete As Range
    Dim lCol As Long
    Dim lCalc As Long

    For lCol =...
  13. Replies
    11
    Views
    427

    =MATCH(reference A,D2:AH2,0) will return the...

    =MATCH(reference A,D2:AH2,0)
    will return the relative position of reference A in the range D2:AH2. You can then use that in conjunction with INDEX to get the value you need:...
  14. Replies
    3
    Views
    95

    Hi Fred, You can alter the formula to: ...

    Hi Fred,

    You can alter the formula to:

    =IF(SUM(1*ISNA(MATCH(B3:G3,tbl_valid_grades,0)))>0,">= 1 bad", SUMPRODUCT(N(OFFSET(tbl_ltr_to_nbr,MATCH(B3:G3,tbl_valid_grades,0)-1,1,1,1)),B$2:G$2))
    and...
  15. Replies
    5
    Views
    238

    Is there any code in the macro workbook that...

    Is there any code in the macro workbook that creates or manipulates toolbar buttons?
  16. Replies
    3
    Views
    427

    FYI, you can run it alongside previous versions...

    FYI, you can run it alongside previous versions of Office, but not previous versions installed via CTR.
  17. Replies
    4
    Views
    155

    [SOLVED] SUMIF/SUMIFS (and COUNTIF/COUNTIFS) doesn't work...

    SUMIF/SUMIFS (and COUNTIF/COUNTIFS) doesn't work if the source workbook is closed (it never has). You need to use SUMPRODUCT instead.
  18. Are you running exactly the same queries on the...

    Are you running exactly the same queries on the same data? CopyFromRecordset can be funny about Null values and errors.
  19. You're missing a sheet name in there. (it's...

    You're missing a sheet name in there. (it's easier if you simply select the ranges with the mouse)
  20. Better yet, don't use VLOOKUP at all: ...

    Better yet, don't use VLOOKUP at all:

    =IF(A8<>"",INDEX('f:\DL\[AAA.xlsx]AAA'!$G$5:$G$400,MATCH(A8,'f:\DL\[AAA.xlsx]AAA'!$D$5:$D$400,0)),"")
  21. There shouldn't be a problem in 2010 using...

    There shouldn't be a problem in 2010 using VLOOKUP to a closed xlsx file. You say the two files have the same format, which begs the question why you changed the sheet name in the second formula.
  22. What does "doesn't work" mean exactly? What...

    What does "doesn't work" mean exactly? What happens?

    I note you have changed the sheet name in the formula from Sheet1 to AAA - was that intentional?
  23. Change this line: zLastCol = Cells(2,...

    Change this line:

    zLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
    to this:

    zLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
  24. There is nothing wrong with xlsx files and you...

    There is nothing wrong with xlsx files and you can most certainly use VLOOKUP with them. Can you give a specific example of what doesn't work?
  25. You should just have to change this: For i = 5...

    You should just have to change this:

    For i = 5 To zLastCol Step 5
    to this:

    For i = 32 To zLastCol Step 5
Results 1 to 25 of 500
Page 1 of 20 1 2 3 4