replace blank cell with formula and formula with blank cells based on empty cell or numbers

[abdo meghari]

Hello

I search for macro dealing with the last columns ARRIVED,SALES for

if the cells for columns ARRIVED,SALES before TTL row are empty then should replace blank cell with formula for TTL row , and if the cells for columns ARRIVED,SALES before TTL row are number then should return the same formula was already existed. I add three columns( ARRIVED,SALES,STOCK ) every month ,then should deal with the last columns ARRIVED,SALES have added when implement the conditions .

orginal data

Stock Sales report (12) .xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1					OCTOBER			NOVEMBER			DECEMBER
2	Category	Size	Pattern	Origin	Arrived	Sales	Stock	Arrived	Sales	Stock	Arrived	Sales	Stock
3	PSR (LRD)	BS 175/70R13	B25A32	INDO	-	16	184	-	-	184			184
4		BS 175/70R13	EP150	INDO	-	-	-	-	-	-			-
5		BS 175/70R14	MY02	THI	-	-	296	-	-	296			296
6		BS 195/65R15	MY02	THI	-	50	361	-	-	361			361
7		BS 205/65R15	EP150	INDO	-	-	-	-	4	-			-
8		BS 235/95R15C	D618	JAP	-	-	56	-	-	56			56
9		BS 255/70R15C	D840	THI	-	66	1329	100	1425	1329			1329
10		FS 31*10.50R15	DEST AT	JAP	-	-	-	-	-	-			-
11		BS 205R16C	D840	THI	-	-	-	100	48	-			-
12		BS 205/R16C	D697	THI	-	4	293	-	-	293			293
13		BS 205/55R16	AR20	INDO	-	1	-	-	-	-			-
14		BS 225/55R16	T001	JAP	-	-	-	-	2	-			-
15		BS 225/60R16	AR20	INDO	-	-	2	-	-	2			2
16		BS 255/70R16	T697	INDO	-	-	1	-	-	1			1
17		TTL			-	137	2522	200	1479	2522	-	-	2522
18	PSR (HRD)	BS 215/45R17	T001	JAP	-	-	-	-	-	-			-
19		BS 225/45R17	RE050A	JAP	-	-	26	-	-	26			26
20		BS 225/45R17	EA03	JAP	-	-	-	-	-	-			-
21		FS 225/45R17	TZ700	JAP	-	-	-	-	-	-			-
22		BS 225/65R17	T697	INDO	-	-	24	-	-	24			24
23		BS 225/70R17	D697	THI	-	-	50	-	-	50			50
24		BS 275/45R20	DSPORT	JAP	-	-	-	-	-	-			-
25		BS 295/35R20	S001	JAP	-	-	-	-	-	-			-
26		BS 315/35R20	SPORT	JAP	-	-	3	-	-	3			3
27		TTL			-	-	103	-	-	103	-	-	103
28	LTR	BS 650R16	R230	JAP	-	8	219	350	5	219			219
29		BS 700R16	R230	JAP	-	316	1193	700	6	1193			1193
30		BS 750R16	R230	JAP	-	1221	9105	2538	364	9105			9105
31		BS 195/70R15C	R623	JAP	-	-	389	-	-	389			389
32		BS 205/70R15C	R624	INDO	-	17	1030	-	-	1030			1030
33		BS 215/70R15C	R624	INDO	-	40	1225	-	-	1225			1225
34		BS 235/65R16C	R660	TR	-	-	-	28	-	-			-
35		TTL			-	1602	13161	3616	375	13161	-	-	13161
36	LSR	BS 750R16	VSJ	JAP	-	44	4939	-	-	4939			4939
37		BS 825R16	R180	JAP	-	-	-	-	-	-			-
38		TTL			-	44	4939	-	-	4939	-	-	4939
39	TBR	BS 1200R20	G580	JAP	-	3570	2641	3967	1460	2641			2641
40		BS 1200R20	R187	JAP	-	607	481	100	19	481			481
41		BS 1400R20	VSJ	JAP	-	34	436	-	-	436			436
42		BS 1400R20	R180	JAP	-	-	-	-	-	-			-
43		BS 1400R20	R180BZ	JAP	-	-	-	-	-	-			-
44		BS 11R22.5	R187	JAP	-	-	128	-	-	128			128
45		BS 12R22.5	R187	JAP	-	-	79	-	-	79			79
46		BS 13R22.5	R187	JAP	-	-	-	8	2	-			-
47		BS 275/70R22.5	R294	JAP	-	-	166	-	-	166			166
48		BS 315/80R22.5	R184	JAP	-	34	2	2	2	2			2
49		BS 315/80R22.5	R184	THI	-	8	948	-	12	948			948
50		BS 1200R24	G580	JAP	-	135	166	1098	492	166			166
51		TTL			-	4388	5047	5175	1987	5047	-	-	5047
In & Out Balance

