Re: $$Excel-Macros$$ VBA VLookup dates
Dave, Apologies for the late response. Very much appreciate the effort you have put into this - it works perfectly! Thanks again! Andy. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe, reply using remove me as the subject.
RE: $$Excel-Macros$$ VBA VLookup dates
You're welcome. Glad to help. Dave. Date: Tue, 6 Apr 2010 03:47:24 -0700 Subject: Re: $$Excel-Macros$$ VBA VLookup dates From: andyr...@hotmail.co.uk To: excel-macros@googlegroups.com Dave, Apologies for the late response. Very much appreciate the effort you have put into this - it works perfectly! Thanks again! Andy. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe, reply using remove me as the subject. _ Browse profiles for FREE! Meet local singles online. http://clk.atdmt.com/NMN/go/150855801/direct/01/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ VBA VLookup dates
Dave, Appreciate the effort - thanks a lot. I started to think of another way around the problem but that's stumping me as well... Sub Test() Dim lookFor As Range Dim rng As Range Dim col As Integer Dim found As Variant If Range(A2) = Then Range(H2) = Else: Set lookFor = Sheets(Access Data).Range(G2) Set rng = Sheets(Front page).Range(fortnight) col = 3 found = Application.vlookup(lookFor.Value, rng, col, 0) Range(H2).Value = found Range(A2).Offset(1, 0).Select Do While ActiveCell.Value Empty Set lookFor = Sheets(Access Data).ActiveCell.Offset(0, 6) Set rng = Sheets(Front page).Range(fortnight) col = 3 found = Application.vlookup(lookFor.Value, rng, col, 0) ActiveCell.Offset(0, 1).Value = found ActiveCell.Offset(1, -7).Select Loop End If End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject.
RE: $$Excel-Macros$$ VBA VLookup dates
Hi Andy, I think I made 'found' a range, when it wasn't. Sorry. Try: If Not IsError(found) Then Range(H2:H5).Value = found Else: Range(H2:H5).ClearContents End If Regards - Dave. Date: Wed, 24 Mar 2010 03:33:09 -0700 Subject: Re: $$Excel-Macros$$ VBA VLookup dates From: andyr...@hotmail.co.uk To: excel-macros@googlegroups.com Dave, Thanks again for the reply. Excel 2002 doesn't recognise IsErr and changing it to IsError generates the error Method 'Range' of object'_Worksheet' failed It seems that it won't work because found is not a range? Changing the line to If Not IsError(found) Then or any variation doesn't seem to help either. On Mar 24, 12:09 am, Dave Bonallack davebonall...@hotmail.com wrote: Hi, Something like: If Not IsErr(Range(found)) Then Range(H2:H5).Value = found Else: Range(H2:H5).ClearContents End If Regards - Dave. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject. _ Get personal with Windows. Download a free gift for your PC. http://experience.windows.com -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject.
RE: $$Excel-Macros$$ VBA VLookup dates
Hi Andy, I don't think my last mail will work either. I've downloaded your sheet from the link you provided, and I'll give it some thought, but too busy to do much before the weekend. Regards - Dave. Date: Wed, 24 Mar 2010 03:33:09 -0700 Subject: Re: $$Excel-Macros$$ VBA VLookup dates From: andyr...@hotmail.co.uk To: excel-macros@googlegroups.com Dave, Thanks again for the reply. Excel 2002 doesn't recognise IsErr and changing it to IsError generates the error Method 'Range' of object'_Worksheet' failed It seems that it won't work because found is not a range? Changing the line to If Not IsError(found) Then or any variation doesn't seem to help either. On Mar 24, 12:09 am, Dave Bonallack davebonall...@hotmail.com wrote: Hi, Something like: If Not IsErr(Range(found)) Then Range(H2:H5).Value = found Else: Range(H2:H5).ClearContents End If Regards - Dave. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject. _ Get personal with Windows. Download a free gift for your PC. http://experience.windows.com -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject.
Re: $$Excel-Macros$$ VBA VLookup dates
Managed to get a bit further. The Lookup part is working perfectly but I can't figure out how to turn the #N/A entries into blanks... Private Sub Worksheet_Change(ByVal Target As Range) Dim lookFor As Range Dim rng As Range Dim col As Integer Dim found As Variant Set lookFor = Sheets(Access Data).Range(G2:G5) Set rng = Sheets(Front page).Range(fortnight) col = 3 found = Application.vlookup(lookFor.Value, rng, col, 0) Range(H2:H5).Value = found End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject.
Re: $$Excel-Macros$$ VBA VLookup dates
Sorry for the extra messages but I decided to delete most of the data in a workbook copy so that I could upload it... http://excel-macros.googlegroups.com/web/2010%2003%2023%20TEST.xls?hl=engsc=c-8xyhYAAAB4suMG83bGpxYXyXRb7VtTTCBlxQqdFNTZwwfBwm5u_Q Basically I want the code to stop calculating once it hits an error (#N/A) or at least to show blanks instead. If there is a better way to do this I'm all ears as a lot of data is usually entered into these sheets. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject.
RE: $$Excel-Macros$$ VBA VLookup dates
Hi, Something like: If Not IsErr(Range(found)) Then Range(H2:H5).Value = found Else: Range(H2:H5).ClearContents End If Regards - Dave. Date: Tue, 23 Mar 2010 07:21:21 -0700 Subject: Re: $$Excel-Macros$$ VBA VLookup dates From: andyr...@hotmail.co.uk To: excel-macros@googlegroups.com Managed to get a bit further. The Lookup part is working perfectly but I can't figure out how to turn the #N/A entries into blanks... Private Sub Worksheet_Change(ByVal Target As Range) Dim lookFor As Range Dim rng As Range Dim col As Integer Dim found As Variant Set lookFor = Sheets(Access Data).Range(G2:G5) Set rng = Sheets(Front page).Range(fortnight) col = 3 found = Application.vlookup(lookFor.Value, rng, col, 0) Range(H2:H5).Value = found End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject. _ Browse profiles for FREE! Meet local singles online. http://clk.atdmt.com/NMN/go/150855801/direct/01/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject.
Re: $$Excel-Macros$$ VBA VLookup dates
Heya, Thanks for the reply! At the moment the file is a bit too large to attach. The dates I'm trying to match are in column C of the Access Data sheet, which are checked with dates on the Front Page sheet (fortnight range) and a number is looked up from these and placed into column H of the Access Data sheet. Hopefully this is a bit clearer, if not I'll try to trim the workbook a little and upload it. Cheers. On Mar 20, 1:36 am, Dave Bonallack davebonall...@hotmail.com wrote: Hi Andy, Not quite sure what date fields you want to match to what. Is it possible to attach the worbook? Regards - Dave. Hey all, I'm trying to cut down on loading times by converting a load of formulae to code. I'm stuck on this particular formula - =IF(ISERROR(VLOOKUP((C3),fortnight,3,FALSE)),,VLOOKUP((C3),fortnight, 3,FALSE)) The main problem I seem to be having is how to amend the code to properly detect when date fields match. _ Link all your email accounts and social updates with Hotmail. Find out now.http://windowslive.ninemsn.com.au/oneinbox?ocid=T162MSN05A0710G -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject.
$$Excel-Macros$$ VBA VLookup dates
Hey all, I'm trying to cut down on loading times by converting a load of formulae to code. I'm stuck on this particular formula - =IF(ISERROR(VLOOKUP((C3),fortnight,3,FALSE)),,VLOOKUP((C3),fortnight, 3,FALSE)) The main problem I seem to be having is how to amend the code to properly detect when date fields match. This is what I have so far for the VBA - Private Sub Worksheet_Change(ByVal Target As Range) Dim lookFor As Range Dim rng As Range Dim col As Integer Dim found As Variant Set lookFor = Sheets(Access Data).Range(E2) Set rng = Sheets(Front page).Range(fortnight) col = 3 On Error Resume Next found = Application.vlookup(lookFor.Value, rng, col, 0) If IsError(found) Then MsgBox lookFor not found Else: MsgBox The look-up value of lookFor is found in column col End If On Error GoTo 0 End Sub -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject.
RE: $$Excel-Macros$$ VBA VLookup dates
Hi Andy, Not quite sure what date fields you want to match to what. Is it possible to attach the worbook? Regards - Dave. Hey all, I'm trying to cut down on loading times by converting a load of formulae to code. I'm stuck on this particular formula - =IF(ISERROR(VLOOKUP((C3),fortnight,3,FALSE)),,VLOOKUP((C3),fortnight, 3,FALSE)) The main problem I seem to be having is how to amend the code to properly detect when date fields match. _ Link all your email accounts and social updates with Hotmail. Find out now. http://windowslive.ninemsn.com.au/oneinbox?ocid=T162MSN05A0710G -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject.
