Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!


Popular Content

Showing most liked content since 01/23/2018 in all areas

  1. 1 point
    Hi Caleeco I think it must be time I considered another system upgrade; my unit took 36 seconds to run the code. You're right though - it's astounding how much faster this code runs than the initial version. Can't match 6 seconds though! That was the good news. The not so good news is that the output is rather interesting. On the first run, it filled the four columns on the first sheet but nothing on the second sheet. Then, when I looked at the output on the first sheet, I couldn't match it to the horizontal data. I then ran the code on the second sheet. The output was fine - it matched the horizontal data - but then I noticed that the output extended way below the rows of data. I then ran a series of run the code, delete the output, run it on the other sheet, delete the output, until I finally nutted out what I believe is happening: When I run the code from the first sheet (the h12Data sheet) it initially, correctly, transfers the h12 data from horizontal to vertical, but then when it moves to the D1 sheet it transfers that horizontal data and prints it on the h12 sheet, overwriting the initial values. That's why, when I first looked at the top of the h12 page, the values in the columns were wrong. When I scroll lower on the sheet, to below row 1840, the column values are correct because down there they've not been overwritten. That also accounts for why the columns on the second sheet were blank. When I delete the transferred values, to give me empty columns again, and then run the code on the second sheet, a similar thing happens. This time the transferred values are correct because the code executes in a fixed sequence; firstly the h12 data is written to J, K, N & O, then the D1 data overwrites it, and since I'm now on the D1 sheet the transferred values match the horizontal data. And then, when I scroll down the sheet, there, below the rows of D1 data, are the values that have been transferred from the h12 sheet. A consequence of this is that I have to run the code from the h12 sheet, not from the D1 sheet, so I've deleted the Update button from the D1 sheet to mitigate a potential lapse of memory. So from my reading it's a pretty simple fix - (simple for you, that is; impossible for me!) - just tweak the code so that when it reads the D1sheet it prints the values to the D1 sheet, not to the h12 sheet. I've also noticed that even once the data transfer is complete, the code still appears to be running in the background. When I run the code I can "hear" the load that has been put on the CPU as there is a distinct change in the frequency of the cooling system fan/pump. Later, when I delete the values in col J, K, N and O, there is the same change in frequency from the cooling system. I would not have expected to hear any change when I simply delete cell contents; iIt's as though it's mother hen, watching over her chicks, and making noise because I've destroyed them. Also, for what it may be worth, I notice that the last block where values should have been written but aren't, is highlighted as if I'd just run a copy/paste. Hope that diagnosis is of some help.
  2. 1 point
    Hi Caleeco It took me a little while to get my head around what you were proposing - even with starting at 0430 hr this morning, when I was supposedly bright-eyed and bushy-tailed! - but I eventually saw what you were thinking. And, as I should have anticipated, it works a treat! I've spent the day making use of the new code. I've also done a lengthy but badly needed re-vamp of my main worksheet. Previously, using my manual system, it typically took me about three days to cover a year of back-testing. Today I've covered eight years! A month's work completed in a day! And better, I'm not brain-dead as I used to be with the previous method. I'd put more beers here but I don't want you getting drunk! Once again - grateful thanks for all your help.
  3. 1 point
    And another one bites the dust!! Well done, that solved it. The errant date + time has now disappeared off the worksheet, and presumably from every other location in the workbook that these annoying, invisible gremlins were lurking. I suspect that one of the comments in the MSDN thread had it nailed. My routine practice, given the complexity of these worksheets, is to spend time developing code in a small section of the sheet. Once I get it fully functional then I copy that block down, or across, the sheet. Once I've proven that sheet, then I clone it and make new, fully operational sheets. The suggestion from MSDN was that the problem you've just resolved is not an issue with my existing formulae, but with the output of a previous formula from a previous iteration of the sheet that resulted in a null, or #N/A, or #DIV0, or whatever that lurks unseen beneath the apparently blank cell, waiting to cause havoc. The new coding should fully take care of this though and I'm making up a schedule/check list that will ensure that each time I open up an existing workbook I run DoIt to purge the book of these vermin! Thanks again. I'll now re-mark it as Solved
  4. 1 point
    Hi Caleeco Here's the new issue that I referred to last night. I have also emailed to you a sample worksheet so that the following notes make some sense. The sample worksheet, with some fairly old data, illustrates my problem. Column O interogates the data in columns D - G and returns a value when conditions are true. That value then triggers columns Q - V. There is one time interval between each of the columns Q - V; on this sheet the time interval is 12 hr (col C). I'm only interested in cells in col R - V that return either RV or FO; (you'll note from the underlying formula that col Q will only ever return LC or Not LC). The occurrences of RV and FO then have to be copied to their corresponding cell in col H - for which I'd appreciate some masterful VBA. I've manually transferred the values to col H to illustrate the desired end output. In essence it's a case of reading horizontally across col R - V and writing the RV and FO values, if any, vertically down col H. You can probably see that the cell in col Q of any given row corresponds to the cell in col H of that row. I've colour coded one set of cells to make the equivalence more obvious. A potential source of error is that if the "SH" occurrences are close enough together, more than one cell from col R - V will try to copy to col H. You can see from the sheet with the arrows on it that there are three examples of this in the lower area. The rules here are that: a) An RV adding to an RV copied from a preceding row remains as an RV; b) An RV adding to an FO copied from a preceding row remains as an FO; c) An FO adding to an RV copied from a preceding row replaces the RV; and d) An FO adding to an FO copied from a preceding row remains as an FO. Hope that is all clear; really looking forward to the outcome as this little issue is currently a major stumbling block with advancing the further development of the worksheet. And hey! Did you notice? There's not one cell on either worksheet that displays as a blank but has something hidden, lurking beneath the surface. Progress!! And while I'm on this topic, the lesson learned with the previous problem is: Only copy VBA code from the forum; do NOT copy it from the emailed notification (because it puts line breaks in that shouldn't be there!). Any other forum users reading this - take note!
  5. 1 point
    Hi Caleeco WtF??? We're there! After sorting a couple of minor hiccups, we have fait accompli!!! First minor issue was that the second sub-routine was calling the wrong function. You'd simply forgotten to update the function name, so I fixed that readily enough. The second issue, and there were two instances of it, was that the new coding was writing FO to col I and M one row above where the same FO had been written in col H and L. An FO occurring, for example, in SL +2 was writing in col M to the SL +1 position. That too had a nice easy fix - I simply added in two new, blank columns, 1 in each block, to push the ocurrences down by 1 row. I've emailed you a copy of the updated workbook so you can see the changes and the end result. The final embellishment was to combine the 4 x sub-routines into one, and then assign the new sub to an 'Update' button on the worksheet. To put your work of art into practice, all I have to do now is enter new blocks of data into col B - G, hit 'Update', then make use of the output from col H - O. Caleeco, thank you so, so much for your time and your skills. It has taken me a year of back-testing to derive what, to you, must look like an incomprehensible assortment and inter-relationship of meaningless symbols. Following the same manual procedure as used last year, the back-testing that I still have ahead of me would also have taken about a year - maybe more. I still have some fine-tuning to complete on the main worksheet but once that's complete - hopefully over the weekend - I anticipate the testing to now require no more than a month. Brilliant!! The above are extremely well-deserved. Maybe one day I'll get the opportunity to convert them into real, live, cold and wet ones. Best wishes and grateful thanks from a very satisfied OldFella
  6. 1 point
    Hi Caleeco Second impressions - exactly the same as the first impressions - brilliant! One feature of my worksheets is that at this stage of their development they don't stand still. What you see today is unlikely to be what you see tomorrow. In line with this, the sample worksheet I sent you is already obsolete. Don't worry, all I've done is add in a few more columns. I modified your coding and changed the column letters and it all still functions as intended. I've also added in a second block of formulae that performs a similar task to the first block. I'll email you a revised sheet so you can see what I've done. I then created a second block of your coding, adapted it to match these new columns, and it too runs as intended. The amended coding, and the new coding, are with the emailed worksheet - hopefully! I'll leave it up to you whether you wish to extend the coding so that the helper columns are eliminated; from my perspective it's not important. This worksheet is actually just an intermediate step in the process; the main sheet accesses the values in col H - O using Index/Match based on date and time, so the appearance of this sheet is not critical. However - if you want the challenge of streamlining it, and especially if it improves the overall efficiency, then I'm more than happy to incorporate the upgrade. I would appreciate one minor change though. When an SH or SL value occurs within the last few rows of data the coding continues to write FO/RV values below the last row of data. You'll see what I mean when you look at the emailed sheet. Can you please modify the coding so that it ceases writing FO or RV values below the last row of data. I'm now going to spend a few days very carefully testing the coding. You'll see on the worksheet that there are two columns headed cT FO. These columns will display externally sourced FO values. I'm having some difficulty with the accuracy of the external input and I'm hoping that your coding will pick up all the instances of FO situations that the external source ought to determine - but doesn't. My gut feeling is that it won't pick them up and if that proves to be the case then I may come back to see if you can somehow develop code to pick them up. If you thrive on challenges, this will be one!! I'm currently endeavouring also to get correct results from the external data; if successful, that will negate the need for your challenge. Thanks once again for bringing it this far forward.
  7. 1 point
    Hi Caleeco First impressions - beautiful to watch! When I hit the FO-RV button and watch the symbols start appearing down col H, it's all I can do not to wet myself! I'm going to give it a bit more extensive testing but based on what I just watched I don't anticipate any problems. If all is well then I'll get back to you, hopefully later today, with the next step. Definitely a beer owing from this one!
  8. 1 point
    Sorry for the delay - I finally got around to amending the code and running it. I'm delighted to say that the outcome is stunning. It purged a 22-sheet workbook in about 15 seconds. I then ran Ctrl-G / Blanks and was horrified with what I saw. I had regularly been finding cells on the various worksheets that weren't responding as they should, or that formulae were missing from odd cells. Using your code, followed by Ctrl-G, I can now see exactly where the problems are and can resolve them. Brilliant!! That's one beer I owe you! I'm now going to sit here and draft up your next beer-earning exercise. It's convoluted, as you'll eventually see, so it's going to take me a while to very carefully outline what I'm trying to achieve. Probably won't complete it tonight so don't hold your breath. Thanks again for sorting this current issue.
  9. 1 point
    haha I enjoy problem solving with VBA so keep them coming Yes we could do that, but it would need a slight change. The original code looks at the list items top to bottom... but if we start removing items, the counter in our loop will cause the code to break Luckily we can just start at the end of the list and step backwards Private Sub btnTransfer_Click() '//Source: www.ExcelWTF.com '//Purpose: Move Selection from List1 to List2. Deletes item from List1 Dim i As Long For i = (ListBox1.ListCount - 1) To 0 Step -1 If ListBox1.Selected(i) Then ListBox2.AddItem ListBox1.List(i, 0), 0 ListBox1.RemoveItem i End If Next End Sub Hope that helps Caleeco
  10. 1 point
    Hmm, not done that before... but had a crack at it! Assumed your second Listbox is called 'Listbox2' Private Sub btnTransfer_Click() '//Source: www.ExcelWTF.com '//Purpose: Move Selection from List1 to List2. Clears selection each time. Dim i As Long ListBox2.Clear 'Delete this line if you wish to transfer multiple selections For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) Then ListBox2.AddItem ListBox1.List(i, 0) End If Next End Sub Please try the code above. Also please note the Listbox2.Clear, this will reset the contents each time the button is clicked. Delete that line if that isnt the intended use case Hope that helps Caleeco
  11. 1 point
    Give this a go, place this code in the Userform Codesheet VBEditor > Double Click UserForm1 in the Project Window > Double Click anywhere in the Userform Private Sub UserForm_Initialize() '//Source: www.ExcelWTF.com '//Purpose: Initialise and Sort Listbox from Sheet Dim i As Long, j As Long Dim SortBox As Variant With Sheets("Data") ListBox1.List = .Range("B1", .Range("B1").End(xlDown)).Value End With With ListBox1 For i = 0 To .ListCount - 2 For j = i + 1 To .ListCount - 1 If .List(i) > .List(j) Then SortBox = .List(j) .List(j) = .List(i) .List(i) = SortBox End If Next j Next i End With End Sub Let me know how you get on Caleeco
  12. 1 point
    Hey Hey Thanks! No problemo, see answers below Alphabetic "Data" Just the defaults "Userform1" & "Listbox1" Thank you Z
  13. 1 point
    Hey Ziya, Good to see you back on the forum! Yes, that shouldn't be too difficult. Some questions for you: Is this an alphabetic or numeric sort? What is the sheet name with the data? What are the assigned names for your Userform and Listbox (Check the Properties window in the VBEditor) Let me know Caleeco
  14. 1 point
    Hi David, Glad you liked the CF solution! Yes, with the spreadsheets I build; I always go for simplicity. As much as I enjoy writing code, VBA isn't always the best answer Excel has lots of in-built tools we can take advantage of! CF is very robust, so many things you can use it for! Ok sounds good, let me know if the colours don't change correctly (I did some testing on my sheet which seemed to work). Haha, yes when dealing with big spreadsheets... efficiency savings are key! Glad I could help Caleeco