Cell Formulas
Range	Formula
E17:M17	E17	=SUM(E3:E16)
E27:M27	E27	=SUM(E18:E26)
F29	F29	=310+6
E35:M35	E35	=SUM(E28:E34)
E38:M38	E38	=SUM(E36:E37)
F40	F40	=586+21
J39:J50,J36:J37,J28:J34,J18:J26,J3:J16	J3	=G3
M39:M50,M36:M37,M28:M34,M18:M26,M3:M16	M3	=J3+K3-L3
E51:M51	E51	=SUM(E39:E50)

firs condition replace blank cell with formula for TTL row

Stock Sales report (12) .xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1					OCTOBER			NOVEMBER			DECEMBER
2	Category	Size	Pattern	Origin	Arrived	Sales	Stock	Arrived	Sales	Stock	Arrived	Sales	Stock
3	PSR (LRD)	BS 175/70R13	B25A32	INDO	-	16	184	-	-	184			184
4		BS 175/70R13	EP150	INDO	-	-	-	-	-	-			-
5		BS 175/70R14	MY02	THI	-	-	296	-	-	296			296
6		BS 195/65R15	MY02	THI	-	50	361	-	-	361			361
7		BS 205/65R15	EP150	INDO	-	-	-	-	4	-			-
8		BS 235/95R15C	D618	JAP	-	-	56	-	-	56			56
9		BS 255/70R15C	D840	THI	-	66	1329	100	1425	1329			1329
10		FS 31*10.50R15	DEST AT	JAP	-	-	-	-	-	-			-
11		BS 205R16C	D840	THI	-	-	-	100	48	-			-
12		BS 205/R16C	D697	THI	-	4	293	-	-	293			293
13		BS 205/55R16	AR20	INDO	-	1	-	-	-	-			-
14		BS 225/55R16	T001	JAP	-	-	-	-	2	-			-
15		BS 225/60R16	AR20	INDO	-	-	2	-	-	2			2
16		BS 255/70R16	T697	INDO	-	-	1	-	-	1			1
17		TTL			-	137	2522	200	1479	2522			2522
18	PSR (HRD)	BS 215/45R17	T001	JAP	-	-	-	-	-	-			-
19		BS 225/45R17	RE050A	JAP	-	-	26	-	-	26			26
20		BS 225/45R17	EA03	JAP	-	-	-	-	-	-			-
21		FS 225/45R17	TZ700	JAP	-	-	-	-	-	-			-
22		BS 225/65R17	T697	INDO	-	-	24	-	-	24			24
23		BS 225/70R17	D697	THI	-	-	50	-	-	50			50
24		BS 275/45R20	DSPORT	JAP	-	-	-	-	-	-			-
25		BS 295/35R20	S001	JAP	-	-	-	-	-	-			-
26		BS 315/35R20	SPORT	JAP	-	-	3	-	-	3			3
27		TTL			-	-	103	-	-	103			103
28	LTR	BS 650R16	R230	JAP	-	8	219	350	5	219			219
29		BS 700R16	R230	JAP	-	316	1193	700	6	1193			1193
30		BS 750R16	R230	JAP	-	1221	9105	2538	364	9105			9105
31		BS 195/70R15C	R623	JAP	-	-	389	-	-	389			389
32		BS 205/70R15C	R624	INDO	-	17	1030	-	-	1030			1030
33		BS 215/70R15C	R624	INDO	-	40	1225	-	-	1225			1225
34		BS 235/65R16C	R660	TR	-	-	-	28	-	-			-
35		TTL			-	1602	13161	3616	375	13161			13161
36	LSR	BS 750R16	VSJ	JAP	-	44	4939	-	-	4939			4939
37		BS 825R16	R180	JAP	-	-	-	-	-	-			-
38		TTL			-	44	4939	-	-	4939			4939
39	TBR	BS 1200R20	G580	JAP	-	3570	2641	3967	1460	2641			2641
40		BS 1200R20	R187	JAP	-	607	481	100	19	481			481
41		BS 1400R20	VSJ	JAP	-	34	436	-	-	436			436
42		BS 1400R20	R180	JAP	-	-	-	-	-	-			-
43		BS 1400R20	R180BZ	JAP	-	-	-	-	-	-			-
44		BS 11R22.5	R187	JAP	-	-	128	-	-	128			128
45		BS 12R22.5	R187	JAP	-	-	79	-	-	79			79
46		BS 13R22.5	R187	JAP	-	-	-	8	2	-			-
47		BS 275/70R22.5	R294	JAP	-	-	166	-	-	166			166
48		BS 315/80R22.5	R184	JAP	-	34	2	2	2	2			2
49		BS 315/80R22.5	R184	THI	-	8	948	-	12	948			948
50		BS 1200R24	G580	JAP	-	135	166	1098	492	166			166
51		TTL			-	4388	5047	5175	1987	5047			5047
In & Out Balance

