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!


  • Content count

  • Joined

  • Last visited

  • Days Won


OldFella last won the day on May 3

OldFella had the most liked content!

Community Reputation

11 Good

About OldFella

  • Rank
    Newbie 2

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. OldFella

    Obtaining Data from CSV Files

    Hi Caleeco Nice to see a little bit of new blood on the site, to help keep the grey matter up to scratch for designing eVTOL flying taxis or whatever else they keep you busy with during the day. I have a new problem. I'm trying to import data into an open worksheet, on a daily basis, from a series of unopened .csv files. If the .csv file is opened with Excel, each file may have hundreds of rows but only two columns - a date in col A and a numerical value in col B. The date is currently displayed in yyyymmdd format although of course it's text, not a date. I'm planning to change the display so that it reads ddmmyyyy which, to me, will look a bit more normal, even though without separators. On the open worksheet my thinking was to enter the date of interest, then use VLOOKUP to find that date in the .csv files of interest and return the numerical value in col B. That works fine if the .csv file is open, but not so good when it's closed. So - I have two queries: 1. Since VLOOKUP won't work with a closed file, I need VBA to achieve what I'm after as my understanding is that VBA can access closed files. As I'm now totally out of my depth, could you possibly generate appropriate coding for me? The .csv files are in F:\Trading\ASX\Portfolio. I won't run through the whole list, I'll be able to add to or modify your coding once I see what you've done, but the first few are AJM.csv, CTM.csv, CTMOB.csv and CVS.csv. The destination workbook is Portfolio.xlsm and is in the F:\Trading folder. The destination for the numeric value from the AJM.csv file is cell F7; from CTM.csv is F8; from CTMOB.csv is F9; and so on. Let me know if you need further detail. 2. A date that displays as ddmmyyyy looks horrible without separators. How do I convert that text into either dd.mm.yyyy or dd/mm/yyyyy? And having done that, can the date in that format be used to match up with the date in the .csv files? If they won't match, then maybe I'll need to use a hidden column on the destination worksheet and do it in reverse - convert dd.mm.yyyy (which will display on the worksheet) into ddmmyyyy (which will be in a hidden column). Does that make sense. Many thanks Ye Olde one (and I won't mention that game that was played in Russia recently)
  2. OldFella

    I've Messed Up =LOOKUP

    Hi Caleeco For a "stab in the dark", that was pretty well aimed! As expected, it works perfectly - but I've learned by now to expect nothing less Thank you so much for that, now the sheet is performing as normal again. I'll try to come up with something that taxes your brain a bit better next time. Cheers
  3. OldFella

    I've Messed Up =LOOKUP

    Hi Caleeco Looks like you've been having it pretty easy with me not pestering you with my problems. However, I have a new problem. Still on the same spreadsheet as previous, but a little enhancement has stopped working. I've been using =LOOKUP(2,1/(1-ISBLANK(S:S)),S:S) to find the last value in a column that consists of blanks and manually entered numbers. It worked fine until I decided to use a formula to automatically enter new values in the column instead of me entering them manually. There are now about a hundred blank cells in the lower section of the column, each with an underlying formula. Now my =LOOKUP cell is blank; presumably it's showing the value of the last formula in the column - a blank. How do I resolve this? I searched the 'net and tried an assortment of similar formulae, but nothing worked. Now I need proper help. As always, I'm open to using a formula or VBA, whichever is the more efficient. Many thanks.
  4. OldFella

    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
  5. OldFella

    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.
  6. OldFella

    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!
  7. OldFella

    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.
  8. OldFella

    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
  9. OldFella

    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.
  10. 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
  11. OldFella

    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
  12. 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
  13. 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???
  14. OldFella

    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
  15. OldFella

    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?