Search results

Took your advice and all OK. Thanks.

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

Hi Marc I see your point about not needing names for fixed ranges but when I look at this in 2 or 5 years’ time it will be easier to understand the formulae. Others will use this tool and may also help them if they should need to change it for their particular circumstance. Thanks for your...

Hi Chihiro Thanks for your help, your code works perfectly. I have done some quick study on InStr, RefersTo & evaluate… there is so much more to learn. Thanks & regards Mark

Hi Marc See attached. Thanks for your interest. Regards Mark

No luck Hui This code causes a Range of Object_Global failure Regards Mark

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

Thanks Narayan You are a champion! I'll study this code to try to understand how it does what it does. Many thanks Mark

Hi Debaser No luck I'm afraid... the code now breaks at myVLookupResult = WorksheetFunction.VLookup(myLookupValue, myNamedRange, 3, False) with a 'Unable to Get the Vlookup property of the WorksheetFunction class' message. Regards Mark

Hi Narayan I'm looking for numbers between 40000000 & 49999999 located in the ActiveWorkbook.Sheets(2).Columns(1). I am now wondering whether it's variable should be Long rather than Range. Regards Mark

Hi Ram, thanks for your reply. Removing 'Set' causes a 424 run-time error: Object required on the line Set myNamedRange = book1.Sheets(2).Range("Consumables").Value Rergards Mark

Hi Ram, thanks for your reply. Removing 'Set' causes a 424 run-time error: Object required. Mark

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

Hi Narayan Thanks for your help, it seems so obvious now that you have pointed it out. Regards Mark

Hi Narayan I see that by leaving out the period in the If statement I am referring to the same range on a different sheet. I changed to using the longhand version as follows Sub CopyTasks2() Dim lastrow As Long Dim lr As Long lastrow = Sheets("Mechanical").Range("A" &...

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

Loving your work Bosco. I had the "AllDates,,DateCol" happening separately in one of my previous attempts but the double commas look like an error. So much to learn! Thanks & regards Mark

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

Works like a bought one! Four little letters... Thanks Hui

The macro file must reside in a folder called Planning Tools on the desktop. Regards Mark

Hi again This is the whole code I've appended to the original macro, all of which executes as expected, except for this issue. 'EXTRAS 'Name sheet, apply colour according to WrkCtr, set Print Parameters, Option to Print a copy & close ThisWorkbook without saving. 'Name sheet...

Hi Hui I followed your directions and all went well; the page printed as it would using the first code example. It seems the code is not executing. Any ideas why? Regards Mark

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

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

That's interesting... I've not used INDIRECT before. Time to go have a play with it! Thanks & regards Mark