Cell Formulas
Range	Formula
M17,E17:J17	E17	=SUM(E3:E16)
M27,E27:J27	E27	=SUM(E18:E26)
F29	F29	=310+6
M35,E35:J35	E35	=SUM(E28:E34)
M38,E38:J38	E38	=SUM(E36:E37)
F40	F40	=586+21
J39:J50,J36:J37,J28:J34,J18:J26,J3:J16	J3	=G3
M51,E51:J51	E51	=SUM(E39:E50)
M39:M50,M36:M37,M28:M34,M18:M26,M3:M16	M3	=J3+K3-L3

second condition replace formula with blank cell

Stock Sales report (12) .xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1					OCTOBER			NOVEMBER			DECEMBER
2	Category	Size	Pattern	Origin	Arrived	Sales	Stock	Arrived	Sales	Stock	Arrived	Sales	Stock
3	PSR (LRD)	BS 175/70R13	B25A32	INDO	-	16	184	-	-	184			184
4		BS 175/70R13	EP150	INDO	-	-	-	-	-	-			-
5		BS 175/70R14	MY02	THI	-	-	296	-	-	296			296
6		BS 195/65R15	MY02	THI	-	50	361	-	-	361			361
7		BS 205/65R15	EP150	INDO	-	-	-	-	4	-			-
8		BS 235/95R15C	D618	JAP	-	-	56	-	-	56			56
9		BS 255/70R15C	D840	THI	-	66	1329	100	1425	1329			1329
10		FS 31*10.50R15	DEST AT	JAP	-	-	-	-	-	-			-
11		BS 205R16C	D840	THI	-	-	-	100	48	-			-
12		BS 205/R16C	D697	THI	-	4	293	-	-	293	11		304
13		BS 205/55R16	AR20	INDO	-	1	-	-	-	-			-
14		BS 225/55R16	T001	JAP	-	-	-	-	2	-			-
15		BS 225/60R16	AR20	INDO	-	-	2	-	-	2			2
16		BS 255/70R16	T697	INDO	-	-	1	-	-	1			1
17		TTL			-	137	2522	200	1479	2522	11	-	2533
18	PSR (HRD)	BS 215/45R17	T001	JAP	-	-	-	-	-	-			-
19		BS 225/45R17	RE050A	JAP	-	-	26	-	-	26			26
20		BS 225/45R17	EA03	JAP	-	-	-	-	-	-			-
21		FS 225/45R17	TZ700	JAP	-	-	-	-	-	-			-
22		BS 225/65R17	T697	INDO	-	-	24	-	-	24			24
23		BS 225/70R17	D697	THI	-	-	50	-	-	50			50
24		BS 275/45R20	DSPORT	JAP	-	-	-	-	-	-			-
25		BS 295/35R20	S001	JAP	-	-	-	-	-	-	1		1
26		BS 315/35R20	SPORT	JAP	-	-	3	-	-	3			3
27		TTL			-	-	103	-	-	103	1	-	104
28	LTR	BS 650R16	R230	JAP	-	8	219	350	5	219			219
29		BS 700R16	R230	JAP	-	316	1193	700	6	1193			1193
30		BS 750R16	R230	JAP	-	1221	9105	2538	364	9105			9105
31		BS 195/70R15C	R623	JAP	-	-	389	-	-	389			389
32		BS 205/70R15C	R624	INDO	-	17	1030	-	-	1030		111	919
33		BS 215/70R15C	R624	INDO	-	40	1225	-	-	1225			1225
34		BS 235/65R16C	R660	TR	-	-	-	28	-	-			-
35		TTL			-	1602	13161	3616	375	13161	-	111	13050
36	LSR	BS 750R16	VSJ	JAP	-	44	4939	-	-	4939			4939
37		BS 825R16	R180	JAP	-	-	-	-	-	-			-
38		TTL			-	44	4939	-	-	4939	-	-	4939
39	TBR	BS 1200R20	G580	JAP	-	3570	2641	3967	1460	2641			2641
40		BS 1200R20	R187	JAP	-	607	481	100	19	481			481
41		BS 1400R20	VSJ	JAP	-	34	436	-	-	436		11	425
42		BS 1400R20	R180	JAP	-	-	-	-	-	-			-
43		BS 1400R20	R180BZ	JAP	-	-	-	-	-	-			-
44		BS 11R22.5	R187	JAP	-	-	128	-	-	128			128
45		BS 12R22.5	R187	JAP	-	-	79	-	-	79			79
46		BS 13R22.5	R187	JAP	-	-	-	8	2	-			-
47		BS 275/70R22.5	R294	JAP	-	-	166	-	-	166			166
48		BS 315/80R22.5	R184	JAP	-	34	2	2	2	2	1		3
49		BS 315/80R22.5	R184	THI	-	8	948	-	12	948			948
50		BS 1200R24	G580	JAP	-	135	166	1098	492	166			166
51		TTL			-	4388	5047	5175	1987	5047	1	11	5037
In & Out Balance

