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 September 25 2018

OldFella had the most liked content!

Community Reputation

12 Good

About OldFella

  • Rank
    Newbie 2

Recent Profile Visitors

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

  1. If you use Find & Select / Replace on the column that contains the addresses you should be able to replace St without problem. Replace recognizes spaces. I assume that your street names that begin with St. will not have a space before St, whereas Something St does have a space before St, so in Find what: you type " St" (without the quote marks but with a space before the S) and in Replace with: you type " Street" (again, without the quote marks but with a space before the S). That should fix the street names and leave names like St. Peter unchanged.
  2. Hi SyafiniAlias I had a similar requirement some time ago. Eventually I developed an easier way of getting the data, but while I was trying to get the data from a pdf file I was working through https://www.myengineeringworld.net/2012/07/vba-macro-to-open-pdf-file.html Have a look at that link - it may help you.
  3. Hi Caleeco If/when you get around to this one, I've simplified it a little bit. After hours of trial and error I've managed to create coding that achieves a) Lock in the Current Data, so now it's only the three sub-wishes and the dynamic charting that are still confounding me. No doubt what took me hours would have taken you minutes, but at least I finally nutted it out so I'm happy.
  4. Hi Caleeco I have a couple of new issues to exercise your grey/white matter. They're not urgent or critical so if you're happy to attack them, please just work on them when the mood suits. No need for a 0300hr finish! 🙂 I have a worksheet with 30-odd rows of data, below which I have a table that sits alongside a chart that plots the data in that table. Column B of the table contains dates and column C contains data values taken daily from cell Y11, higher up on the worksheet. Headers are in cells B42 and C42. The dates in the table show the most recent date at or near the top and the oldest dates at the bottom. As needed, I add in 5 more rows of blank cells below the headers. I actually managed to create coding to do this - to move the existing data down 5 rows and put borders around each of the 10 newly created cells below the headers. I would like to improve the sheet in two ways: a) Lock in the Current Data Each morning, in the cell in col C alongside today's date in col B, I set =Y11. At the end of the day I lock in that value. Next morning I move up 1 row and in col C, alongside what has now become the current date, I again set =Y11. I'd like to automate that process with coding that I can attach to a button that will: Look for the first cell below C42 that displays a value (it will currently contain the formula =Y11); Set that as a fixed value, not as a formula; Move up 1 row; If that cell is blank then set that cell =Y11, otherwise (if the table has no blank cells) Call Add5Rows and then set the blank cell (above the first displayed value below C42) =Y11; There are now three sub-wishes. I actually have a number of iterations of this workbook, which is one reason I'm trying to reduce the repetitive manual data entry. In each book, one sheet handles daily results, one handles weekly results, and the other handles monthly results; the wish list and comments above referred to the daily sheet. If it's practicable to do it, after entering =Y11 in col C I'd like to move to the corresponding cell in col B and increment the value. For weekly and monthly sheets I'm guessing it's straightforward - increment weekly by 7 days and monthly by one month from the value in the cell below. Daily could be an issue given that I only record data Mon - Fri, so maybe after Calling Add5Rows the coding could add three days instead of one? That will lock the current value in the table, add in 5 new rows if the table is full, and next day's/week's/month's date will automatically display together with the new value of Y11. b) Static v Dynamic Chart This is one we touched on a while back and I'll confess I've never been able to consistently implement. The current chart is static; the data range is fixed and has to be updated every time I add in a new block of 5 rows. I'd love to make it dynamic so that whenever those new rows get added the chart grows like Topsy to accommodate the new data range. As an example, the chart on screen at the moment shows Chart Data Range = Summary!$B$43:$C$70. Once I Call Add5Rows it becomes Chart Data Range = Summary!$B$48:$C$75 so I have to manually change $B$48 back to $B$43. How, using Offset or whatever, do I convert this into a dynamic chart? Now you can go have a well-deserved beer!! And in case you want it, here's my amateur effort Add5Rows. One improvement it needs but I don't know how - how do I get rid of Range.Select ActiveSheet.Paste? : Sub Add5Rows() Dim LastRow As Long Dim rng As Range With ActiveSheet LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row 'Find the last row with data in it End With Range("B43:C" & LastRow).Cut 'Take the existing block of data and move it down 5 rows Range("B48").Select ActiveSheet.Paste Set rng = Range("B43:C47") 'Put borders around each of the newly created blank cells With rng.Borders .LineStyle = xlContinuous .Color = vbBlack .Weight = xlThin End With End Sub Many thanks.
  5. Hi Caleeco I can see where the problem is - the notifications I'm getting, and your emails, are showing the originating address as [email protected], not [email protected] Anyway, I've now deleted the [email protected] variety from my address book so that should no longer be a source of confusion. Befuddled Olde One
  6. Sure enough - I used the other address - [email protected] I'll resend now. Sleep well!
  7. Hi Caleeco You should already have both an early iteration of the worksheet and a sample .csv file, both emailed to you on 18 July. The worksheet is very much a work-in-progress and the current layout looks somewhat unlike the version emailed to you, but the concept is identical. I noted in the first post on Monday that the value that's read from the .csv file has to be written to 'Summary' column F; in the sample worksheet I emailed to you it's to be written to column G. Let me know if you don't have those files and I'll resend them. I recall we had confusion months ago with me sending things to the wrong WTF address; maybe I did it again! 😩
  8. Hi Caleeco I've just come across a web page that shows that accessing a closed .csv file is practicable with VBA. Have a look at https://blog.udemy.com/vba-open-file/ and scroll down to Example 2. I'm sure that will make far more sense to you than it does to me - hope it helps. Or open it as a workbook? 😉
  9. Hi again Caleeco Sorry to be a pain but I've just been simulating the effect of the requested coding and I've found a small issue. There are times when not every symbol in 'Summary' col B will have a corresponding .csv file. I can readily overcome this by increasing the amount of data in each .csv file, but it complicates the coding slightly because instead of reading data from a specific cell in the .csv file it will now need to read the last value in the column. So, in items c) & h) above, please replace "Read the value in the .csv file cell B2" with "Read the last value in col B of the .csv file". That should ensure there is always a .csv file to correspond with each symbol in 'Summary' col B and hence each symbol would have a value in col F, although for safety it might be sensible to include some error trapping to allow for a situation where there is no corresponding .csv file. And of course the first line in d) of the upper block is no longer valid; there will now be multiple cells of data and different numbers of cells in each .csv file. In text mode, it's always the last value in the last line that has to be read. Thanks
  10. Hi Caleeco My sincere apologies for the prolonged delay following up with this. Far, far too many side issues! I've made some slight changes to the requirements, which hopefully have simplified it a bit. Anyway, here's the description: a) In a folder named 'Results' I have a worksheet named 'Summary'; b) Column B contains a list of symbols, usually three alpha characters but the length can be between 1 and 5. Data starts in row 6 and currently extends down to row 23 although the last data row will change from week to week; c) In the 'Results' folder there is a sub-folder named 'Data1' in which there are (currently) 18 .csv files. These files have the same name as each symbol in col B of the 'Summary' sheet. Cell B6 of 'Summary' has the symbol AAD; in the 'Data1' folder there is a .csv file named AAD.csv. Cell B7 of 'Summary' has the symbol BBD; in the 'Data1' folder there is a .csv file named BBD.csv. And so on . . . . d) If I open them with Excel, each .csv file has just 4 cells of data. A1 and B1 are headers, A2 contains a date and B2 contains a numeric value. The .csv files are comma delimited and are created, on demand, using an external process. A typical file (in text mode) is: "Date","Close" "24/09/2018","0.67" The quote marks are optional and are not really needed. That was the easy part. I need VBA coding that I can attach to an "UPDATE" button and that will: a) Read the value in 'Summary' cell B6; b) Open the .csv file (in read only mode) that has the title of the value in B6; c) Read the value in the .csv file cell B2; d) Close the .csv file; e) Write the value to 'Summary' cell F6; f) Read the value in 'Summary' cell B7; g) Open the .csv file (in read only mode) that has the title of the value in B7; h) Read the value in the .csv file cell B2; i) Close the .csv file; j) Write the value to 'Summary' cell F7; k) Read the value in 'Summary' cell B8; l) Open the .csv file (in read only mode) that has the title of the value in B8; and continue looping until it reaches a blank cell in 'Summary' col B, each value in 'Summary' col B has been read and the value in cell B2 of the corresponding .csv file has been read and then written to 'Summary' col F. I've found that I'm unable to achieve the required result using formulae because regardless of how I try, I cannot get the data in 'Summary' to automatically update whenever I generate each new batch of .csv files unless I open each individual .csv file. Hence the need for VBA coding to open and close each .csv file. Hope all that makes sense. I've done away with the need for VLOOKUP, and the date problems have been resolved, so it's down to just needing looping coding that will sequentially copy a single cell of data to the summary sheet. Many thanks
  11. 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)
  12. 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
  13. 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.
  14. 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
  15. 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.
  • Create New...