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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

thuyhong@gmail.com - 13 Aug 2007 15:29 GMT

Hello everyone -

I am desperately in need of help:

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:

Column A ------ Column B ------- Column C
1/6/2006 ------- CompanyA ------- 25
1/6/2006 ----------CompanyA ------- 50
1/7/2006 --------- CompanyB ---------100
1/8/2006 ----------CompanyB -----------125
1/9/2006 ----------CompnayA -----------52
1/9/2006 ----------CompanyA ----------50
1/10/2006 --------CompanyB ---------- 200

With the example above I would like excel to recognize that on
1/6/2006 companyB have no data and to insert a row with 1/6/2006 ---
CompanyB ---0

If both company A and B have no data then just insert 0's in... the
reason for this is when graph I would like to see the low day/week/
monthly average...

Can anyone help me with this?

I also posted this question on [http://www.mrexcel.com/board2/
viewtopic.php?t=287266]

Reply to this Message

Brian Withun - 14 Aug 2007 19:57 GMT

On Aug 13, 10:29 am, thuyh...@gmail.com wrote:

> Hello everyone -
>
[quoted text clipped - 27 lines]
> I also posted this question on [http://www.mrexcel.com/board2/
> viewtopic.php?t=287266]

Here's a possibility for you. You have to assign this macro to a
button click, and then click the button only after selecting the top
date in your ColumnA. It will walk down the column filling in missing
zeroes for two companies, "CompA" and "CompB" You'll want to change
these constants to match your situation.

Private Sub FillDate_Click()
Dim MyDate As Date

Dim MyRow As Integer
Dim MyCol As Integer

Const CompA = "CompA"
Const CompB = "CompB"

Dim CompAPresent As Boolean
Dim CompBPresent As Boolean

Dim ExpectedDate As Variant
Dim LastDateSeen As Variant

MyRow = Selection.Row
MyCol = Selection.Column

LastDateSeen = 0
CompAPresent = False
CompBPresent = False

While IsDate(ActiveSheet.Cells(MyRow, MyCol).Value)

' detect if this is the same date we saw last time through
If LastDateSeen <> 0 _
And LastDateSeen <> ActiveSheet.Cells(MyRow, MyCol).Value Then

CompanyCheck:
' upon seeing a new date, check that we had found values
for
' each company on the prior date
If Not CompAPresent Then
ActiveSheet.Rows(MyRow).Insert
ActiveSheet.Cells(MyRow, MyCol).Value = LastDateSeen
ActiveSheet.Cells(MyRow, MyCol + 1).Value = CompA
ActiveSheet.Cells(MyRow, MyCol + 2).Value = 0
MyRow = MyRow + 1
End If

If Not CompBPresent Then
ActiveSheet.Rows(MyRow).Insert
ActiveSheet.Cells(MyRow, MyCol).Value = LastDateSeen
ActiveSheet.Cells(MyRow, MyCol + 1).Value = CompB
ActiveSheet.Cells(MyRow, MyCol + 2).Value = 0
MyRow = MyRow + 1
End If

' detect if this new date is the next chronological date
ExpectedDate = DateSerial(Year(LastDateSeen),
Month(LastDateSeen), Day(LastDateSeen) + 1)
If ActiveSheet.Cells(MyRow, MyCol).Value <> ExpectedDate
Then
CompAPresent = False
CompBPresent = False
LastDateSeen = ExpectedDate
GoTo CompanyCheck
End If

CompAPresent = False
CompBPresent = False

End If

' detect company on current record
If ActiveSheet.Cells(MyRow, MyCol + 1).Value = CompA Then
CompAPresent = True
If ActiveSheet.Cells(MyRow, MyCol + 1).Value = CompB Then
CompBPresent = True

NextLine:
' remember the date and move to next row
LastDateSeen = ActiveSheet.Cells(MyRow, MyCol).Value
MyRow = MyRow + 1

Wend

If LastDateSeen <> 0 Then
If Not CompAPresent Then
ActiveSheet.Rows(MyRow).Insert
ActiveSheet.Cells(MyRow, MyCol).Value = LastDateSeen
ActiveSheet.Cells(MyRow, MyCol + 1).Value = CompA
ActiveSheet.Cells(MyRow, MyCol + 2).Value = 0
MyRow = MyRow + 1
ElseIf Not CompBPresent Then
ActiveSheet.Rows(MyRow).Insert
ActiveSheet.Cells(MyRow, MyCol).Value = LastDateSeen
ActiveSheet.Cells(MyRow, MyCol + 1).Value = CompB
ActiveSheet.Cells(MyRow, MyCol + 2).Value = 0
MyRow = MyRow + 1
End If
End If

End Sub

HTH

Brian Herbert Withun

Reply to this Message