Cell Formulas
Range	Formula
E17:M17	E17	=SUM(E3:E16)
E27:M27	E27	=SUM(E18:E26)
F29	F29	=310+6
E35:M35	E35	=SUM(E28:E34)
E38:M38	E38	=SUM(E36:E37)
F40	F40	=586+21
J39:J50,J36:J37,J28:J34,J18:J26,J3:J16	J3	=G3
M39:M50,M36:M37,M28:M34,M18:M26,M3:M16	M3	=J3+K3-L3
E51:M51	E51	=SUM(E39:E50)

notice: it's not necessary every part should be filled number when replace formula with blank cell as the last picture

the most important if there is any number for any part then should fill formula for all parts for TTL row .

 

[abdo meghari]

 

[abdo meghari]

I hope some body see my request.
thanks

 

[abdo meghari]

is there any help?

 

[Alex Blakenburg]

I am not very clear on what you are trying to do, so lets start with this.

Sub AddTotals()

    Dim sht As Worksheet
    Dim lastRow As Long, lastCol As Long, totalRow As Long
    Dim rngSize As Range, firstCell As Range, totalCell As Range
    Dim strToFind As String, FirstAddr As String
    
    strToFind = "TTL"
    
    Set sht = Worksheets("In & Out Balance")
    With sht
        lastCol = .Cells(2, Columns.Count).End(xlToLeft).Column
        lastRow = .Cells(Rows.Count, "B").End(xlUp).Row
        Set rngSize = .Range("B2:B" & lastRow)
        Set firstCell = .Range("B2")
    End With
    
    Set totalCell = rngSize.Find(What:=strToFind, After:=rngSize.Cells(1, 1), LookIn:=xlFormulas, _
                                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                MatchCase:=False, SearchFormat:=False)
       
    If Not totalCell Is Nothing Then FirstAddr = totalCell.Address
    
    Do
        With sht
            totalRow = totalCell.Row
            .Range(.Cells(totalRow, lastCol - 2), .Cells(totalRow, lastCol)).FormulaR1C1 = _
                                                .Cells(totalRow, lastCol - 3).FormulaR1C1
        End With
    
        Set totalCell = rngSize.FindNext(After:=totalCell)
        If totalCell Is Nothing Then Exit Do
    
    Loop Until totalCell.Address = FirstAddr
    
