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

abdo meghari · Jan 14, 2023

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

4

BS 175/70R13

EP150

INDO

-

5

BS 175/70R14

MY02

THI

-

296

-

296

6

BS 195/65R15

MY02

THI

-

50

361

-

361

7

BS 205/65R15

EP150

INDO

-

4

-

8

BS 235/95R15C

D618

JAP

-

56

-

56

9

BS 255/70R15C

D840

THI

-

66

1329

100

1425

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

16

BS 255/70R16

T697

INDO

-

1

-

1

17

TTL

18

PSR (HRD)

BS 215/45R17

T001

JAP

-

19

BS 225/45R17

RE050A

JAP

-

26

-

26

20

BS 225/45R17

EA03

JAP

-

21

FS 225/45R17

TZ700

JAP

-

22

BS 225/65R17

T697

INDO

-

24

-

24

23

BS 225/70R17

D697

THI

-

50

-

50

24

BS 275/45R20

DSPORT

JAP

-

25

BS 295/35R20

S001

JAP

-

1

26

BS 315/35R20

SPORT

JAP

-

3

-

3

27

TTL

28

LTR

BS 650R16

R230

JAP

-

8

219

350

5

219

29

BS 700R16

R230

JAP

-

316

1193

700

6

1193

30

BS 750R16

R230

JAP

-

1221

9105

2538

364

9105

31

BS 195/70R15C

R623

JAP

-

389

-

389

32

BS 205/70R15C

R624

INDO

-

17

1030

-

1030

111

919

33

BS 215/70R15C

R624

INDO

-

40

1225

-

1225

34

BS 235/65R16C

R660

TR

-

28

-

35

TTL

36

LSR

BS 750R16

VSJ

JAP

-

44

4939

-

4939

37

BS 825R16

R180

JAP

-

38

TTL

39

TBR

BS 1200R20

G580

JAP

-

3570

2641

3967

1460

2641

40

BS 1200R20

R187

JAP

-

607

481

100

19

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

45

BS 12R22.5

R187

JAP

-

79

-

79

46

BS 13R22.5

R187

JAP

-

8

2

-

47

BS 275/70R22.5

R294

JAP

-

166

-

166

48

BS 315/80R22.5

R184

JAP

-

34

2

1

3

49

BS 315/80R22.5

R184

THI

-

8

948

-

12

948

50

BS 1200R24

G580

JAP

-

135

166

1098

492

166

51

TTL

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 · Jan 24, 2023

thanks
seem to something missed
if fill any value for any each part contains TTL should return the formula for all of TTL rows for two both columns ARRIVED & SALES even if some parts are blank cell without any value .
it's not necessity to fill numbers for each part to return the formula again . just search any part contains values in columns AARIVED , SALES , then fill the formulas for all of TTL rows .
sorry about this detail could be missed!

Alex Blakenburg · Jan 24, 2023

I'm confused, so either all TTL rows have a formula or nine have a formula ?

If that is the case, before the macro starts are the TTL rows blank or with formula ?

Once I have answers I will look at it tomorrow.

abdo meghari · Jan 24, 2023

I don't understand what you mean nine word

before the macro starts are the TTL rows blank or with formula ?

before run the macro contains formula and based on your last version it will clear the formula for all of TTL rows when they are empty cells precede TTL rows and this what I want.
the problem If one of TTL rows fill numeric values just in column Arrived into Blank cell will just add the formula for the TTL row for the column Arrived has been filled numeric values and ignore adding formula to SALES column and ignore adding formula to Arrived & SALES columns for others parts contain TTL rows.
so the first case is ok as I have ever said based on your last version.
second case when add one of two columns or two columns together(Arrived ,Sales) for one part or two part whatever how many fill numeric values for each TTL row , directly should adding formula to Arrived & SALES columns for others parts contain TTL rows , even if some parts are Blank cell ( there is no values for columns Arrived & sales at all) .
note: I'm struggling for post some pictures based on XL2BB , despite of the same size as in OP , but gives message about limiting 10000 characters and try decreasing data but still gives the message
if you still need pictures I will capture by tool from win system .

Alex Blakenburg · Jan 24, 2023

Not 100% clear but try this:

VBA Code:

Sub AddTotals_v03()

    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 rngLatest As Range, sumrngLatest As Double, 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")
        ' Last 2 data columns (exclude last row total column)
        Set rngLatest = .Range(.Cells(3, lastCol - 2), .Cells(lastRow, lastCol - 1))
    End With
    
    ' Test for no values in Latest columns and exit if no values
    sumrngLatest = Application.WorksheetFunction.Sum(rngLatest)
    
    If sumrngLatest = 0 Then
        rngLatest.ClearContents
        Exit Sub
    End If
    
    ' Values exist contintue to add totals
    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 rngLatest = .Range(.Cells(totalRow, lastCol - 2), .Cells(totalRow, lastCol))
        End With
        
        With rngLatest
            .FormulaR1C1 = sht.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 · Jan 25, 2023

very fantastic !!
this is exactly what I look for .

many thanks for your answering & time

Alex Blakenburg · Jan 25, 2023

You're welcome. Glad I could help.

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

abdo meghari

Well-known Member

abdo meghari

Well-known Member

Excel Facts

Alex Blakenburg

MrExcel MVP

abdo meghari

Well-known Member

Alex Blakenburg

MrExcel MVP

abdo meghari

Well-known Member

Alex Blakenburg

MrExcel MVP

Similar threads

Forum statistics

Share this page

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

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

abdo meghari

Well-known Member

abdo meghari

Well-known Member

Excel Facts

Alex Blakenburg

MrExcel MVP

abdo meghari

Well-known Member

Alex Blakenburg

MrExcel MVP

abdo meghari

Well-known Member

Alex Blakenburg

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock