Macro to create formula based on last 2 cells of a column [SOLVED]

[skarnik01]

Hi,

I am writing a Macro wherein I need to identify 2 last cells in the column (whose rows keep increasing on daily basis)

Added a Command button, on clicking the following actions should occur -
- Identify 2nd last row cell value from column C as C1
- Identify last row cell value from column C as C2
- In another sheet 'Calc Formulae' in cell C18 I need to apply formula:
apply formula - IF(ABS(C2-C1)>90,"YES",IF(ABS(C2-C1)>C1*1.5%,"YES","NO"))

Please help -

 

[Faseeh]

Why you need a macro for that? Just two helper cells and its will be done

 

[skarnik01]

Why you need a macro for that? Just two helper cells and its will be done

I intend to add it to another Macro I have created. Also the cell references are not absolute. Please let me how to use cell helpers if this can be really used in my case.

 

[Somendra Misra]

Hi skarnik01,

May be this code fulfill your requirement.

Sub lastC()

Dim lr As Long
Dim ws As Worksheet

Set ws = ThisWorkbook.ActiveSheet

With ws
lr = .Cells(Rows.Count, 3).End(xlUp).Row ' calculate last row in column C - 3 represent column C. Change it as per your need.
End With

ws.Cells(1, 5) = ws.Cells(lr, 3).Value      ' last value in column C will go in cell E5
ws.Cells(2, 5) = ws.Cells(lr - 1, 3).Value  ' last but 1 value will go in E2.

End Sub

Regards,

 

[skarnik01]

Hi skarnik01,

May be this code fulfill your requirement.

Sub lastC()
 
Dim lr As Long
Dim ws As Worksheet
 
Set ws = ThisWorkbook.ActiveSheet
 
With ws
lr = .Cells(Rows.Count, 3).End(xlUp).Row ' calculate last row in column C - 3 represent column C. Change it as per your need.
End With
 
ws.Cells(1, 5) = ws.Cells(lr, 3).Value      ' last value in column C will go in cell E5
ws.Cells(2, 5) = ws.Cells(lr - 1, 3).Value  ' last but 1 value will go in E2.
 
End Sub

Regards,

Hey it worked as expected, thanks Sir !

 

[Somendra Misra]

Welcome back anytime!!!
Regards,