I have this long code that used to work but doesn't now. The reason is one
column in the active spreadsheet changed. If you add a blank column "B" in
the active spreadsheet and a column "E" in the dct_count spreadsheet it
works. I would like to fix it so you don't have to add these 2 columns every
time I run it. Basically what it is doing is updating the active sheet with
the counts on column "c" from the other sheet dct_count.xls. It should be
column B so I change the range to column b:12 b:64000 and change the offset
to (0,1) but then it throws in an extra row between every c cell. I tried
different combinations of the above and it still adds in a row under each
count that needs to be updated.


If you can tell me where to look it would be great. There are two
sub-routines but they just add the totals. I don't think they change
anything.

thanks,

------------------code-----------------

Sub DCT_Count()

Dim C As Range
Dim R As Range
Dim FoundRange As Range
Dim DCT As Long
Dim Wb As Workbook
Dim DCT_Workbook_Found As Boolean


Dim DCT_Count_Range As Range
Dim Streams_Needed As Range

DCT_Workbook_Found = False

For Each Wb In Workbooks
If Wb.Name = "dct_count.xls" Then
DCT_Workbook_Found = True
Exit For
End If
Next Wb

If DCT_Workbook_Found = False Then
MsgBox ("The dct_count.xls workbook needs to be open for this Macro
to work. Please open the workbook and rerun the Macro")
Exit Sub
End If


Remove_Subtotals_VOD

Set DCT_Count_Range =
Intersect(Workbooks("dct_count.xls").Sheets("dct_c ount").Range("D:D"),
Workbooks("dct_count.xls").Sheets("dct_count").Use dRange)

DCT_Count_Range.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' For Each C In Intersect(Range("C12:C64000"), ActiveSheet.UsedRange)
' DCT = 0
'
' DCT = Application.WorksheetFunction.SumIf(DCT_Count_Rang e, C.Value,
DCT_Count_Range.Offset(0, 2))
' C.Offset(0, 2).Value = DCT
For Each C In Intersect(Range("C12:C64000"), ActiveSheet.UsedRange)
DCT = 0

DCT = Application.WorksheetFunction.SumIf(DCT_Count_Rang e, C.Value,
DCT_Count_Range.Offset(0, 1))
C.Offset(0, 2).Value = DCT
Next C

Application.Calculate

Set Streams_Needed = Intersect(Range("H12:H64000"),
ActiveSheet.UsedRange)

Streams_Needed.Font.Bold = False
Streams_Needed.Font.ColorIndex = xlAutomatic


Add_Subtotals_VOD

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set Streams_Needed = Intersect(Range("H12:H64000"), ActiveSheet.UsedRange)

For Each C In Streams_Needed
If C.Formula Like "=SUMIF*" Then
If C.Value 40 Then
C.Font.Bold = True
C.Font.ColorIndex = 3
End If
End If
Next C

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic



End Sub