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!

OldFella

Members
  • Content count

    27
  • Joined

  • Last visited

  • Days Won

    8

OldFella last won the day on February 20

OldFella had the most liked content!

Community Reputation

9 Neutral

About OldFella

  • Rank
    Newbie 2
  1. Slow Running Code

    Hi Caleeco Brilliantissimo!!!! A simple, little 3-character tweak and the thing runs like a dream! CPU load - yes, that makes sense. Unfortunately it's not as simple as just upgrading the CPU; it would need a major re-vamp. I'm currently using an i7-4790K; to move up to an i7-8700K would mean changing the existing Z97 motherboard to a Z370 board to accommodate it. The Z370 board won't accept my existing DDR3 DRAM so I'd have to upgrade that to DDR4 - which wouldn't be a bad thing! - so all in all I'd be up for a major overhaul. I can live with what I've got for now; instead, I shall patiently wait for 3D-Xpoint to really take hold and then upgrade to something which is truly bleeding-edge. Data missing - the highlighting of the vacant block was just an observation, not a problem. The missing data was because of the missing "ws." It was the highlighting that seemed odd, but it wasn't a problem. It's cured itself now anyway, with the addition of the "ws." So, once again, my very, very grateful thanks for your time, skills and perseverance. This morning I've gone back through the existing workbooks and upgraded the coding, and then casually back-tested a further 12 years of data. And it's not even lunchtime yet! This time you deserve more than just a couple of glasses - now you've got it on tap for consumption as/when needed! https://img.frl/hbw4c https://img.frl/ai641 Wahhhh!!!!! They won't load! A Very Satisfied OldFella
  2. Slow Running Code

    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.
  3. Slow Running Code

    Hi Caleeco After all these years of debate, I've finally resolved it - size doesn't matter! I tried the new code on two worksheets: 1,840 rows transferred the data in 4.9 seconds 3,545 rows transferred the data in 4.6 seconds Or there again - maybe bigger is better? Anyway - it looks like your intense session has proved to be extremely worthwhile. Compared with 50 minutes, and still incomplete, 5 seconds for 1 column is a fantastic start! Now I'm itching to see how it goes when extended to the four columns. Might have to move you up from beer to whisky!! Many thanks - once again!
  4. Slow Running Code

    Hi Caleeco No problem - I've filled the two nuisance columns on each sheet, hopefully that will resolve that little issue for you. I've also emailed you an updated workbook as requested. Many thanks.
  5. Slow Running Code

    This is a follow-up to my earlier thread - 'Selectively Writing Horizontally Occurring Values to a Column' The code, posted below, has now been run on 10 or so files. On a worksheet with 500 rows it typically completes in 2 - 3 minutes unless there are any #Ref! errors on the sheet, in which case it slows markedly but still completes, displays the error pop-up message, then fills the 4 columns with data as normal. On a sheet with 4,000 rows, it typically takes 10 - 15 minutes to complete, assuming no #Ref! errors. On the most recent 3,600 row sheet, the timing has extended dramatically. I let it run for about 50 minutes, got tired of waiting for it and interrupted the code. Debug showed that it was paused at the first instance of 'End If'. There were no #Ref! errors on the sheet and after 50 minutes it had only transferred data to row 1903 of the first column of the first sheet - barely 25% complete. Ages ago I had a similar problem with a procedure that formed part of a sheet that I kept repeatedly duplicating; it seemed that every iteration of the sheet added something behind the scenes that made the procedure run slower and slower with each new version of the sheet. I'm wondering if something similar may be happening here - why does it run much, much slower on a later iteration than it did on the early versions? I've been trying to locate the guidance I had on that previous occasion, for searching beneath the covers of Excel, in case that can assist but to date have not been able to locate it. Are you able to see what has gone astray? Many thanks - again! Sub FO_RV_SH() 'Call DataProcesser(Sheet Name, the code you're looking for eg SL/SH, The column the SL/SH is in, the column you want to output to, is it FO/RV or just FO data?) Call DataProcesser("Back-test Layout v3", "SH", "Q", "H", "FO/RV") End Sub Sub FO_RV_SL() Call DataProcesser("Back-test Layout v3", "SL", "Y", "L", "FO/RV") End Sub Sub FO_SH() Call DataProcesser("Back-test Layout v3", "SH", "AG", "I", "FO") End Sub Sub FO_SL() Call DataProcesser("Back-test Layout v3", "SL", "BM", "M", "FO") End Sub Function DataProcesser(wsBT As String, sYesNo As String, sYesNoCol As String, sOutputCol As String, sType As String) '//Source: www.ExcelWTF.com '//Purpose: to process horizontal data entries and transpose them with set criteria Dim ws As Worksheet Dim rYesNO As Range, r As Range Dim lr As Long, items As Long, i As Long Dim sItem As String Application.ScreenUpdating = False Set ws = Sheets(wsBT) lr = ws.Range(sYesNoCol & Rows.Count).End(xlUp).Row Set rYesNO = ws.Range(sYesNoCol & "3:" & sYesNoCol & lr) items = ws.Range(Cells(1, ws.Range(sYesNoCol & 1).Column), Cells(1, ws.Range(sYesNoCol & 1).Column).End(xlToRight)).Count For Each r In rYesNO If r.Value = sYesNo Then For i = 0 To items - 1 sSeq = ws.Range(sYesNoCol & r.Row).Offset(, i + 1).Value If sType = "FO" Then If sSeq = "FO" Then ws.Range(sOutputCol & r.Row).Offset(i).Value = "FO" Else If sSeq = "RV" And sOutput = "RV" Then ws.Range(sOutputCol & r.Row).Offset(i).Value = "RV" ElseIf sSeq = "RV" And sOutput = "FO" Then ws.Range(sOutputCol & r.Row).Offset(i).Value = "FO" ElseIf sSeq = "FO" And sOutput = "RV" Then ws.Range(sOutputCol & r.Row).Offset(i).Value = "FO" ElseIf sSeq = "FO" And sOutput = "FO" Then ws.Range(sOutputCol & r.Row).Offset(i).Value = "FO" ElseIf sOutput = "" And sSeq = "FO" Or sSeq = "RV" Then ws.Range(sOutputCol & r.Row).Offset(i).Value = sSeq End If End If Next i End If Next r ws.Range(sOutputCol & ws.Range("G" & Rows.Count).End(xlUp).Offset(1).Row & ":" & sOutputCol & ws.Range(sOutputCol & Rows.Count).End(xlUp).Offset(1).Row).ClearContents Application.ScreenUpdating = False End Function
  6. User Input Pop-up to Update VBA String

    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.
  7. Guess who again! A new little challenge - but still on the original theme. I've renamed the sheet 'Back-test Layout v3' to 'Back-test Template' and I'm now starting to create new workbooks for the multiple data sets that I want to back-test. The first realization is that I'm going to have to go to each of your 4 x subs, and my added sub, and change the sheet name in eight places - on at least 60 worksheets. To simplify it - are you able to create a user input pop-up, that I can assign to a "Sheet name?" button, into which I can then enter the current sheet name which will replace the string "Back-test Template" in each of the five sub-routines? Sort of like Find / Replace, but applied to the VBA code instead of to formulae on the worksheet. Thanks
  8. Getting Rid of the Hidden Content of Blank Cells

    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
  9. 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
  10. Hi Caleeco Sorry about the blank column and the consequent re-code. The lack of formula in SH +1 was an exercise in convenience (laziness?) for me, resulting in new problems for you. If you need a challenge, I'm your 'Go To' man! You may recall that I mentioned in one of the posts or emails that the SH/SL values are peak/trough values for S, so I figured there was no point in coding SH +1 to look for a value that exceeded the SH column value as by definition SH +1 cannot exceed SH as it would mean that SH is not a peak. Seemed like a good idea at the time I have to go out for a while; when I come back I'll apply the new code and let you know the outcome. Could it be that we're suddenly at the end of the exercise???
  11. Getting Rid of the Hidden Content of Blank Cells

    Hi Caleeco Not sure which 'c' you meant so I checked each of them: IsEmpty(c) - c = Error 2042 c.HasFormula - c.HasFormula = True (c.Value2) - c.Value2 = Error 2042 I'm sure you'll have a solution to this but since I'm always up for solving my own problems wherever possible I did a bit of a scroll round the 'net. I won't mess with your coding, but http://www.vbaexpress.com/forum/showthread.php?25189-Solved-vlookup-error-2042 looked like it could be helpful. Thanks David
  12. Getting Rid of the Hidden Content of Blank Cells

    Hi Caleeco Sorry to have to re-open this one but I have a new issue. As the problems are close to being resolved in the Selectively Writing Horizontally Occurring Values to a Column thread, I'm now preparing the main worksheet to receive data from the FO_RV worksheet. The first issue I've discovered is text being displayed when it shouldn't, as a result once again of an invisible, underlying object. The date and time shown here should only be displayed if there's a value in any of the four preceding 'blank' cells. "No worries", thought I. "I'm now armed with 'DoIt'!" So I ran 'DoIt' and was greeted with a Runtime error popup: I then ran Debug and was presented with the following: What have I screwed up this time, and how do I get 'DoIt' to run properly?
  13. Hi Caleeco I'll email you the updated workbook shortly, but we're now in the home straight - just one last imposition on your time. You may have gathered from the earlier post that my gut feeling re the non-RV FO values proved to be correct and there are many FO occurrences that your coding simply can't detect. After a bit of thought I realized that I could fairly readily create new blocks of formulae similar to those already in place, to generate the missing FO values, and that it should then be a fairly easy task to adapt your coding to accommodate these new formula blocks. The requirement with the new blocks is to: a) Copy the horizontal occurrences of "FO" from col AH - BK and write them vertically in col I; and b) Copy the horizontal occurrences of "FO" from col BN - CQ and write them vertically in col M following the same sequencing method as with the existing coding. It's conceivable that subsequent rows may try to write to a cell in col I or M that already contains FO, so you may need to protect against an error occurring here? At least this time there's no conflict between RV and FO being written to the same cell. If there's any coding advantage in having the blocks of SH formulae contiguous and the blocks of SL formulae contiguous, feel free to rearrange the columns in my current layout to suit. Column Q and beyond is your territory; my interest is in col A - O. We're so close to completion that I've already moved back to the main worksheet and am busily setting it up to take the data from col H - O of the back-test sheet. I'm looking forward to then testing the output from the main sheet against my manually determined output. I'm hoping for no mis-alignment, although I'm fully expecting the coding to show up errors in my manually determined output, and this is one of the reasons for wanting to automate the number crunching. Speed and accuracy - computers fill that role so much better than the human brain, especially my one! And again, in anticipation - thank you so much for your kind assistance with my little project.
  14. Hi Caleeco Need I say it? (but I will anyway) - it works perfectly! But better than just "works", it has totally transformed the run-time. The original coding copied the RV/FO values across to col H or L one at a time. A 500 row sheet took about 2 minutes to fill each column and I'd figured that before I moved on to the sheets that have thousands of rows I should go and warm up the coffee machine. Now the transfer is instant. One click and Bingo!, the column is populated. Admittedly I've only tested it on the sheet that I sent you, so only 40 rows, but the difference was instantly apparent and I'm assuming that the same will happen with the larger sheets. I'll test that shortly. Brilliant! Now, I've had a play with the non-RV FO and I've managed to create what is, in effect, just a very expanded variety of the Q - AE columns. "very expanded" means that the sheet will now grow by a further 60+ columns. As noted, the aesthetics are irrelevant so for the reasons you stated, let's stay with the proliferation of helper columns. The coding concept will be identical except that here we'll only be transferring a single code ("FO"), so no need for any fuzzy lookup. I've already discovered that the transfer of just a single value means some minor code mods that are beyond my simple mind. I'm OK at simply changing column letters, but re-coding it to transfer one value instead of two is way outside my scope! I'll endeavour to create a sample sheet for you later today. Thanks once again for your help with this.
  15. 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.
×