J
Janis
I'm updating counts from a sheet called dct_count.xls to the active
worksheet.
It is really a simple macro it just checks the value on the dctPcount.xls
sheet on column E, called number_of_dcts (if I insert the blank column it
makes it F).
Then it checks column D of the activesheet and if it is not the same it
updates it
(if I insert a blank column on D it moves it to E). At least that is what
it should do.
The problem is now I have to insert a blank column "B" on the activesheet
and a blank column E on the dct_count.xls sheet to get it to work presumably
because some columns have been moved and the offsets aren't right.
If you insert a blank column E
on the dct_count.xls and a blank column "B" on the active sheet. That seems
to keep all the offsets correct.
I tried changing all the columns to the correct column letter but there is
some macro that is adding an extra row and value and the counts aren't
updating so I'm missing something I think is probably on an offset value?
Its only a guess but perhaps they are working in the offsets instead of
hardwiring the columns because the output is needed in another module? Or
otherwise I don't know why they are doing it that way but as I said I changed
all the column letters to the right letter
and the macro doesn't work.
So here is the code again and I'll try to explain it.
The serviceGroupColumn is "I" but I don't see a reference to it in this sub.
That
was in the private declarations. This might not be important to this sub.
I'll add just the code below, but the variables are:
dim c as range
dim r as range
dim foundrange as range
dim dct as long
dim wb as workbook
dim dct_count_range as range
--------------code--------------
Set DCT_Count_Range =
Intersect(Workbooks("dct_count.xls").Sheets("dct_count").Range("D
"),
Workbooks("dct_count.xls").Sheets("dct_count").UsedRange)
' This section gets the range of the column D which is the field that has
updated dct counts.
' This sheet dct_count.xls is the sheet compared with the active sheet.
' It is column D but it only works if it is column E as I mentioned.
For Each C in Intersect(Range("C12:C6400"), ActiveSheet.UsedRange)
DCT = 0
' I think what it is doing is initializing the variable DCT
' The range should be column D instead of C
DCT = Application.worksheetFunction.SumIf(DCT_Count_Range, C.Value,
DCT_Count_Range.Offset(0,2))
' I don't know what this offset is for, my column D is DCT the value I
want to update
' Offset 2 columns from C is E which must be why inserting a column in E
makes the macro work.
' Should I just change the offset to 1 without changing the column letters?
' That still doesn't explain why I need to insert a "B" column in the
dct_count.xls sheet?
Next C
If you have read this far the above seems most important however the rest of
this below doesn't
make sense to me. Supposedly the above just updates the dct counts. The
streamsNeeded
column is now column "G". I don't know what the code below even does. It
seems to be
still calculating the dct but I don't know what the relationship is to
Streams needed??
If you can shed some light on the rest it would help because as I said it is
only updating the counts
so I don't know what it is doing with the streams needed column.
application.calculate
set streams_needed = Intersect(range("H12:h64000"), ActiveSheet.UsedRange)
' streams_needed column should be G
For each C in Streams_needed
DCT= Application.worksheetfunction.sumif(streams_needed.offset(0,1),
c.offset(0,1) value, streams_needed)
' is this sumIf function summing up the streams needed column or the
column next to it?
If c.formula like "=sumif*" then
if C.Value >40 then
c.font.bold = true
c.font.colorindex = 3
' this c value is back to our cell range that starts out on the
column "c"
' should I change this to "d" or just change the offsets?
end if
end if
Next C
thanks very much if you read this to the end.
Any help will be greatly appreciated.
worksheet.
It is really a simple macro it just checks the value on the dctPcount.xls
sheet on column E, called number_of_dcts (if I insert the blank column it
makes it F).
Then it checks column D of the activesheet and if it is not the same it
updates it
(if I insert a blank column on D it moves it to E). At least that is what
it should do.
The problem is now I have to insert a blank column "B" on the activesheet
and a blank column E on the dct_count.xls sheet to get it to work presumably
because some columns have been moved and the offsets aren't right.
If you insert a blank column E
on the dct_count.xls and a blank column "B" on the active sheet. That seems
to keep all the offsets correct.
I tried changing all the columns to the correct column letter but there is
some macro that is adding an extra row and value and the counts aren't
updating so I'm missing something I think is probably on an offset value?
Its only a guess but perhaps they are working in the offsets instead of
hardwiring the columns because the output is needed in another module? Or
otherwise I don't know why they are doing it that way but as I said I changed
all the column letters to the right letter
and the macro doesn't work.
So here is the code again and I'll try to explain it.
The serviceGroupColumn is "I" but I don't see a reference to it in this sub.
That
was in the private declarations. This might not be important to this sub.
I'll add just the code below, but the variables are:
dim c as range
dim r as range
dim foundrange as range
dim dct as long
dim wb as workbook
dim dct_count_range as range
--------------code--------------
Set DCT_Count_Range =
Intersect(Workbooks("dct_count.xls").Sheets("dct_count").Range("D
"),Workbooks("dct_count.xls").Sheets("dct_count").UsedRange)
' This section gets the range of the column D which is the field that has
updated dct counts.
' This sheet dct_count.xls is the sheet compared with the active sheet.
' It is column D but it only works if it is column E as I mentioned.
For Each C in Intersect(Range("C12:C6400"), ActiveSheet.UsedRange)
DCT = 0
' I think what it is doing is initializing the variable DCT
' The range should be column D instead of C
DCT = Application.worksheetFunction.SumIf(DCT_Count_Range, C.Value,
DCT_Count_Range.Offset(0,2))
' I don't know what this offset is for, my column D is DCT the value I
want to update
' Offset 2 columns from C is E which must be why inserting a column in E
makes the macro work.
' Should I just change the offset to 1 without changing the column letters?
' That still doesn't explain why I need to insert a "B" column in the
dct_count.xls sheet?
Next C
If you have read this far the above seems most important however the rest of
this below doesn't
make sense to me. Supposedly the above just updates the dct counts. The
streamsNeeded
column is now column "G". I don't know what the code below even does. It
seems to be
still calculating the dct but I don't know what the relationship is to
Streams needed??
If you can shed some light on the rest it would help because as I said it is
only updating the counts
so I don't know what it is doing with the streams needed column.
application.calculate
set streams_needed = Intersect(range("H12:h64000"), ActiveSheet.UsedRange)
' streams_needed column should be G
For each C in Streams_needed
DCT= Application.worksheetfunction.sumif(streams_needed.offset(0,1),
c.offset(0,1) value, streams_needed)
' is this sumIf function summing up the streams needed column or the
column next to it?
If c.formula like "=sumif*" then
if C.Value >40 then
c.font.bold = true
c.font.colorindex = 3
' this c value is back to our cell range that starts out on the
column "c"
' should I change this to "d" or just change the offsets?
end if
end if
Next C
thanks very much if you read this to the end.
Any help will be greatly appreciated.