0

Could u help me with formulas in vba?

ColumnLetter = Split(ActiveWorkbook.ActiveSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(1, 0), "$")(0)

Row = ActiveWorkbook.ActiveSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 1

For i = 6 To Row

    var = ActiveSheet.Range("H" & i).value
    var2 = ActiveSheet.Range(ColumnLetter & i).value
    Range(ColumnLetter & i).Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=(RC[" & var & "] - RC[" & var2 & "])"
     
Next i

I would like to substract two values. ColumntLetter - lastColumn Row - lastRow I have problem with last - "ActiveCell.FormulaR1C1 = "=(RC[" & var & "] - RC[" & var2 & "])"

mean
  • 17
  • 4
  • Sorry, no idea what you mean. I would suggest finding the last cell and using the column and row numbers rather than splitting the address apart. With `Set LastCell = ActiveSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)` you can return the values using `LastCell.Row` and `LastCell.Column`. – Darren Bartrup-Cook Mar 18 '22 at 15:48
  • I'd also suggest having a read of this post: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Darren Bartrup-Cook Mar 18 '22 at 15:49

0 Answers0