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!

All Activity

This stream auto-updates     

  1. Earlier
  2. Slow Running Code

    Hey, That's excellent news! Glad it is now working. Haha, yes that's the trouble with coding. Seemingly insignificant things can have big consequences! Ah ok yes, sounds like it would require a lot of modification. Almost better to wait until the next gen of tech is out and build a shiny new system No problem, glad I could help. Like always, stop by the forum if you have any other Excel/VBA problems you'd like me to take a look at Cheers Caleeco MARKED AS SOLVED
  3. 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
  4. Slow Running Code

    Hey mate, Back from work! Hmm sorry to hear the bad news Thanks for the detailed description, it certainly help pinpoint the problems. Problem 1 - Data not being output to the correct sheets This is an oversight on my part the final part of the macro (where data is written back to the sheet) is not sheet specific. So it will always output on the sheet it was run on. D'oh! Please find this line Set Destination = Range(strOutputCol & "4") and change it to Set Destination = ws.Range(strOutputCol & "4") Problem 2 - CPU load I'm not surprised, you're working with some large data with thousands of formulas. Every time you add (with the code) or delete (manually) columns of data the whole sheet needs to re-calculate. This is what is causing the cooling system to be put to the test! This can be avoided by removing formulas you no longer need. Eg. Pasting Values over formulas. If that's not an option, i would consider a CPU upgrade Problem 3 - Data Missing Is this the output for column O? Not sure why that would occur. Could you please send me the updated sheet (where the blank columns/formulas are now filled in for all blocks). I never received it on my email ([email protected]). Many Thanks Caleeco
  5. 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. Slow Running Code

    I'm have returned from the depths of VBA. With (hopefully) some working code! I have now modified it to run on ALL worksheets in the workbook and for all 4 blocks. On my machine.. it managed to process 12,100 lines of data in about 6 seconds. Even i'm pretty astounded in how much quicker Arrays are! I did some self checking of the output data, but am reliant on your superior attention to detail to spot any mistakes. let me know if you see anything wrong. Again. Please ensure the two statements at the top of the module are also copied across! Option Explicit Option Base 1 Sub Superfast_AIO() '//Source: www.ExcelWTF.com '//Purpose: Execute data processing to all sheets within active workbook. ' Focus given to speed for Version 2.0 Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ActiveWorkbook.Worksheets If Not sh.Name = "EURGBP" Then Call DataProcessor(sh.Name, "SH", "S", "J", "FO/RV") Call DataProcessor(sh.Name, "SL", "AA", "N", "FO/RV") Call DataProcessor(sh.Name, "SH", "AI", "K", "FO") Call DataProcessor(sh.Name, "SL", "BP", "O", "FO") End If Next sh Application.ScreenUpdating = True End Sub Function DataProcessor(strWS As String, strSearch As String, strYesNoCol As String, strOutputCol As String, strType As String) '//Source: www.ExcelWTF.com '//Purpose: Processes all lines of data and searches for FO/RV values. Outputs according to defined logic. ' Focus given to speed for Version 2.0 Dim ws As Worksheet Dim arrYesNO() As Variant, arrFind() As Variant, arrOutput() As Variant Dim lr As Long, i As Long, j As Long Dim strTest As String, strOut As String Set ws = Sheets(strWS) lr = ws.Range("B" & Rows.Count).End(xlUp).Row ReDim arrOutput(lr - 3) 'Set the maximum size of the output array arrYesNO = ws.Range(strYesNoCol & "4:" & strYesNoCol & lr) For i = LBound(arrYesNO) To UBound(arrYesNO) If arrYesNO(i, 1) = strSearch Then arrFind = Range(ws.Range(strYesNoCol & i + 3).Offset(, 1), ws.Range(strYesNoCol & i + 3).End(xlToRight)) For j = LBound(arrFind, 2) To UBound(arrFind, 2) If i + j = lr - 1 Then GoTo done strTest = arrFind(1, j) If strType = "FO/RV" Then If strTest = "FO" Or strTest = "RV" Then If IsEmpty(arrOutput(i + j - 1)) Then arrOutput(i + j - 1) = strTest Else strOut = strTest & "/" & arrOutput(i + j - 1) Select Case strOut Case "RV/RV" arrOutput(i + j - 1) = "RV" Case "RV/FO" arrOutput(i + j - 1) = "FO" Case "FO/RV" arrOutput(i + j - 1) = "FO" Case "FO/FO" arrOutput(i + j - 1) = "FO" End Select End If ElseIf arrFind(1, j) = "" Then GoTo out End If Else If arrFind(1, j) = "" Then GoTo out ElseIf IsEmpty(arrOutput(i + j - 1)) And strTest = "FO" Then arrOutput(i + j - 1) = strTest End If End If Next j End If out: Next i done: Dim Destination As Range Set Destination = ws.Range(strOutputCol & "4") Set Destination = Destination.Resize(UBound(arrOutput), 1) Destination.Value = Application.Transpose(arrOutput) End Function Hope that helps Caleeco
  7. Slow Running Code

    Hi OldFella, Sorry for the delayed reply, this time difference is killer! Just got back from work will be doing some more coding this evening. Haha... bigger is better it would seem. That looks promising, I would anticipate the all 4 blocks to run in less than 1 minute (although the last two blocks have many more columns to process so we'll see). I'll post up once I have some tested code. Haha whisky is my poison of choice! Cheers Caleeco
  8. 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!
  9. Slow Running Code

    Not going to lie, that was an intense coding session! The new array method is drafted (looks promising). I tested it on the 'EURGBP_h12Data' sheet for the FIRST SH block only. Please note the first two lines above the sub. Option Explicit & Option Base 1. The second one is VERY important, and the code wont work properly without it so make sure it's at the top of your module. The code below runs in a few seconds for 3500 rows for the first block. let me know if it works for you, and I will incorporate the other 3 blocks and get it to loop all sheets! Option Explicit Option Base 1 Sub All_in_One_v2() '//Source: www.ExcelWTF.com '//Purpose: Execute data processing to all sheets within active workbook. ' Focus given to speed for Version 2.0 Dim ws As Worksheet Dim arrYesNO() As Variant, arrFind() As Variant, arrOutput() As Variant Dim lr As Long, i As Long, j As Long Dim strTest As String, strOut As String Set ws = Sheets("EURGBP_h12Data") lr = ws.Range("B" & Rows.Count).End(xlUp).Row ReDim arrOutput(lr - 3) 'Set the maximum size of the output array '// "SH", "S", "J", "FO/RV" arrYesNO = ws.Range("S4:S" & lr) For i = LBound(arrYesNO) To UBound(arrYesNO) If arrYesNO(i, 1) = "SH" Then arrFind = ws.Range("T" & i + 3, ws.Range("T" & i + 3).End(xlToRight)) For j = LBound(arrFind, 2) To UBound(arrFind, 2) If i + j = lr - 1 Then GoTo done strTest = arrFind(1, j) If strTest = "FO" Or strTest = "RV" Then If IsEmpty(arrOutput(i + j - 1)) Then arrOutput(i + j - 1) = strTest Else strOut = strTest & "/" & arrOutput(i + j - 1) Select Case strOut Case "RV/RV" arrOutput(i + j - 1) = "RV" Case "RV/FO" arrOutput(i + j - 1) = "FO" Case "FO/RV" arrOutput(i + j - 1) = "FO" Case "FO/FO" arrOutput(i + j - 1) = "FO" End Select End If ElseIf arrFind(1, j) = "" Then GoTo out End If Next j End If out: Next i done: Application.ScreenUpdating = False Dim Destination As Range Set Destination = Range("J4") Set Destination = Destination.Resize(UBound(arrOutput), 1) Destination.Value = Application.Transpose(arrOutput) Application.ScreenUpdating = True End Sub Cheers Caleeco
  10. Slow Running Code

    Hi Mate, Didnt see the new sheet in my inbox, do you mind checking it's not stuck in your outbox? Cheers Caleeco
  11. 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.
  12. Slow Running Code

    Hey OldFella, Thanks for the question. I have received your example file, and have some thoughts as to why it may be taking so long: Generally interacting with the Worksheet using VBA will be slow for very large datasets. I am investigating using arrays instead which should be quicker. The loop of 3600 rows occurs 4 times, as the DataProcessor() Function is called 4 times. I will try and modify this to that loop only occurs once. This should cut run time by 75% in all cases. If you could ensure that there are no blanks inbetween SH, SH+1, SH+x values it would speed up the code (see Picture below). I will need to make a code edit to benefit from this. The speed of the sheet will depend on how many times data is moved between columns and rows. So a sheet with 4000 rows, may run quicker than that with 3600 rows provided there are less found values (FO, RV) to move around the sheet. If you could send me an updated sheet with the change above, it would be most helpful. Many Thanks Caleeco
  13. 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
  14. 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.
  15. User Input Pop-up to Update VBA String

    Hi OldFella, Thanks for the question! I like having coding projects to work on! Ah, that would indeed be time-consuming to edit the code every time. I would suggest a slightly different approach, I would get VBA to loop all the sheets in the workbook and then give the macro the sheet name automatically (so you don't need to keep entering them) Please note the following: The new code MUST be in the 'ThisWorkbook' module. Now that you have created the All_in_One() macro, you can delete the other small subroutines I had made. This will run code on ALL the sheets in the workbook, EXCEPT the 'Back-test Template' sheet (case-sensitive). If you need to exclude more sheet names, I can show you how to edit the code. This can be modified to run on all sheets of all open workbooks if needed, let me know I will send you my example file via email Sub All_in_One() '//Source: www.ExcelWTF.com '//Purpose: Execute data processing to all sheets within active workbook. Excluding Template sheet. Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ActiveWorkbook.Worksheets If Not sh.Name = "Back-test Template" Then Call DataProcesser(sh.Name, "SH", "Q", "H", "FO/RV") Call DataProcesser(sh.Name, "SL", "Y", "L", "FO/RV") Call DataProcesser(sh.Name, "SH", "AG", "I", "FO") Call DataProcesser(sh.Name, "SL", "BN", "M", "FO") End If Next sh Application.ScreenUpdating = True 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 On Error GoTo out 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 Set ws = Sheets(wsBT) ws.Activate 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 Exit Function out: MsgBox "Ooops... looks like something went wrong. Please check code/output", vbOKOnly + vbExclamation, "Error" End Function Let me know if that works. If you still would prefer the input box let me know and I'll code something up for you Thanks Caleeco
  16. 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
  17. 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
  18. Getting Rid of the Hidden Content of Blank Cells

    Hi OldFella, Thanks for the link & the values of 'c', it does shed some light on it. It appears as if one of your formulas is returning an error like a #N/A or #DIV0 so the VBA code has trouble evaluating it for length. I was struggling to recreate it on my spreadsheet, but from some reading online, this may help: I have added an IFERROR statement, to bypass trying to evaluate its length with the LEN() function. Sub doit_for_AllWorksheets() '// www.ExcelWTF.com '// Purpose: Clear contents of problem cells Dim ws As Worksheet Dim c As Range With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With For Each ws In Worksheets For Each c In ws.UsedRange If IsError(c.Value2) Then 'Move on Else If Not IsEmpty(c) And Not c.HasFormula And Len(c.Value2) = 0 Then c.ClearContents End If End If Next c Next ws With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With End Sub Let me know if it works, Many Thanks Caleeco
  19. Well that's some excellent news! Apologies for the obvious errors I had left, but great to see you figured out what was wrong and corrected them! Good idea of having them run off a single button click Wow! That's sounds like quite a project. Glad I was able to help shorten the lead time on completion & hopefully remove hours of manual data entry! (it's what VBA is built for!) Haha, thanks for the beer... it's made me thirsty, so going to crack open a real one Enjoy your weekend! Please mark as solved if you're happy with the current solution I'm off to your other thread to see if I can figure out that error you've been getting. Cheers Caleeco
  20. 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
  21. 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???
  22. 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
  23. Getting Rid of the Hidden Content of Blank Cells

    Marked as unsolved! Hmm not sure what's up there. Can you please do the following: Run the code again Run the Debug when you get the error The hover your mouse over the letter 'c' and tell me what pops up in the textbox It will give me a clue as to what the problem is! Many Thanks Caleeco
  24. All done mate! The new blocks raised an interesting problem! The previous blocks (LC, RV, FO FO etc) were all contiguous, where as the new blocks sometimes had blanks in the first column eg SL+1. This caused some problem with the existing code. So I have modified it to search how wide the header rows are (row 1) and use that as the search width. This means a NEW version of the code. I have also incorporated another argument to the function for your latest requirement eg. when you're looking for FO values only! Sorry, for giving you another round of testing to do. Let me know how you get on: 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 In this case... no The code runs on individual blocks, so their placement in the sheet is irrelevant. however, THERE MUST BE AN EMPTY COLUMN BETWEEN THE BLOCKS in row 1 Hope that helps Caleeco
  25. Hi Oldfella, I found the bug in the code, maybe I had one too many beers whilst coding Your requirement was: I obviously misread this and wrote the code below. ElseIf sSeq = "FO" And sOutput = "RV" Then sOutput = "RV" please replace it with the correct output here: ElseIf sSeq = "FO" And sOutput = "RV" Then sOutput = "FO" This should fix the issue you found, please let me know. I'm going to start work on the new blocks you have specified and will have some code for you by tonight. Cheers, Caleeco
  26. 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?
  1. Load more activity
×