Ask Experts Questions for FREE Help !
Ask

Search:

Type: Posts; User: JBeaucaire

Search: Search took 0.15 seconds.

  1. Question: If/Else

    by JBeaucaire
    Answers
    4
    Views
    588

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

    Another fun one:

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

    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
    371

    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
    435

    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
    435

    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
    462

    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
    462

    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
    399

    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
    281

    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
    480

    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
    573

    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
    573

    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
    573

    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
    573

    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
    989

    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
    989

    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
    989

    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
    989

    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
    989

    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
    786

    Sure, whatever you need.

    Sure, whatever you need.
  23. Answers
    7
    Views
    799

    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
    989

    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
    786

    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
    1,176

    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
    1,176

    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
    1,396

    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
    720

    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
    1,396

    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
    1,396

    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
    1,286

    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
    1,286

    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
    3,378

    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,052

    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,252

    Like so, see attached.

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

    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
    516

    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,184

    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
    587

    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
    591

    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
    591

    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
    890

    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