VBA: Select last used cell in column and insert formula.

[jakobt]

Want to write some VBA code for:

Want to select the last used cell in column I.

The I want to select 2 cells below and insert the Text Result before tax.

Then I want to go one cell to the right and insert a formula:=+sumifs(I:I,B:B,">4000000")

(This is part of a bigger VBA model - for a task which needs to be one repetitively)

 

[Kamolga]

Want to select the last used cell in column I

Do you really need to select it? just keep lr for calculation and don't use last line

Dim lr As Long
lr = ActiveSheet.Cells(ActiveSheet.Rows.Count, "I").End(xlUp).Row
Range("I"&lr).select

The I want to select 2 cells below and insert the Text Result before tax.​

[LEFT][COLOR=#222222][FONT=Verdana]Range("I"&lr[COLOR=#222222][FONT=Verdana]+2[/FONT][/COLOR]).value="[COLOR=#333333][FONT=Verdana]Result before tax"[/FONT][/COLOR][/FONT][/COLOR][/LEFT]

Then I want to go one cell to the right and insert a formula:=+sumifs(I:I,B:B,">4000000")​

[COLOR=#222222][FONT=Verdana]Range("J"&lr[COLOR=#222222][FONT=Verdana]+2[/FONT][/COLOR]).value="[COLOR=#333333][FONT=Verdana]=+sumifs(I:I,B:B,"">4000000"")[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]"[/FONT][/COLOR][/FONT][/COLOR]

 

[jakobt]

This is great only correction:
I want to make the sumifs only for accounts higher than 4.000.000 and less than 8.000.000

 

[Kamolga]

[LEFT][COLOR=#222222][FONT=Verdana]Range("J"&lr[/FONT][/COLOR][COLOR=#222222][COLOR=#222222][FONT=Verdana][FONT=Verdana]+2[/FONT][/FONT][/COLOR][/COLOR][COLOR=#222222][FONT=Verdana]).value="[/FONT][/COLOR][COLOR=#333333][COLOR=#333333][FONT=Verdana][FONT=Verdana]=+sumifs(I:I,B:B,"">4000000"",[/FONT][/FONT][/COLOR][/COLOR][COLOR=#333333][COLOR=#333333][FONT=Verdana][FONT=Verdana][COLOR=#333333][FONT=Verdana]B:B,""<8000000""[/FONT][/COLOR][/FONT][/FONT][/COLOR][/COLOR][COLOR=#333333][COLOR=#333333][FONT=Verdana][FONT=Verdana])[/FONT][/FONT][/COLOR][/COLOR][COLOR=#333333][COLOR=#333333][FONT=Verdana][FONT=Verdana]"[/FONT][/FONT][/COLOR][/COLOR][/LEFT]

 

[jakobt]

Thanks this is great!
One last question.
I want to give a name to the cell with the formula. The name should be:"PBT"

In another sheet I want to reference this cell by referencing =PBT

(This should be part of VBA code)

 

[Fluff]

Try

[COLOR=#222222][FONT=Verdana]Range("J"&lr[/FONT][/COLOR][COLOR=#222222][COLOR=#222222][FONT=Verdana][FONT=Verdana]+2[/FONT][/FONT][/COLOR][/COLOR][COLOR=#222222][FONT=Verdana]).Name="PBT"[/FONT][/COLOR]

 

[Kamolga]

I did not know Fluff's way to name a range, I make it difficult :

ActiveWorkbook.Names.Add Name:="PTB", RefersTo:=Worksheets("Sheet1").[FONT=Verdana]Range("J"&lr[/FONT][COLOR=#222222][COLOR=#222222][FONT=monospace][COLOR=#222222][FONT=monospace][FONT=Verdana][FONT=Verdana]+2[/FONT][/FONT][/FONT][/COLOR][/FONT][/COLOR][/COLOR][COLOR=#222222][COLOR=#222222][FONT=monospace][FONT=Verdana])[/FONT][/FONT][/COLOR][/COLOR]

but that can happens only once (I guess you get an error if you add twice the same name in the same workbook)
So to change a name that already exists, I would use

[LEFT][COLOR=#222222][FONT=Verdana]ActiveWorkbook.Names("PTB") RefersTo:=Worksheets("Sheet1").[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]Range("J"&lr[/FONT][/COLOR][COLOR=#222222][COLOR=#222222][FONT=monospace][COLOR=#222222][FONT=monospace][FONT=Verdana][FONT=Verdana]+2[/FONT][/FONT][/FONT][/COLOR][/FONT][/COLOR][/COLOR][COLOR=#222222][COLOR=#222222][FONT=monospace][FONT=Verdana])[/FONT][/FONT][/COLOR][/COLOR][/LEFT]

 

[Fluff]

If you want the name to be sheet rather than workbook scope try

    ActiveSheet.Names.Add Name:="PBT", RefersTo:=Range("J" & lr + 2)

 

[jakobt]

Want to change the format for the cell referenced: ("J" & lr + 2) to
Font.bold = true.
Can anyone help to write this code..

 

[Kamolga]

Range[COLOR=#333333][FONT=Verdana]("J" & lr + 2).[/FONT][/COLOR][COLOR=#333333][FONT=Verdana][COLOR=#333333][FONT=Verdana]Font.bold = true[/FONT][/COLOR][/FONT][/COLOR]