Recent content by Emeng

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