Ask Experts Questions for FREE Help !
Ask

Search:

Type: Posts; User: JBeaucaire

Search: Search took 0.05 seconds.

  1. Question: If/Else

    by JBeaucaire
    Answers
    4
    Views
    1,715

    Another fun one: =L10 / CHOOSE(K10, 120, 208,...

    Another fun one:

    =L10 / CHOOSE(K10, 120, 208, 360)
  2. Answers
    4
    Views
    1,547

    SUBSCRIPT OUT OF RANGE errors typically refer to...

    SUBSCRIPT OUT OF RANGE errors typically refer to something being named incorrectly. My example listed the hyperlink being in cell B7 of a sheet called "Sheet1". You would naturally need to edit...
  3. Answers
    4
    Views
    1,547

    Perhaps you'll have more luck with this method: ...

    Perhaps you'll have more luck with this method:



    ThisWorkbook.FollowHyperlink (ThisWorkbook.Sheets("Sheet1").Range("B7").Value)

    This presumes the value in B7 is actually a text string...
  4. Answers
    4
    Views
    1,572

    What a horrid sheet, this utility is design to...

    What a horrid sheet, this utility is design to make MASSIVE changes to your environment when you open the workbook, but there is no code in that workbook to undo those changes when you close the...
  5. Answers
    4
    Views
    1,572

    If you upload the workbook here I'll take a look...

    If you upload the workbook here I'll take a look at what the file did to the environment and see about undoing it.
  6. Answers
    4
    Views
    1,567

    This is a turnkey solution that I've used for...

    This is a turnkey solution that I've used for this purpose. You would need to Google up methods for use on other sites, to me it would be a waste of time. But I found this solution with simple...
  7. Answers
    4
    Views
    1,567

    If you're talking about stock prices, the web is...

    If you're talking about stock prices, the web is full of functions that pull stock prices from web sites. Here's one I've used:


    Function Shareprice(YahooTicker As String)
    Dim strURL As String,...
  8. Answers
    4
    Views
    1,366

    Scott is right regarding Access. But if you're...

    Scott is right regarding Access. But if you're hooked on Excel, the real goal here would still be to NOT be maintaining separate sheets, that's just missing the benefit of computers, eliminating...
  9. Answers
    1
    Views
    1,303

    There's no reason VBA cannot insert values into a...

    There's no reason VBA cannot insert values into a cells, data validation or not. If you post your workbook, or a small version of it, with instructions on how I can replicate the problem you're...
  10. Answers
    1
    Views
    1,511

    You would use conditional formatting for this. ...

    You would use conditional formatting for this.

    If the date were in A1 and that date is good for 3 years, then this Conditional Formatting formula could used to create a rule to apply to that cell...
  11. Answers
    10
    Views
    2,003

    Just shot in the dark silly suggestions, maybe...

    Just shot in the dark silly suggestions, maybe edit this one line of code:


    ws.UsedRange.AutoFilter 2, Range("D3").Value
  12. Answers
    10
    Views
    2,003

    Well, that's just odd. I just opened the same...

    Well, that's just odd. I just opened the same file above, working perfectly.

    Video: 2014-07-31_0749 - JerryBeaucaire's library
  13. Answers
    10
    Views
    2,003

    When I click on the Prelude in your form, it puts...

    When I click on the Prelude in your form, it puts a P in D3 which causes all the "P" rows to be copied in from all the other sheets. How is that not what you wanted?
  14. Answers
    10
    Views
    2,003

    I've added the TITLES to every sheet to make the...

    I've added the TITLES to every sheet to make the filtering of data clean and simple. Then built a macro into the SEARCH sheet module that will watch for any change in cell D3. If you edit that...
  15. My bad, I had a helper cell in use while...

    My bad, I had a helper cell in use while constructing that formula. The final Array formula should be:

    =INDEX(A1:A4,MATCH(ROUND(MIN(MOD(A1:A4, 1)),2),ROUND(MOD(A1:A4,1),2),0))
  16. Try this array formula: ...

    Try this array formula:

    =INDEX(A1:A4,MATCH(ROUND(F1,2),ROUND(MOD(A1:A4,1),2),0))

    ... confirmed by pressing CTRL+SHIFT+ENTER, not just ENTER. You will know the array is active when you...
  17. Answers
    12
    Views
    2,364

    First you have to ADD the next sheet to the...

    First you have to ADD the next sheet to the "selected sheets". Once both sheets are selected you should get the results you want.




    'Export activesheet and next sheet to PDF
    If...
  18. Answers
    12
    Views
    2,364

    Ok, your code is hiding the error, so let's...

    Ok, your code is hiding the error, so let's reveal it. Change this code at the end of your macro:


    End With

    errorhandler1:
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set...
  19. Answers
    12
    Views
    2,364

    The problem appears to be this code: ...

    The problem appears to be this code:


    PdfFile = CreateObject("WScript.Shell").Specialfolders("Desktop") & "\" & Title & ".pdf"
    stAttachment = Specialfolders("Desktop") & "\" & Title & ".pdf"...
  20. Answers
    12
    Views
    2,364

    I try to keep my WITH/END WITH ranges short and...

    I try to keep my WITH/END WITH ranges short and easy to spot.

    I also am manic about the indentation of code to also make it inordinately easy to see where the IF and END IF parts match up.

    I...
  21. Answers
    12
    Views
    2,364

    When a single command is too long to fit on one...

    When a single command is too long to fit on one line comfortably and you put it on more than one line, you must add a space and an underline as a "linefeed" to indicate the command is continued on...
  22. Answers
    4
    Views
    2,127

    Sure, whatever you need.

    Sure, whatever you need.
  23. Answers
    7
    Views
    1,898

    Sheet references must be attached to each range...

    Sheet references must be attached to each range of cell references:


    =SUMPRODUCT(--(Frequency('01'!a15:a30,'01'!a15:a30)>0))
  24. Answers
    12
    Views
    2,364

    SaveAs PDF and Email is a pretty common activity....

    SaveAs PDF and Email is a pretty common activity. Here's the best primer on that:

    http://www.rondebruin.nl/pdf.htm
  25. Not sure you're going to get any better than...

    Not sure you're going to get any better than that. Excel's precision is a known issue.

    Numeric precision in Microsoft Excel - Wikipedia, the free encyclopedia
  26. Answers
    4
    Views
    2,127

    If you mock up your sample layout with 3-4 sample...

    If you mock up your sample layout with 3-4 sample sets of candidate results, then mockup a sheet manually showing the results you'd like to automate from that simple sample data, I may be able to...
  27. Try going to VIEW > FREEZE PANES and turn off...

    Try going to VIEW > FREEZE PANES and turn off freezing if it is on.
  28. Answers
    3
    Views
    2,280

    Assuming those text strings are in A2 going down:...

    Assuming those text strings are in A2 going down:

    B2: =DATEVALUE(MID(A2,3,2)-70 & "/" & MID(A2,5,2) & "/" & CHOOSE(MID(A2,2,1)+0,,, 2013, 2014))
    (format cell as Date)

    C2:...
  29. Answers
    3
    Views
    2,280

    Provide an example text string and the resulting...

    Provide an example text string and the resulting date that would be derived from it, provide one for EVERY month of the year and include several years as well. Show us all the variations. I can't...
  30. Answers
    5
    Views
    2,496

    Read through this thread, it offers a hefty...

    Read through this thread, it offers a hefty solution to this simple problem with no simple answer:

    ListBox Mouse Scroll
  31. Answers
    6
    Views
    1,144

    Your question demonstrates how a question itself...

    Your question demonstrates how a question itself can imply it's position is the correct one for no reason.

    You resist the idea that just because you believe something is right you don't have to...
  32. Answers
    5
    Views
    2,496

    Care to share? Others who search on their own...

    Care to share? Others who search on their own may find this thread and your code snippet may help them.
  33. Answers
    5
    Views
    2,496

    So I don't have to create a pseudo workbook to...

    So I don't have to create a pseudo workbook to test it, can you post your workbook as it is currently with any helpful notes within?

    This will be a loop through the ListBox.Items to determine...
  34. Start a new thread here on the forum with your...

    Start a new thread here on the forum with your question.

    Then click GO ADVANCED on that new thread and use the paperclip icon to attach your sample workbook so we can look at it together.
    Thanks.
  35. Answers
    5
    Views
    2,236

    That's OK, no need to fret, even forums are...

    That's OK, no need to fret, even forums are subject to occasional quirks and flukes. Thanks for coming back to let me know.
  36. Answers
    5
    Views
    2,236

    THe formula you're looking for might be this, for...

    THe formula you're looking for might be this, for example in O4, then copied down:

    =IF(C4<>"n", "", N(O3)+1)


    You can use that same formula on your new sheet, but for the first cell on that...
  37. Answers
    6
    Views
    5,110

    Is he? You will know your regional laws better...

    Is he? You will know your regional laws better than we will.

    If he is, then what? Are you able to pursue the infraction safely? Would it you be able to keep your job if you did? What are the...
  38. Answers
    3
    Views
    1,558

    Definitely use the ROUND() function in your...

    Definitely use the ROUND() function in your formula as that will create an actual decimal value that is under your control. If you use the suggestion in the first reply you'll still have long...
  39. Answers
    9
    Views
    1,956

    Like so, see attached.

    Like so, see attached.
  40. Answers
    9
    Views
    1,956

    Your question first states sheet2 A1 would have a...

    Your question first states sheet2 A1 would have a drop down comprised of B1:X1 from SHeet1, that's the categories.

    Then you state you'd like to choose a worker name in A1. You would need a second...
  41. Answers
    2
    Views
    805

    1) Select the range of cells to copy 2) Ctrl-G...

    1) Select the range of cells to copy
    2) Ctrl-G opens the Go to window
    3) Select Visible cells only
    4) Now copy and paste.

    Video example
  42. If there's ever a chance the TIMES in those two...

    If there's ever a chance the TIMES in those two cells will go past midnight, so you're trying to subtract 1:00am from 11:00pm, the formula would need to be a little longer:

    =IF(COUNT(C8,D9)=2,...
  43. If the cell will be EMPTY, then maybe this: ...

    If the cell will be EMPTY, then maybe this:

    =IF(COUNT(C8,D9)=2, TEXT(C9-D8,"h:mm"), "")
  44. Question: Spreadsheets

    by JBeaucaire
    Answers
    5
    Views
    1,453

    This is the formula I use for that: ...

    This is the formula I use for that:

    =LOOKUP(99^99,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(A$1:INDEX(A:A,LEN($A1)))))


    This will extract the first numeric string it finds...
  45. Answers
    2
    Views
    840

    Excel 2003: Show or hide gridlines on a...

    Excel 2003: Show or hide gridlines on a worksheet - Excel - Office.com

    Excel 2007: Show or hide gridlines on a worksheet - Excel - Office.com

    Excel 2010: Show or hide gridlines on a worksheet...
  46. Then the last user can provide the login...

    Then the last user can provide the login password.

    Else search on the web to find out how to do a "factory restore" on that particular model of computer, resetting it back to the day it came out...
  47. Answers
    3
    Views
    857

    This should do it: Option Explicit Sub...

    This should do it:


    Option Explicit

    Sub TransferData()
    Dim NR As Long

    With Sheets("Sheet2")
    .AutoFilterMode = False
  48. Answers
    3
    Views
    857

    1) Right-click on the EXPORT DATA button and...

    1) Right-click on the EXPORT DATA button and select ASSIGN MACRO
    2) Click on NEW
    3) Paste in this macro:


    Sub Rectangle3_Click()
    Dim NR As Long

    With Sheets("Sheet2")
    NR = .Range("A" &...
  49. Formulas cannot delete anything from other cells,...

    Formulas cannot delete anything from other cells, only display results in the cell where they are entered.
  50. Answers
    7
    Views
    1,063

    If you use the paperclip icon to attach your...

    If you use the paperclip icon to attach your workbook so I can see how your reference data is laid out, this shouldn't be too hard.
Results 1 to 50 of 500