Code to insert text if value = true

[Burt_100]

Hi, I am looking code to insert text into a column based on the value in another, I am currently using the following vlookup code =VLOOKUP(N3,$AM$2:$AN$8,2,0) but have been finding it very slow when using a macro for the rest of the workbook, I have attached a copy of the workbook showing the code and what I am trying to achieve, I am also using the formula =INDEX(AB2:AK2,MATCH(REPT("z",255),AB2:AK2)) in column AL to show the last entry if possible could the code incorporate this also?
Thanks in Advance
Johnny

[LJMetzger]

Hi Burt_100,

See the attached file which uses an Excel 'Scripting Dictionary' which can be used as a VBA replacement for VLOOKUP.
See http://www.snb-vba.eu/VBA_Dictionary_en.html

The code updates Columns 'AB' thru 'AH' and Column 'AL'. Columns 'AI' thru 'AK' are not used because you ran out of 'Contact Numbers'.

My speculation is that this code will improve performance very slightly. I think the array formulas in columns 'N', 'P', etc. are probably your Bottleneck.

In the Sheet 'Reminders' Code module:

Please Login or Register  to view this content.

In ordinary Code Module ModReminders:

Please Login or Register  to view this content.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

Lewis