How can I reference the value of a dynamically created variable?

[rbeaslin]

I have created a bit of code to eventually build a sheet for csv import into an HR system. The code is working as expected to this point with the exception of the idea I'm trying.

Where I'm getting stuck is that with this line, I'm getting the "NewRef" variable input into my sheet instead of the format of the variable value as shown, i.e., "adate1" instead of the value of adate1 which would then be formatted.

I'm missing something fundamental here, so please let me know what.
Thanks in advance for my oversight being pointed out!

Option Explicit
Sub UpdateDates()

Dim Adate1 As Single
Dim Adate2 As Single
Dim Adate3 As Single
Dim Adate4 As Single
Dim Adate5 As Single
Dim Adate6 As Single
Dim Adate7 As Single
Dim Adate8 As Single
Dim Adate9 As Single
Dim Path As String
Dim fname As String
Dim Sheet As String
Dim HireDate As String
Dim PNum, NewRef As String
Dim TRows, Item, DRow, X As Integer
Dim wb1, wb2 As Excel.Workbook
Set wb1 = ThisWorkbook
Dim trange As Range
Dim MyObj As FileDialog
Item = 2
DRow = 1

Set MyObj = Application.FileDialog(msoFileDialogOpen)

With MyObj
    .AllowMultiSelect = False
    .Title = "Select Anniversary File"
    .InitialFileName = "K:\My Drive\Downloads\Eann.xlsx"
    If .Show = 0 Then
        Exit Sub
    End If
    Path = .SelectedItems(1)
    fname = Dir(Path)
    Range("a1") = fname
End With

Set wb2 = Workbooks.Open(fname)
Sheet = wb2.ActiveSheet.Name
wb2.Activate

Range("a1").CurrentRegion.Select
TRows = Selection.Rows.Count - 1

Range("b" & Item).Select

Do While Item <= TRows
HireDate = Cells(Item, 5)

    If Cells(Item, 9) = "00/00/0000" Then
        PNum = Selection.Cells(Item, 1)
        Adate1 = DateValue(HireDate) + 365.25
        Adate2 = DateValue(HireDate) + (3 * 365.25)
        Adate3 = DateValue(HireDate) + (5 * 365.25)
        Adate4 = DateValue(HireDate) + (10 * 365.25)
        Adate5 = DateValue(HireDate) + (15 * 365.25)
        Adate6 = DateValue(HireDate) + (20 * 365.25)
        Adate7 = DateValue(HireDate) + (25 * 365.25)
        Adate8 = DateValue(HireDate) + (30 * 365.25)
        Adate9 = DateValue(HireDate) + (35 * 365.25)
   
    For X = 1 To 9
        With wb1.Worksheets("data")
            .Cells(DRow, 1) = PNum
            If (X + 3) < 10 Then
                .Cells(DRow, 2) = "miscdate0" & (X + 3)
            Else
                .Cells(DRow, 2) = "miscdate" & (X + 3)
            End If
            NewRef = "adate" & CStr(X)
            .Cells(DRow, 4) = Format(NewRef, "yyyymmdd")
        End With
        DRow = DRow + 1
    Next X
    'DRow = DRow + 9
   End If
    
Item = Item + 1
Loop

End Sub

 

[Fluff]

You cannot concatenate variables like. I would suggest putting the dates into an array rather than individual variables & then you can reference the array.

 

[rbeaslin]

Well, it was a good idea, i thought. I'll do the array. Thank you, Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.