NH VBA code to auto refresh the default option in a drop down

[mdiangelo]

Good Afternoon Excel Guru’s,

So my latest issue is this: I would like a data validation list to auto refresh the first option in a drop down using a VBA code.
So please allow me to back fill you in on what’s happening prior to the situation above. This program has many end users that extract four reports from a website and export into CSV files. My program then imports the data into a table that’s used by 17 different pivot tables, from there the tab “MainTab” complies all the new information a displays it vividly for the end user to see.
<o></o>
The Layout:
Column A 342:370 contains Training Topics that are fixed and used by columns C:ALN
Column B: is the Date when the training was conducted.This is a Data validation list of three values. The formula for mode of all the dates within that row,“N/A”, “ “
Columns C:ALN Indexmatch the topic in A column with the person’s name in row 342 from a pivot table on a different tab "CNAF" to give the date. When no date is available the formula then compares the date they checked into the work center and the date the training was conducted to display either “NotChkdIn” or “Missed_it”. If "N/A" was selected in the B column then the entire row will display "N/A".
<o></o>
My issue is this:
Column B always saves the last date that was entered. I would like it to auto-refresh the first option which is a formula for mode. The Data Validation drop downs use tiles ALU343:370 for mode ALV343:370 for “N/A”, and ALW343:370 for the blank space (this allows the user to input any date they would like) So an example is row 343 will use the validation options ALU343-ALV343-ALW343.

P.S. Obivously Im pretty new at the forum, so please bear with me. And why would my ability to post an attchment be turned off?
Excel 2007 Windows XP

 

[mdiangelo]

So I've mad some head way with the above problem with this:

Set c = Range("B79")
c.Value = Range(Mid(c.Validation.Formula1, 2)).Cells(1, 1).Value


but i have to copy and paste for each of the drops down I have. I did try this:

Set c = Range("B79:B106")
c.Value = Range(Mid(c.Validation.Formula1, 2)).Cells(1, 1).Value

but it dosen't seem to allow the time needed for the formula for the first option to calculate.

and lastly I'd like to add an IF statement. I've come up with this but it fails to work correctly:

Sub Default_First_Option()


'if cell value is a date then refresh to the data validation's first option.
'if cell value is N/A then leave it alone go to the next cell. N/A is the second option in the drop down
'The third option is a blank to the end user can enter any date they would like.

On Error GoTo 1
1: MsgBox "FAIL"

Sheets("Sheet1").Select
For Each cell In Range("B79:B106")
Select Case cell.Value
Case "*-???-??" 'this would be any date value. ex 29AUG13
cell.Range(Mid(c.Validation.Formula1, 2)).Cells(1, 1).Value
Case "N/A"
c.Value = Range(Mid(c.Validation.Formula1, 2)).Cells(1, 2).Value
End Select
Next cell


End Sub