J
Janis
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_count").Range("D
"),
Workbooks("dct_count.xls").Sheets("dct_count").UsedRange)
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_Range, 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_Range, 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
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_count").Range("D
"),Workbooks("dct_count.xls").Sheets("dct_count").UsedRange)
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_Range, 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_Range, 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