how do i get excel to add 0s???

[CThai]

i have a workbook with missing dates and would like excel to insert any missing dates and add 0's into that missing dates if there is no data...

for example

Book1
	A	B	C	D
1	Date	Company Name	Total
2	1/2/2006	A	25
3	1/2/2006	A	35
4	1/3/2006	B	125
5	1/5/2006	B	20
6	1/5/2006	B	185
7	1/7/2006	A	200
8	1/8/2006	B	250
9	1/10/2006	B	250
10	1/10/2006	A	350
Sheet1

 

[CThai]

hi QuietRiot -

Your codes work great ! just one minor thing in the part where it say company's name - it's showing up "Y" and "D" is there away i can have it to show up as the company's name --- since in column C i have it 37368A - can it just show up A in column B when run the codes?

thank you

 

[QuietRiot]

can you post some samples of your real data

so i can get a better idea

 

[CThai]

PADD_testDaily.xls
	A	B	C	D
2	4/22/2002	Giant	37368Giant	9875
3	4/22/2002	Giant	37368Giant	9875
4	4/22/2002	y	37368Valero	0
5	4/22/2002	y	37368Valero	0
6	4/23/2002	Giant	37369Giant	15000
7	4/23/2002	y	37369Valero	0
8	4/24/2002	Giant	37370Giant	15000
9	4/24/2002	y	37370Valero	0
10	4/25/2002	Giant	37371Giant	15000
11	4/25/2002	y	37371Valero	0
12	10/20/2002	Valero	37549Valero	11500
13	10/20/2002	d	37549Giant	0
14	10/21/2002	Valero	37550Valero	11500
15	10/21/2002	d	37550Giant	0
16	10/24/2002	Valero	37553Valero	27500
17	10/24/2002	d	37553Giant	0
18	10/25/2002	Valero	37554Valero	27500
19	10/25/2002	d	37554Giant	0
Coker

 

[QuietRiot]

so what are the actual companies

i programmed it for just A and B

will it always be giant and valero?

 

[QuietRiot]

try this

Sub addzeros()
Dim rng As String, llast As String, g As Long


Set c = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)
If c Is Nothing Then
    last = 1
Else
    last = c.Row
End If
llast = last
rng = "C2:C" + llast
a = last + 1

For x = 2 To last
datte = Cells(x, 3).Value
  checkdatte = Left(datte, 5) + "Giant"
    g = Application.WorksheetFunction.CountIf(Range(rng), checkdatte)
If g < 1 Then
Cells(a, 3).Formula = checkdatte
Cells(a, 4).Formula = 0
Cells(a, 2).Formula = Mid(checkdatte, 6, 20)
Cells(a, 1).Formula = Left(checkdatte, 5)
a = a + 1
Else
End If
Next x
  
For x = 2 To last
datte = Cells(x, 3).Value
  checkdatte = Left(datte, 5) + "Valero"
    g = Application.WorksheetFunction.CountIf(Range(rng), checkdatte)
If g < 1 Then
Cells(a, 3).Formula = checkdatte
Cells(a, 4).Formula = 0
Cells(a, 2).Formula = Mid(checkdatte, 6, 20)
Cells(a, 1).Formula = Left(checkdatte, 5)
a = a + 1
Else
End If
Next x
    
Columns("A:A").Select
    Selection.NumberFormat = "m/d/yyyy"

End Sub

 

[CThai]

will it always be giant and valero?

No the company will not alway be Gaint and Valero --- I'm using your codes for multiple worksheets with multiple company's names... some worksheets will have more then 2 company's listed -

I have to tell you I was so happy yesterday when i ran your codes I wanted to reach and give you the biggest hugs

 

[QuietRiot]

haha,
I think we might have to toss it though


i'll see what i can come up with

 

[QuietRiot]

can you post one more sample data without running the code

 

[CThai]

without the codes ---

Book2
	A	B	C	D
2	20-Oct-02	Valero	23000	23000
3	21-Oct-02	Valero	23000	23000
4	24-Oct-02	Valero	55000	55000
5	25-Oct-02	Valero	55000	55000
6	26-Oct-02	Valero	55000	55000
7	27-Oct-02	Valero	55000	55000
8	28-Oct-02	Valero	55000	55000
9	29-Oct-02	Valero	55000	55000
10	30-Oct-02	Valero	55000	55000
11	31-Oct-02	Valero	55000	55000
12	29-Nov-02	Valero	55000	55000
13	30-Nov-02	Valero	55000	55000
14	1-Dec-02	Valero	55000	55000
15	22-Apr-02	Giant	30000	19750
16	22-Apr-02	Giant	9500	19750
17	23-Apr-02	Giant	30000	30000
18	24-Apr-02	Giant	30000	30000
19	25-Apr-02	Giant	30000	30000
20	21-Mar-03	Giant	53000	36250
21	21-Mar-03	Giant	19500	36250
22	22-Mar-03	Giant	53000	36250
23	22-Mar-03	Giant	19500	36250
24	23-Mar-03	Giant	53000	36250
25	23-Mar-03	Giant	19500	36250
26	24-Mar-03	Giant	53000	36250
Coker

 

[QuietRiot]

how does this work for you?

Sub addzeros()
Dim rng As String, llast As String, g As Long, strname As String

Set c = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)
If c Is Nothing Then
    last = 1
Else
    last = c.Row
End If
llast = last
rng = "C2:C" + llast
a = last + 1

strname = InputBox(Prompt:="Company Name to add zeros", _
          Title:="ENTER COMPANY NAME")

For x = 2 To last
datte = Cells(x, 3).Value
  checkdatte = Left(datte, 5) + strname
    g = Application.WorksheetFunction.CountIf(Range(rng), checkdatte)
If g < 1 Then
Cells(a, 3).Formula = checkdatte
Cells(a, 4).Formula = 0
Cells(a, 2).Formula = Mid(checkdatte, 6, 20)
Cells(a, 1).Formula = Left(checkdatte, 5)
a = a + 1
Else
End If
Next x
  
Columns("A:A").Select
    Selection.NumberFormat = "m/d/yyyy"

End Sub

just run it for every company by entering the company name.