Recent content by julesl

Hi Skip Thank you for your reply. Having tried your formula I realised my original description of what I'm trying to do is too ridgid. I think it should say:- Look up A2(id) from Worksheet 2 in Worksheet 1 (A2:N691) and bring back the contents of cell I* (which is a date of birth) if K* =...

Hi I have a spreadsheet containing 2 worksheets and I need to copy data into the second worksheet based on certain criteria. The formula I am trying to write is as follows:- Look up A2(id) from Worksheet 2 in Worksheet 1 (A2:N691) and bring back the contents of cell I2 if K2 = "Main" (in...

Hi I have an extract which is run each week day evening and provided to me the following morning. On Tuesday to Friday I need to extract all the policies that changed to a status of inforce on the day the extract was run e.g on Tuesday morning I need to extract all the policies that went to a...

Great, it works perfectly. Thanks very much for your help Jules

Hi I have a spreadsheet which contains 13 columns, one of which is a start date (column D). The spreadsheet will contain rows with varying start dates and I need to remove all dates that are before yesterday's date except on Monday's when data for the previous Friday, Saturday and Sunday should...

Hi I've created the macro below using VBA in excel and I'm trying to get the formula to copy to the last row of data within the spreadsheet rather than to a specific point. Sub CopyForm() ' Range("N1").Select ActiveCell.FormulaR1C1 = "RR Termination Date" Range("N2").Select...

Hi I've just tried re-running the macro and I still get a run time error 1004 but this time the message was "Autofilter method of range class failed." Sub delenonmt2() With ActiveSheet.UsedRange lRow = Range(Cells(.Row, 1), Cells(.Rows.Count, 1)).Row End With Range("A1:M" &...

Hi Thanks for the code. I've tried running it but I get a run time error 1004 saying "Microsoft Excel cannot insert or delete a column in a list while the Autofilter command is turned on." This is the code debug highlights:- "Rows("2:" & lRow).Delete Shift:=xlUp" Any help please? Thanks Jules

Hi Thank you for your reply. Sorry to sound completely stupid but which bit of my code do I need to replace with your suggestion. When I said I had next to no knowledge of VBA I wasn't joking!!!! Thanks for your help Jules

Hi I'm trying to automatically delete rows in excel which meet certain criteria. I have a spreadsheet with 13 columns and column F is an indicator which contains either "mortgage" or blank. I have created the following code using Thread 707-687994:- Sub deletenonmort() lRow = Cells(65536...

Thanks for your reply. I've tried both of the queries and get the following problems:- (Query 1) it doesn't recognise distinct docdef and asks for a parameter value to be entered (Query 2) It runs the query but then comes up with a message 'At most one record can be returned by this sub...

Thank you for all your replies. I've tried the first two suggestions as I am using access 2000 but I cant get either to work. I've amended the code as follows and have attached the results:- (1) select CATREF, count(*) as catrefcount, count(distinct DOCDEF) as distinctdocdef from legacy...

Hi I've built an access database and I've been asked to build the query below, but I'm not sure how to, as my access and SQL skills are limited. I have the following list of items and I need to list all the non duplicates from column 1 (REF), then count the duplicates from column 1 (REF) and...

Hi I have next to no knowledge of VB and have volunteered to create a template containing several input forms. I have designed the forms in VBA (which include check boxes and combo box) but I have no idea how I’m going to get the data from the forms into the Word document. For the form...