Search:

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

Page 1 of 6 1 2 3 4

Search: Search took 0.04 seconds.

  1. Replies
    10
    Views
    223

    Re: Complicated Nested If Statement

    I used a few helper cells. But it boils down to Eric W's solution, only I don't use VLOOKUP but INDIRECT(ADDRESS(1,column))
  2. Replies
    10
    Views
    223

    Re: Complicated Nested If Statement

    Hello,
    instead of writing a 4 levels nested if statement consider the following approach:

    1. for every entered option set a number to 0 ( 1st option ) or 1 ( 2nd option ) say n1, n2, n3 and n4...
  3. Re: Speed Up Current Working Script - Transferring Data From One Sheet To Another

    Can you tell me the hardware (processor, memory) your workbook is running?
    My machine runs an Intel core/i7 processor @2.4GHz with 8Gb memory.
    I tested with 1260 rows on input sheet, 11 columns...
  4. Re: Speed Up Current Working Script - Transferring Data From One Sheet To Another

    14 minutes! That is outrageous. Could you upload your workbook to some file sharing service (dropbox, onedrive) and publish the link in a reply. I will investigate why it takes so long (most likely...
  5. Re: Speed Up Current Working Script - Transferring Data From One Sheet To Another

    Two things help
    1. disable screen updates in the sub


    sub ...
    Application.ScreenUpdating = False
    ...
    ...
    Application.ScreenUpdating = True
    end sub
  6. Replies
    6
    Views
    309

    Re: Loop through files in directory

    I corrected the error. Try this version.


    Option Explicit

    Sub MasterKey()
    Dim MyFolder As String
    Dim wbFile As File

    Dim wb As Workbook
  7. Replies
    0
    Views
    282

    understanding BB codes

    Hello fellow developers.
    I would like you to give feedback on this macro I coded.


    educational value: the code contains comment lines to explain various coding techniques. Did it help to...
  8. Re: hiding rows based on cell content (multiple options)

    Well if you change the value of cell C4, the target.address value will be "$C$4" therefore the lines after 'case "$C$4" ' are processed



    arCases = Array("X")
    Set shRows...
  9. Re: hiding rows based on cell content (multiple options)

    Sorry, fotget the previous reply it is wrong. This one does work


    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim arCases As Variant
    Dim res As Variant...
  10. Re: hiding rows based on cell content (multiple options)

    Give this a try


    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim arCases As Variant
    Dim res As Variant
    Dim shRows As String
  11. Replies
    25
    Views
    1,494

    Re: Sub-String Extraction !

    Corrected this

    You assumed right, but I changed it a bit. Now you can change this value at the very beginning of this module

    See comment added

    This intended purpose, sctolling so you can...
  12. Replies
    25
    Views
    1,494

    Re: Sub-String Extraction !

    This is the screen I tested with
    <table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:12pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt;...
  13. Replies
    25
    Views
    1,494

    Re: Sub-String Extraction !

    Sorry, forgot to say:
    In the VBA environment select the sheet where the output has to go, and change the property (name) to sh. Then sh is recognized and there is no need to set it.
  14. Re: Storing work sheets from other Workbook into One Excel work book

    Please tell us why you want all sheets in all workbooks into one sheet. What do you want to do with that sheet?
  15. Re: Storing work sheets from other Workbook into One Excel work book

    All books on your computer or books found in a certain directory?
    And if you want to collate all sheets into one, I assume all sheets are formatted the same way?
  16. Replies
    25
    Views
    1,494

    Re: Sub-String Extraction !

    If you code it like this, you don't need the CS() array anymore:
    note: You have to adapt a little bit to your situation:
    top-left of output area: init r and c to something other than 1,1 and at...
  17. Replies
    25
    Views
    1,494

    Re: Sub-String Extraction !

    [COLOR=#333333]
    Change Cs() as Long to CS() as Byte saves 1743588 bytes of memory.
  18. Replies
    25
    Views
    1,494

    Re: Sub-String Extraction !

    this is the modified code.
    In the beginning the calculation of numcomb is done with the combin function.
    The cels in the worksheet only contains combinations that have a 3 and a 5.



    Sub...
  19. List variable usage in workbooks. Is this a usefull tool to have?

    Hello fellow developers.
    I would like you to give feedback on this macro I coded.


    educational value: the code contains comment lines to explain various coding techniques. Did it help to...
  20. Re: How to use VBA to set up equal number of Rows per Page for a worksheet for printing

    Try this sub, let me know if you like it


    Option Explicit

    Sub paginate()
    'insert page breaks in activesheet
    'to avoid last page with just a few lines

    Const minNbrLinesOnPage As Long = 5...
  21. Replies
    7
    Views
    413

    Re: Capture cell data into other cell

    It's too complicated to put every historical value on a new sheet.
    Instead create one sheet, let's call it history, and copy the daily values there, one row per day, one column for every text value....
  22. Re: How to use VBA to set up equal number of Rows per Page for a worksheet for printing

    Did you try the "fit on page" option? It should reduce the scale a bit so it fits all on one page.
  23. Replies
    7
    Views
    413

    Re: Capture cell data into other cell

    Hi riskier4ra. Not clear to me what you want.

    Do you want this historical list on sheet2? If you want to study change patterns I suggest that the list also contains a date/time stamp, correct?...
  24. Replies
    9
    Views
    370

    Re: hyperlinks on new worksheets

    Could you please tell me if the given solution works for you?
  25. Replies
    9
    Views
    370

    Re: hyperlinks on new worksheets

    run this macro to solve your problem



    Sub HyperlinksToSheet()
    Dim sh As Worksheet
    Dim hl As Hyperlink

    For Each sh In ThisWorkbook.Worksheets
    For Each hl In sh.Hyperlinks
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4