VBA: How to dynamically use Excel Formulas in the code?

[SeekerExcel11]

So I am creating a Year Month column using VBA that is based on another column ("Time") that has a list of dates.

The challenge is that I want to make the VBA code dynamic. This means that I want the code to search for the title of the column ("Time") and perform the following Excel function in the new "Year Month" column I am asking VBA to do:

=year("Time" column)&"-"&month("Time" column).

Currently struggling the most with this part.

I want VBA to do that for each row of the "Time" column (I want the # of rows to be dynamic as well).

Thanks!

 

[par60056]

you could use "match" to get the column number and then use that column to reverence cells.

Something like:

Dim tCol As Integer
tCol = WorksheetFunction.Match("Time", Range(Cells(1, 1), Cells(1, 20)), 0)


Range(Cells(2, tCol), Cells(1, tCol).End(xlDown)).Offset(0, 5).Formula = "=year(" & Replace(Cells(2, tCol).Address, "$", "") & ")&""-""&month(" & Replace(Cells(2, tCol).Address, "$", "") & ")"

 

[SeekerExcel11]

Hmm, the code doesn't seem to work. I do not see the formula in the cell after running the code.

 

[ParamRay]

.
.

Assumptions:
(1) Column headings are in row 1;
(2) one column has heading "time" and contains date/time entries;
(3) another column has heading "year month"; and
(4) this worksheet is the active sheet.

Option Explicit

Sub InsertFormulas()

    Dim src_col As Integer
    Dim dst_col As Integer
    Dim row_num As Long
    Dim lst_row As Long
    
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    
    src_col = GetColumn(ActiveSheet, "Time")
    dst_col = GetColumn(ActiveSheet, "Year Month")
    
    If src_col = 0 Or dst_col = 0 Then Exit Sub
    
    With ActiveSheet
        If IsEmpty(.Cells(.Rows.Count, src_col)) Then
            lst_row = .Cells(.Rows.Count, src_col).End(xlUp).Row
        Else
            lst_row = .Rows.Count
        End If
    End With
    
    For row_num = 2 To lst_row
        Cells(row_num, dst_col).Formula = "=" & _
            ThisWorkbook.Name & "!YEARANDMONTH(" & _
            Cells(row_num, src_col).Address(False, False) & ")"
    Next row_num

End Sub

Private Function GetColumn(wkst As Worksheet, strg As String) As Integer

    Dim rang As Range
    Dim cell As Range
    
    With wkst
        On Error Resume Next
        Set rang = Intersect(.UsedRange, .Rows(1))
        On Error GoTo 0
    End With
    
    If Not rang Is Nothing Then
        For Each cell In rang
            If LCase(cell.Value) = LCase(strg) Then
                GetColumn = cell.Column
                Exit Function
            End If
        Next cell
    End If
    
End Function

Function YEARANDMONTH(serial_number As Date) As String
    
    YEARANDMONTH = Format(serial_number, "yyyy-mm")

End Function

 

[SeekerExcel11]

Wait I see it, let me rearrange the code...

 

[par60056]

I would need more information about your sheet to know what is wrong.

Basically, the first line looks on row 1 for the head that says "Time" it is going to look in columns A-T. If your "Time" column has a different header or is past column T you will need to modify that line.

You did not say where you wanted the formula. I picked an arbitrary column 5 to the right of where it finds "Time". This is represented in "Offset(0, 5)" You need to set that to where you want the results.

 

[par60056]

Thanks gpeacock. Didn't know there were parameters on address. that allows me to shorten the line.

Range(Cells(2, tCol), Cells(1, tCol).End(xlDown)).Offset(0, 5).Formula = "=year(" & Cells(2, tCol).Address(False, False) & ")&""-""&month(" & Cells(2, tCol).Address(False, False) & ")"

 

[SeekerExcel11]

gpeacock, your code is a little bit longer (but also very comprehensive) than I expected. This could be a problem when I use the same logic to incorporate more columns that I create with my data set.

par60056, yeap, the offset(0,5) was the problem haha. I should have been more detailed, my bad. To answer your question, I would like to put the new column at the end. That new column would be called, "Year Month" in row 1. And from row 2 to as long as whatever the "Time" column is, VBA would apply the formula that I specified. Does this make sense?

 

[par60056]

Here is a slight modification.

It finds the last column in the header row, adds 1 and make that the "Year Month" column and then uses the difference between that column and the "Time" column to set the offset.

Dim lastCol As Integer
Dim tCol As Integer
lastCol = Range("A1").End(xlToRight).Column + 1
tCol = WorksheetFunction.Match("Time", Range(Cells(1, 1), Cells(1, lastCol)), 0)


Cells(1, lastCol) = "Year Month"
Range(Cells(2, tCol), Cells(1, tCol).End(xlDown)).Offset(0, lastCol - tCol).Formula = "=year(" & Cells(2, tCol).Address(False, False) & ")&""-""&month(" & Cells(2, tCol).Address(False, False) & ")"