VBA how to stop the formula based on other cell

[Sallylwy]

Hi , I would like to seek for some advice. I want to write the sumif formula VBA code on column C and stop copying the formula while the cell of column A is blank.
Do I have to set the Lastrow in the VBA code? Do I need to set the reference cell (A2)? Thanksss

 

[richh]

Wrap the SUMIF in an IF Statement.

=IF(A2 = "", "", SUMIF($F$2.....

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Sallylwy()
   Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=sumif($F$2:$F$7,A2,$H$2:$H$7)"
End Sub

 

[Sallylwy]

Hi, thanks for your advise.
The code works for me. But if i add something on cell A8, can I just stop the sumif formula in cell C5 only? How i adjust the code? very new to vba, get a lot questions

 

[Alex Blakenburg]

I assume you want to use VBA as a practice exercise.

This is easily catered without VBA using tables.

20210801 Sumifs Table or VBA.xlsm
	A	B	C	D	E	F	G	H	I
1	Name	Department	# of Holidays			Manager	Employee	Holiday
2	Peter	DM	4			Peter	Kay	2
3	Betty	MO	9			Betty	Terry	4
4	John	Sales	3			John	Anna	3
5	Darron	Sales	1			Darron	Amber	1
6						Peter	Odebe	2
7						Betty	Jack	5
8	Top2
9	Betty
10	Peter
11
Data Tables

Cell Formulas
Range	Formula
C2:C5	C2	=SUMIFS(tbl_HolDetail[Holiday],tbl_HolDetail[Manager],[@Name])

 

[Fluff]

How about

Sub Sallylwy()
   If Range("A2").Value = "" Then Exit Sub
   Range("C2:C" & Range("A1").End(xlDown).Row).Formula = "=sumif($F$2:$F$7,A2,$H$2:$H$7)"
End Sub