No, it's not a common question, it's fairly unusual to do this sort of
thing.

Your formula  is a simple  string. You can shape it any way you want to. For
this, you're looking at simple string manipulation using simple VBA.

Yesterday's date is found by subtracting one  from Date(). If you need it in
a specific format, use the Format() function.

The actual row/column name of the active cell  is retrieved by
ActiveCell.Address.

Sub PasteRMRegFunc()
mydate = Format(Month(Date - 1), "00") & Format(Day(Date - 1), "00")
Selection.Formula = "=VLOOKUP(D" & ActiveCell.Row & ",RMRegArray" &
mydate & ",8,FALSE)"
End Sub

These are absolute gems. I greatly appreciate your very fast help. I've already
begun to
change some of my macros  at work today. Almost thought I'd lost my file today
due to a
power outage. The IT guy told me I'd have to start from scratch, which meant I'd
lose ALL
my macros and named  ranges. Oh my! it was almost a melt down moment since I'm
the only
person in my office trying to use VB to make most of my Excel reporting more
efficient. Even
very simple  macros makes some tedious tasks tolerable.

Here's a quick macro which, on a new sheet, will list all the ranged names in
the active workbook  (you can do this just as easily without VBA by selecting
Insert >> Name >> Paste >> Paste List).

Sub ListNames()
'On a new sheet, list all range names in active workbook.
Sheets.Add After:=Sheets(Sheets.Count)
Range("A1").Select
Selection.ListNames
End Sub

Would this work?

Dim NewRow
Dim NewDate
NewRow = ActiveCell.Row
NewDate = Now()
NewDate = Format(NewDate, "MMdd")
Selection.Formula = "=VLOOKUP(D" & NewRow & ",RMRegArray" & NewDate &
",8,FALSE)"

The code I sent previously did not include the logic for yesterday's


Dim NewRow
Dim NewDate

NewRow = ActiveCell.Row
NewDate = Now()

Select Case Weekday(NewDate)
'Sunday = 1, Saturday = 7
'Monday looking for Friday
Case 2
NewDate = Now() - 3
'otherwise just yesterdays date
Case Else
NewDate = Now() - 1
End Select

NewDate = Format(NewDate, "MMdd")
Selection.Formula = "=VLOOKUP(D" & NewRow & ",RMRegArray" & NewDate
& ",8,FALSE)"

That's interesting. I can see ways to modify that for Mondays - I have to
do the
worksheet for Friday, Saturday and Sunday. That was my initial incentive for
making as
many macros  as possible. The reporting is difficult enough to get through daily
as there are
dozens of steps that used to take me hours. But I always dreaded having to
repeat the task
three times on Mondays just because data is building up while I'm having a much
needed
weekend off. When I get an extended weekend ... well, I don't like to think
about it.