End Sub

 

[abdo meghari]

Hi Alex,
thanks for your code after many bumps !
I no know why this is not clear .
all of what I want macro clear the formula are existed in TTL row for two last columns Arrived & Sales when the cells where precede TTL row for two last columns Arrived & Sales are empty .
and if when the cells where precede TTL row for two last columns Arrived & Sales contain numbers then return the formula in TTL row for two last columns Arrived & Sales
BTW: your code shows the formula for two both cases whether when the cells where precede TTL row for two last columns Arrived & Sales are empty or if cells where precede TTL row for two last columns Arrived & Sales contain numbers .
the first case should clear formula to become empty when the cells are empty where precede TTL row for two last columns Arrived & Sales .
I hope to give thing maybe you help .

 

[abdo meghari]

Hi Alex,
isn't still clear for you?

 

[Alex Blakenburg]

What I think you are saying doesn't seem to make sense.
You seem to be saying that if all cells in K3:K16 are blank then you don't want a total formula in K17 (and the same for Column L).
Is that correct ?
(if it is that a very strange way of setting up a total line in a spreadsheet)

 

[abdo meghari]

What I think you are saying doesn't seem to make sense.

yes you're right , but you don't know why I want this way.

the main reason I have macro pulls data from another sheet based on all of empty cells for two last columns Arrived & Sales even TTL row , but if all of cells are empty for two last columns Arrived & Sales even if TTL row contain formula ,then the macro will not pull the numbers to fill into last columns Arrived & Sales from another sheet becuase contains formula in TTL row

You seem to be saying that if all cells in K3:K16 are blank then you don't want a total formula in K17 (and the same for Column L).
Is that correct ?

yes that's correct.

(if it is that a very strange way of setting up a total line in a spreadsheet)

you're right , but as I said this depends on another macro to pull data
my problem is stopping macro to pull numbers from another sheet if the TTL row contains formula .
so after pulling the numbers in blank cells , then will return the formula again as I earlier mentioned in OP .
I hope to understand my needing for fix that.

 

[Alex Blakenburg]

See if this works for you.

Sub AddTotals()

    Dim sht As Worksheet
    Dim lastRow As Long, lastCol As Long, totalRow As Long
    Dim rngSize As Range, firstCell As Range, totalCell As Range
    Dim rngTTL_Latest As Range, cntValues As Long
    Dim strToFind As String, FirstAddr As String
    Dim strFormula As String
    
    strToFind = "TTL"
    
    Set sht = Worksheets("In & Out Balance")
    With sht
        lastCol = .Cells(2, Columns.Count).End(xlToLeft).Column
        lastRow = .Cells(Rows.Count, "B").End(xlUp).Row
        Set rngSize = .Range("B2:B" & lastRow)
        Set firstCell = .Range("B2")
    End With
    
    Set totalCell = rngSize.Find(What:=strToFind, After:=rngSize.Cells(1, 1), LookIn:=xlFormulas, _
                                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                MatchCase:=False, SearchFormat:=False)
       
    If Not totalCell Is Nothing Then FirstAddr = totalCell.Address
    
    Do
        totalRow = totalCell.Row
        With sht
            Set rngTTL_Latest = .Range(.Cells(totalRow, lastCol - 2), .Cells(totalRow, lastCol))
        End With
        
        With rngTTL_Latest
            .FormulaR1C1 = sht.Cells(totalRow, lastCol - 3).FormulaR1C1

            ' Arrived Total - Clear if no values
            With .Cells(1)
                strFormula = Replace(.Formula, "Sum", "Count")
                cntValues = Evaluate(strFormula)
                If cntValues = 0 Then .Value = Empty
            End With
            
             ' Sales Total - Clear if no values
            With .Cells(2)
                strFormula = Replace(.Formula, "Sum", "Count")
                cntValues = Evaluate(strFormula)
                If cntValues = 0 Then .Value = Empty
            End With
        End With
    
        Set totalCell = rngSize.FindNext(After:=totalCell)
        If totalCell Is Nothing Then Exit Do
    
    Loop Until totalCell.Address = FirstAddr
    
End Sub