Excel VBA 2016 32bit - Vlookup Date after date typed in and return value from named range

**theshybutterfly** · 12-14-2017, 04:43 AM

Thank you for looking at my question.

The object is of the code is:
When the user types in the date in column A of the "WS_ProductConsumable" sheet, the adjacent cell in column B is automatically populated with the Vlookup value from column 4 of the named range "CalendarWeekend_Hols", and if the value equals "Weekend" or "P/Hol", the row (from column A to H) is filled with conditional formatting of red cell fill with white text, as per sample of cell B3 in sheet "wsCalendarWithHolidays", which has been vba coded for that cell.

VBA Code:

Please Login or Register  to view this content.

The cells which are not a Weekend or P/Hol are to be left populated with the data the user entered or 0 if no data in cell (cells from C:H which appear to be blank have been conditionally formatted to white fill with white text, to appear blank for the cells that contain 0s).

The workbook has 2 worksheets with the worksheet codenames:

WS_ProductConsumables (which is the data entry sheet)
wsCalendarWithHolidays (which is a customised calendar in which a named range has been created for the columns A:D using offset).
Name range name: "CalendarWeekend_Hols"

I would really appreciate any help.

**dflak** · 12-14-2017, 01:50 PM

Skip the VBA. Use Excel Tables instead.

Here is what you need to get started with Excel tables: http://www.utteraccess.com/wiki/Tables_in_Excel

Two of the advantages of tables are:
- They know how big they are, so any formulas that reference them or pivot tables build from them will always reference the correct number of rows.
- When you add a row at the bottom (such as entering a date in the next available row), the row becomes a part of the table and all formulas, data validation, formatting (including conditional formatting) is automatically copied down.

This is the conditional formatting I used: =OR($B3="Weekend",$B3="P/Hol") and I applied it to =$A$3:$I$255 which is the current range of the table. As you add data to the table, this range will grow.

**theshybutterfly** · 12-14-2017, 05:09 PM

Thank you Dflak for your quick response

I do appreciate your suggest/resolution.
I am familiar with the benefits of using a 'table' and had considered this, however, I am trying to avoid slowing down the workbook with 'in cell' formulas and the conditional formatting and thought that by using VBA to handle this it would not affect the responsiveness of the spreadsheet/workbook, but if it is too difficult to do in VBA, then I have no choice but to use the table & 'in-cell' formulas, and then having to periodically remember to go in and copy & paste values only.

**dflak** · 12-14-2017, 05:31 PM

Actually, anything developed in VBA will probably be slower than using "straight" Excel. I have a love/hate relationship with Microsoft, but their engineers have optimized their code and they have access to subfunctions that us mere mortals don't have. However, I never developed a comprehensive set of benchmarks to know what the differences in speed are.

About the only place where I think VBA can hold its own against a native function might be with certain array formulas.

It's a personal preference of mine, but I try to get basic Excel to do as much of the "heavy lifting" as possible (quicker development at lest and potentially less buggy) and then like Dr. Frankenstein, I bolt and sew the pieces together with VBA.

**theshybutterfly** · 12-14-2017, 10:09 PM

I'm wondering whether to leave this 'open' for a short while, in case someone does come up with a VBA solution, which I could also amend to suit another project that is in the works. Would you mind if I left it open for a week, and if there is no bites of the cheery, I'll close it off

?
Thank you again - I hope you have a wonderful weekend and Christmas

Cheers,
TheShyButterfly

**dflak** · 12-15-2017, 12:28 PM

It's up to the moderators to decide that but I doubt they would care.

Anyway, here is a VBA version.

**theshybutterfly** · 12-27-2017, 02:34 AM

Thank you DFlak

Apologies for the late response.
Works a charm

Happy New Year to you and your family.
Cheers,
TheShyButterfly

Excel VBA 2016 32bit - Vlookup Date after date typed in and return value from named range

LinkBack

Thread Tools

Rate This Thread

Display

Excel VBA 2016 32bit - Vlookup Date after date typed in and return value from named range

Re: Excel VBA 2016 32bit - Vlookup Date after date typed in and return value from named ra

Re: Excel VBA 2016 32bit - Vlookup Date after date typed in and return value from named ra

Re: Excel VBA 2016 32bit - Vlookup Date after date typed in and return value from named ra

Re: Excel VBA 2016 32bit - Vlookup Date after date typed in and return value from named ra

Re: Excel VBA 2016 32bit - Vlookup Date after date typed in and return value from named ra

Re: Excel VBA 2016 32bit - Vlookup Date after date typed in and return value from named ra

Thread Information

Users Browsing this Thread

Similar Threads

04-12-2016 date in excel changes to April 12th 2016 when in fact it should be Dec 4th 2016

[SOLVED] vlookup between 2 worksheets, return yes/no if date is between date range

Excel 64bit 2016 Database Connection 32bit...what to do?

Excel 2013 - if date is found between date range return value

[SOLVED] VLOOKUP with the ability to select named range based on a date in another cell

[SOLVED] Vlookup date range to return month

Search named range for multiple matches to critera and return date & values

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions