• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. E

    SortOn:=SortOnCellColor - Named argument not found

    Hi all I have copied & adapted the text of the macro recorder to sort by colour but receive the above error. The code blows out on SortOn which is the last line of the procedure... so far. Range("MyRange").Sort Key1:=Range("D" & newRow), SortOn:=xlSortOnCellColor, Order:=xlAscending...
  2. E

    Check if a Named Range in Array is blank

    Hi all I have created seven Named Ranges for use in formulae in following procedures and want to check that none of them is blank. I have tried variations of the following but have bugged out on the If Application.CountBlank line each time. With Sheets("Parameters") Range("G7").Name...
  3. E

    VBA VLookup, Refer to Named Range Compile Error

    Hi all I’m having trouble with the syntax to refer to a named range in ThisWorkbook when attempting a VLOOKUP from another (newly created, ActiveWorkbook) workbook. Specifically I receive a Compile Error: object required message on the Set myVLookupResult line. Any help is much appreciated...
  4. E

    Autofilter Runtime Error

    Hi guys My code throws 1004 runtime error "No cells found" when copying autofilter results. The line immediately before the error as follows, If Range("J1:K" & lastrow).SpecialCells(xlCellTypeVisible).Count > 1 Then There is no data in this instance, only a header row & I'm puzzled why the...
  5. E

    Index Match with multiple criteria

    Hi all I’m trying to Index/Match multiple criteria to find a variable date in a variable column. The variables & lookup ranges are defined in named ranges. My formula is {=INDEX(AllDates,MATCH(1,(Vehicles=TARGET)*(AllDates=NxtSvc),1),DateCol)} but throws #N/A, as do the simpler...
  6. E

    Same PageSetup & Print code gives diffferent results

    Hi all I have a macro which prints the active sheet in A3 format. Sub PrintPlan () ' Setup page to print 17 columns 1 page wide A3 portrait ' ' Keyboard Shortcut: Ctrl+Shift+P Dim LR As Long Application.ScreenUpdating = 0 Application.PrintCommunication = False...
  7. E

    Find next event in series

    Hi all I would like to find a way to determine the next time an event occurs. For example, if today is 12/11/2016 and using an initial start date of 13/11/2015 of an event that occurs every 6 weeks, to return 23/11/2016. I’m trying to avoid a spreadsheet full of dates but will if it’s the...
  8. E

    Looking for a more elegant solution

    Hi all I have created a macro to retrieve data from selected sheets to a summary page in the same workbook. The code is fine but took some time to put together. I'm wondering if is there is a simpler solution. The patterns are easy to follow. Thanks for your help Mark Sub SetFormulas() Const...
  9. E

    VBA.Random characters added to Path

    Hi all I have some code I wish to modify for use on a thumb drive to allow for Drive variation depending upon a particular machine’s configuration. For example on one machine it may be E:\etc\etc, on another O:\etc\etc. The code has been working fine when the drive is specified on my home...
  10. E

    Index/Match & Dates, partial failure

    Hi all My report spreadsheet has developed a problem & now has trouble reading some dates, causing a #N/A error. The formula had worked as expected up to this point. Now that I am looking at a date range which includes October it is behaving unusually. Put simply, it worked last week, but not...
  11. E

    Code Runs Twice

    Sub ShowCapacity() Dim ws As Worksheet Dim lastrow As Long Dim FX3 As String FX3 = "=IF(ISNUMBER(SEARCH(""Total"",$B2)),C2,"""")" Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each ws In...
  12. E

    Apply Conditional Formatting to Subtotal

    Hi all I'm trying to apply conditional formatting to a subtotal if it's value is greater than another cell's which is identified using Index/Match. I'm working thru the steps methodically, and have reached the point where I can compare one cell with another. I have tried 5 different methods but...
  13. E

    Conditional Formatting Time of Day

    Hi all With my spreadsheet, and using the Conditional Formatting tool, I am trying to format times before 7AM & after 7PM to identify them more easily. I tested my formula in a cell on the sheet to prove it works… “=IF(OR(TIMEVALUE(B2)<0.291665,TIMEVALUE(B2)>0.791667),1,2)”, and it does...
  14. E

    Insert Rows & Fill a Range of Cells

    Hi all I wish to colour a range of cells in a newly inserted line. I have no trouble inserting the line but my code for identifying the cells is not compatible. The code trips on the ".Interior.Color" line. .Rows(lRow & myRange).Interior.Color = 9868950 Sub InsertRows_SeparateCranes()...
  15. E

    Worksheets array syntax error

    Hi all The following code has a problem which I am unable to identify. Sub IdentifyClashes() Dim ws As Worksheet Dim FX2 As String Dim FX3 As String Dim lastrow As Long FX2 = "=IF(AND(C2=C3,A2+J2>A3),""REVIEW"","""")" FX3 = "=IF(O2=P2,P2,(P2 & O2))"...
  16. E

    Open another workbook & copy worksheet using cell value as reference

    Hi all I would like to open another workbook & retrieve a worksheet equal to a cell value using VBA. I have two codes which each do part of the job but I’m having trouble stitching them together. The following code opens the workbook & retrieves the sheet but requires the specific file name...
  17. E

    Hyperlink to TODAY() not working as expected

    Hi all I have a hyperlink which finds today's date in a column of dates that has worked normally till now. =HYPERLINK("#a"&MATCH(TODAY(),A2:A1048576,0),"GO TO TODAY") Today however, it is finding yesterday! I'm working from a work computer which is part of a worldwide network, so I expect...
  18. E

    How to refer to other workbooks Vba

    Hi I am creating a macro enabled workbook with an active x control which is to rearrange a separate spreadsheet downloaded from SAP. Part of the process involves opening a third workbook and importing a sheet named equal to the value of a cell in the second workbook. I am wondering how to refer...
  19. E

    Insert row at data change on multiple sheets vba 2007

    Hi all In my workbook I am trying to add a row at each data change, in the same column, on three different sheets using: Sub InsertRowAtChangeInValue() Dim lRow As Long Dim ws As Worksheet For Each ws In ActiveWorkbook.Sheets(Array("Mechanical", "Contractors", "Cranes &...
  20. E

    Add coloured row at data change

    Hi all I wish to add a row when data changes in column 13 and colour it red (255), see code below. Any/all hints & help appreciated. Regards Mark Sub SubtotalAndFormat() &#39; SubtotalAndFormat Macro Dim aCell As Range, bCell As Range Dim ExitLoop As Boolean Dim iRow As Integer...
  21. E

    Sorting by sequence , not value

    Hi all Is there a way to sort by the first digit? E.G. 10,100,1000,1010,1020,110,20,200,210,220 etc. Regards Mark
  22. E

    Hyperlink to future week depending upon rule.

    Hi all I have a list of dates into the future in column A and a list of corresponding week numbers in column B. I am able to click and go directly to today’s date using =HYPERLINK("#a"&MATCH(TODAY(),A:A,0),"GO TO TODAY"). I would like to be able to go to the next 'Planning Week' depending...
  23. E

    Hyperlink to TODAY()

    HI I would like to create a hyperlink to take me to today’s date in a list of dates in the same worksheet. Any help much appreciated. Regards Mark
  24. E

    Convert Unusual Date Format

    Hi I'm trying to convert date format mmm-dd (no year) to any format excel will recognise. I have looked thru the web - many variations explored...but not this one. Kind regards and thanks for reading, Mark
Back
Top