CODEWITHSUNDEEP
excelvba
Joseph Prespare
Joseph Prespare

Reputation: 55

Add formula to cell in last column

I'm looking to add a formula to the last column of my worksheet.

The problem is that the number of columns will change.

My code thus far:

Dim LastColumn As Long
Dim LastRow As Long
Dim r, count As Range
Dim temp As Integer

ActiveSheet.Name = "Sheet1"

Set sht = ThisWorkbook.Sheets("Sheet1")
  
LastRow = Range("A" & Rows.count).End(xlUp).Row
With ActiveSheet
    .Cells(1, .Cells(2, .Columns.count).End(xlToLeft).Column + 1).Value = "Index"
End With
    
With ActiveSheet
    .Cells(2, .Cells(2, .Columns.count).End(xlToLeft).Column + 1).Formula = "SUM(LEN(LastColumn)-LEN(SUBSTITUTE(LastColumn,".jpg","")))/LEN(".jpg")"
End With

The formula I have looks at the last column with data, which contains multiple file names. I'm trying to create an index on that column to tell me how many file names exist in this cell, as another part of my macro inserts rows based on this value.

Upvotes: 0

Views: 336

Answers (1)

norie
norie

Reputation: 9857

Use R1C1 notation to refer to the column to the left of the column you are putting the formula in.

With ActiveSheet
    .Cells(2, .Cells(2, .Columns.count).End(xlToLeft).Column + 1).Resize(LastRow - 1).FormulaR1C1 = "=SUM(LEN(RC[-1])-LEN(SUBSTITUTE(RC[-1],"".jpg"","""")))/LEN("".jpg"")"
End With

Upvotes: 2

Related Questions