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!

Caleeco

Administrators
  • Content count

    236
  • Joined

  • Last visited

  • Days Won

    37

Caleeco last won the day on July 11

Caleeco had the most liked content!

Community Reputation

18 Good

About Caleeco

  • Rank
    Veteran Lvl.1
  1. Obtaining Data from CSV Files

    haha! I can neither confirm or deny my involvement in such things! Thanks for the new problem! You can access closed .xlsx files, but this tends to be very slow - I've never tried it on CSV files but dont imagine it's possible. I have some thoughts as to how to go about this....there are several options: Query 1 Open each CSV file in a set folder (will keep opening until there are none left), extract the data you need, close the CSV, move onto the next Setup a data connection to each CSV, so they are imported upon workbook open. Then VLOOKUP should work as you expect on the imported data It would be useful to see the format of the data in the CSV files, is it consistent? are the columns delimited by a special character? are you able to email me a sample file? Query 2 You can use the Format Cells command: Highlight Cells > CTRL + 1 On the 'Number' Tab, Choose 'Date' and pick a format you like However, I presume this is imported data, that excel sees as TEXT instead of a DATE. In which case you need a formula to convert it. So Cell C2 will dispay the 'True' date and can be manipulated as expected. The formula for reference is below: =LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,4) Haha, not to worry. I only watch the World Cup for the entertainment.. not that invested into the sport as a whole! Kind Regards Caleeco
  2. Recieved! Thanks for the source files, I will get started on this now.. hopefully I can crack it this evening and report back Caleeco
  3. Hi Sapron, Thanks for the question! I'd be happy to take a look Yes, It will make it easier if i could have the original file. Please email your file to me at [email protected] Kind Regards Caleeco
  4. Create general password protected pdf file

    Ah ok, I thought that may have been the case. Can you not instead just send a copy of the Excel sheet which is password protected? You could duplicate the sheet, paste everything as values and then set a password & email it to the recipient. Let me know if this solution would suffice? Caleeco
  5. Create general password protected pdf file

    Hi Ron, Thanks for the question. Your code looks good so far! In terms of setting a PDF password, you need to have either the Full Adoble Suite (not just the reader) or be able to install some third party PDF creators. I have seen this used a few times: http://www.pdfforge.org/pdfcreator You would setup a default password using the software and then print your file to the PDF creator. More information available here: https://www.exceltrainingvideos.com/tag/how-to-create-password-protected-pdf-file-with-vba/ Let me know if this helps. I can write some modified code for you once you confirm you are able to install software (not sure if you're doing this on a restricted network or not) Caleeco
  6. Changing Chart Y-Axis Maximum with VBA

    Hey Kirabo, I thought that may have been the case! It's always a good idea to rename your charts however, as this makes things easier when modifying them via VBA. You can make use of the CEILING function to round up to the nearest 10, and I have modified the code slightly to just look at the entire columns of B:C. Sub ChangeAxisScales_1Line() '// Source: www.ExcelWTF.com '// Purpose: Set Max Y-Axis using MAX of 2 x datasets Dim Max1 As Long, Max2 As Long Dim ws As Worksheet Set ws = Sheets("Utilization") With ws Max1 = WorksheetFunction.Max(.Range("B:B")) Max2 = WorksheetFunction.Max(.Range("C:C")) If Max1 > Max2 Then ' Change the chart names below! .ChartObjects("Chart1").Chart.Axes(xlValue).MaximumScale = WorksheetFunction.Ceiling(Max1, 10) .ChartObjects("Chart2").Chart.Axes(xlValue).MaximumScale = WorksheetFunction.Ceiling(Max1, 10) Else .ChartObjects("Chart1").Chart.Axes(xlValue).MaximumScale = WorksheetFunction.Ceiling(Max2, 10) .ChartObjects("Chart2").Chart.Axes(xlValue).MaximumScale = WorksheetFunction.Ceiling(Max2, 10) End If End With End Sub Give it a try and let me know how you get on Thanks Caleeco
  7. Changing Chart Y-Axis Maximum with VBA

    Hey Kirabo, If you are using Excel 2013 or greater, you can just click on the chart and then the Chart Name will be displayed in the Top Left of the screen (Next to the formula bar). You can then edit the code below to suit, in my example i have called them 'Chart1' and 'Chart2'. Sub ChangeAxisScales_1Line() '// Source: www.ExcelWTF.com '// Purpose: Set Max Y-Axis using MAX of 2 x datasets Dim Max1 As Long, Max2 As Long Dim ws As Worksheet Set ws = Sheets("Utilization") With ws Max1 = WorksheetFunction.Max(.Range("B2:B8")) Max2 = WorksheetFunction.Max(.Range("C2:C15")) If Max1 > Max2 Then ' Change the chart names below! .ChartObjects("Chart1").Chart.Axes(xlValue).MaximumScale = Max1 .ChartObjects("Chart2").Chart.Axes(xlValue).MaximumScale = Max1 Else .ChartObjects("Chart1").Chart.Axes(xlValue).MaximumScale = Max2 .ChartObjects("Chart2").Chart.Axes(xlValue).MaximumScale = Max2 End If End With End Sub Let me know how you get on Thanks Caleeco
  8. Changing Chart Y-Axis Maximum with VBA

    Hi Kirabo, As far as I know, this is not possible using the Excel interface. I believe Microsoft are looking to introduce equation controlled axis' in a future release of Excel, but for now we have VBA Can you let me know the following: Your sheet name The names of the 2 charts and where the data for each charts if (Cell references eg. C1:C20) Cheers Caleeco
  9. I've Messed Up =LOOKUP

    Awesome, thanks for letting me know that it worked! Haha, no problem.. don't mind doing quick fire questions too, helps me sharpen my skill set All the best Caleeco Marked As Solved!
  10. I've Messed Up =LOOKUP

    Hey OldFella, Good to see you, hope yo've been keeping well! Yes, I haven't really been promoting the forum in recent months, my 'real' job is taking up most of my time these days. Always happy to answer Excel questions though, so thanks for posting. Without seeing your sheet, this is a bit of a stab in the dark, but we could make use of Mathmatical operators! Try using this formula instead: =LOOKUP(2,1/(S:S<>""),S:S) Let me know how you get on Cheers Caleeco
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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
×