I have a script I need to understand so I'm starting from the top with the
delcarations. added a column and now the report doesn't print but I don't
know which column was added because I don't have the original sheet.
-----declarations-------
Option Explicit
Private Const ServiceGroupColumn as string ="$I"
Private Const FirstDataRow as Integer =12
Private Const FirstDataCell As string = "a12"
Private Const DCTColumnOffset As Integer = 4
PRivate Const ServiceGroupColumnRelative As String = "I"
Private Const StreamsColumnOffset as Integer = 1
Private const FreezePaneCell As String = "A12"
Private Const HomesColumnOffset as Integer = 5

One question I have is why is the ServiceGroupColumn $I and the
ServiceGroupColumnRelative also I?

The other question is Why is the DCTColumnOffset =4 mean? Why does it say
offset? The DCTCount Column is "D" . THe streamscolumn offset is "G". Is
that the problem with the printing? The ServiceGroupColumn is "H". The
Homes column is "C". I'm not quite sure why they don't just make the columns
a letter instead of calling it the offset so I want to be sure.

The first data row is 12 so that checks out.
If you can help me get this printed Monday would be a happy day.

tia,

First, I'm not sure why it doesn't print with just the addition of a column.
I could understand if it printed improperly, but not printing at all? Have
you checked out File | Page Setup | [Sheet] tab and looked to see what's set
for the Print Range?

It's hard to be absolutely definitive in my interpretation of these
constants without seeing the code they're being used in. But I'll give it a
try:

I suspect that ServiceGroupColumn ($I) is being used to create formulas to
reference things in that column and they want that formula to refer to column
I (the dollar symbol makes it unchanging even if columns are inserted/deleted
to the left of I - which can create problems with columns inserted/deleted
after the formula is put in place). Along that same line, I think
ServiceGroupColumnRelative value is probably used somewhere to calculate an
offset either to or from that column for some other actions.

Regarding "Offset" - these values are (most likely) used to determine a
relative position from one column (most likely) or row to another related to
it. Consider this statement:
X = Range("I1").Offset(0, 4)
That actually says 'give me the value in the cell 4 columns to the right of
column I on same row; i.e. the value at M1.

I suspect, and I stress suspect, that the offset values are based on column
offsets from column A. Why? Because of the FirstDataCell value pointing to
A12. It almost begins to look like someone may have deleted a column or
perhaps moved it from somewhere early (column B, C or D) to a position
farther to the righ on the sheet. But it is possible that one or more of the
....Offset constants are related to different columns.

Personally I think it's rather bad form to set up Offset values as constants
unless you warn the user about the potentially disastrous results of adding
or deleting columns/rows without changing the values. It is better to
calculate those at run time. Consider this (a simplistic example, but it
will do):
Const BaseCell = "A1"
Const HomeEntryColumn = "C"
Const HomeEntryOffset = 2

Now you try to put something into the Home Entry Column using an offset as:
Range(BaseCell).Offset(0, HomeEntryOffset) = "Ranch, 1940 sq ft"
it works in the original - that gets put into column C. But now you add a
new column C in front of the old column C, so HomeEntryColumn *should* get
changed to D and the HomeEntryOffset constant *should* be changed to 3, but
we don't know that should be done, so when
Range(BaseCell).Offset(0, HomeEntryOffset) = "Ranch, 1940 sq ft"
is executed, the entry still goes into column C instead of D where it really
belongs after our change.

A better way to have done this would be
#1 inform the end user that they need to change Const declarations for
columns used.
#2 - do this kind of thing in the actual running code:

Sub SomeCode()
Dim HomeEntryOffset As Long ' could be Integer, but Long is safe

HomeEntryOffset = Range(HomeEntryColumn & "1").Column - _
Range(BaseCell).Column

so when Home entry column is C, HomeEntryOffset is 2, and when we insert
that new column AND IF we change Const HomeEntryColumn to = "D" vs "C" then
the offset value self-repairs at runtime.

Again, without seeing the code that uses these Const values, it's difficult
to be certain in my interpretation. For example, you say that DCTCount
column is D while the DCTColumnOffset value is set to 4. That does seem odd,
since an offset from column A to column D would be 3, not 4 (and that's part
of why I guessed earlier that a column may have been deleted or moved out of
the BCD group of columns). If DCTCount column had been E earlier, it would
make sense.
"Janis" wrote:

I have a script I need to understand so I'm starting from the top with the
delcarations. added a column and now the report doesn't print but I don't
know which column was added because I don't have the original sheet.
-----declarations-------
Option Explicit
Private Const ServiceGroupColumn as string ="$I"
Private Const FirstDataRow as Integer =12
Private Const FirstDataCell As string = "a12"
Private Const DCTColumnOffset As Integer = 4
PRivate Const ServiceGroupColumnRelative As String = "I"
Private Const StreamsColumnOffset as Integer = 1
Private const FreezePaneCell As String = "A12"
Private Const HomesColumnOffset as Integer = 5

One question I have is why is the ServiceGroupColumn $I and the
ServiceGroupColumnRelative also I?

The other question is Why is the DCTColumnOffset =4 mean? Why does it say
offset? The DCTCount Column is "D" . THe streamscolumn offset is "G". Is
that the problem with the printing? The ServiceGroupColumn is "H". The
Homes column is "C". I'm not quite sure why they don't just make the columns
a letter instead of calling it the offset so I want to be sure.

The first data row is 12 so that checks out.
If you can help me get this printed Monday would be a happy day.

tia,

.. it does print but it is skewed. it could also be in a sheet that gets
compared with this one. In that the dct count that updates this one is in
column E. thanks,

"JLatham" wrote:

First, I'm not sure why it doesn't print with just the addition of a column.
I could understand if it printed improperly, but not printing at all? Have
you checked out File | Page Setup | [Sheet] tab and looked to see what's set
for the Print Range?

It's hard to be absolutely definitive in my interpretation of these
constants without seeing the code they're being used in. But I'll give it a
try:

I suspect that ServiceGroupColumn ($I) is being used to create formulas to
reference things in that column and they want that formula to refer to column
I (the dollar symbol makes it unchanging even if columns are inserted/deleted
to the left of I - which can create problems with columns inserted/deleted
after the formula is put in place). Along that same line, I think
ServiceGroupColumnRelative value is probably used somewhere to calculate an
offset either to or from that column for some other actions.

Regarding "Offset" - these values are (most likely) used to determine a
relative position from one column (most likely) or row to another related to
it. Consider this statement:
X = Range("I1").Offset(0, 4)
That actually says 'give me the value in the cell 4 columns to the right of
column I on same row; i.e. the value at M1.

I suspect, and I stress suspect, that the offset values are based on column
offsets from column A. Why? Because of the FirstDataCell value pointing to
A12. It almost begins to look like someone may have deleted a column or
perhaps moved it from somewhere early (column B, C or D) to a position
farther to the righ on the sheet. But it is possible that one or more of the
...Offset constants are related to different columns.

Personally I think it's rather bad form to set up Offset values as constants
unless you warn the user about the potentially disastrous results of adding
or deleting columns/rows without changing the values. It is better to
calculate those at run time. Consider this (a simplistic example, but it
will do):
Const BaseCell = "A1"
Const HomeEntryColumn = "C"
Const HomeEntryOffset = 2

Now you try to put something into the Home Entry Column using an offset as:
Range(BaseCell).Offset(0, HomeEntryOffset) = "Ranch, 1940 sq ft"
it works in the original - that gets put into column C. But now you add a
new column C in front of the old column C, so HomeEntryColumn *should* get
changed to D and the HomeEntryOffset constant *should* be changed to 3, but
we don't know that should be done, so when
Range(BaseCell).Offset(0, HomeEntryOffset) = "Ranch, 1940 sq ft"
is executed, the entry still goes into column C instead of D where it really
belongs after our change.

A better way to have done this would be
#1 inform the end user that they need to change Const declarations for
columns used.
#2 - do this kind of thing in the actual running code:

Sub SomeCode()
Dim HomeEntryOffset As Long ' could be Integer, but Long is safe

HomeEntryOffset = Range(HomeEntryColumn & "1").Column - _
Range(BaseCell).Column

so when Home entry column is C, HomeEntryOffset is 2, and when we insert
that new column AND IF we change Const HomeEntryColumn to = "D" vs "C" then
the offset value self-repairs at runtime.

Again, without seeing the code that uses these Const values, it's difficult
to be certain in my interpretation. For example, you say that DCTCount
column is D while the DCTColumnOffset value is set to 4. That does seem odd,
since an offset from column A to column D would be 3, not 4 (and that's part
of why I guessed earlier that a column may have been deleted or moved out of
the BCD group of columns). If DCTCount column had been E earlier, it would
make sense.
"Janis" wrote:

I have a script I need to understand so I'm starting from the top with the
delcarations. added a column and now the report doesn't print but I don't
know which column was added because I don't have the original sheet.
-----declarations-------
Option Explicit
Private Const ServiceGroupColumn as string ="$I"
Private Const FirstDataRow as Integer =12
Private Const FirstDataCell As string = "a12"
Private Const DCTColumnOffset As Integer = 4
PRivate Const ServiceGroupColumnRelative As String = "I"
Private Const StreamsColumnOffset as Integer = 1
Private const FreezePaneCell As String = "A12"
Private Const HomesColumnOffset as Integer = 5

One question I have is why is the ServiceGroupColumn $I and the
ServiceGroupColumnRelative also I?

The other question is Why is the DCTColumnOffset =4 mean? Why does it say
offset? The DCTCount Column is "D" . THe streamscolumn offset is "G". Is
that the problem with the printing? The ServiceGroupColumn is "H". The
Homes column is "C". I'm not quite sure why they don't just make the columns
a letter instead of calling it the offset so I want to be sure.

The first data row is 12 so that checks out.
If you can help me get this printed Monday would be a happy day.

tia,

What do you mean by skewed? If it's missing a column/row from an edge, that
probably means that the Print Range (see 1st paragraph of other post) is set
wrong.

If you mean some things are showing up in wrong columns, then maybe identify
a couple of "where they are --- where they should be" examples and maybe we
can figure this out.

"Janis" wrote:

. it does print but it is skewed. it could also be in a sheet that gets
compared with this one. In that the dct count that updates this one is in
column E. thanks,

"JLatham" wrote:

First, I'm not sure why it doesn't print with just the addition of a column.
I could understand if it printed improperly, but not printing at all? Have
you checked out File | Page Setup | [Sheet] tab and looked to see what's set
for the Print Range?

It's hard to be absolutely definitive in my interpretation of these
constants without seeing the code they're being used in. But I'll give it a
try:

I suspect that ServiceGroupColumn ($I) is being used to create formulas to
reference things in that column and they want that formula to refer to column
I (the dollar symbol makes it unchanging even if columns are inserted/deleted
to the left of I - which can create problems with columns inserted/deleted
after the formula is put in place). Along that same line, I think
ServiceGroupColumnRelative value is probably used somewhere to calculate an
offset either to or from that column for some other actions.

Regarding "Offset" - these values are (most likely) used to determine a
relative position from one column (most likely) or row to another related to
it. Consider this statement:
X = Range("I1").Offset(0, 4)
That actually says 'give me the value in the cell 4 columns to the right of
column I on same row; i.e. the value at M1.

I suspect, and I stress suspect, that the offset values are based on column
offsets from column A. Why? Because of the FirstDataCell value pointing to
A12. It almost begins to look like someone may have deleted a column or
perhaps moved it from somewhere early (column B, C or D) to a position
farther to the righ on the sheet. But it is possible that one or more of the
...Offset constants are related to different columns.

Personally I think it's rather bad form to set up Offset values as constants
unless you warn the user about the potentially disastrous results of adding
or deleting columns/rows without changing the values. It is better to
calculate those at run time. Consider this (a simplistic example, but it
will do):
Const BaseCell = "A1"
Const HomeEntryColumn = "C"
Const HomeEntryOffset = 2

Now you try to put something into the Home Entry Column using an offset as:
Range(BaseCell).Offset(0, HomeEntryOffset) = "Ranch, 1940 sq ft"
it works in the original - that gets put into column C. But now you add a
new column C in front of the old column C, so HomeEntryColumn *should* get
changed to D and the HomeEntryOffset constant *should* be changed to 3, but
we don't know that should be done, so when
Range(BaseCell).Offset(0, HomeEntryOffset) = "Ranch, 1940 sq ft"
is executed, the entry still goes into column C instead of D where it really
belongs after our change.

A better way to have done this would be
#1 inform the end user that they need to change Const declarations for
columns used.
#2 - do this kind of thing in the actual running code:

Sub SomeCode()
Dim HomeEntryOffset As Long ' could be Integer, but Long is safe

HomeEntryOffset = Range(HomeEntryColumn & "1").Column - _
Range(BaseCell).Column

so when Home entry column is C, HomeEntryOffset is 2, and when we insert
that new column AND IF we change Const HomeEntryColumn to = "D" vs "C" then
the offset value self-repairs at runtime.

Again, without seeing the code that uses these Const values, it's difficult
to be certain in my interpretation. For example, you say that DCTCount
column is D while the DCTColumnOffset value is set to 4. That does seem odd,
since an offset from column A to column D would be 3, not 4 (and that's part
of why I guessed earlier that a column may have been deleted or moved out of
the BCD group of columns). If DCTCount column had been E earlier, it would
make sense.
"Janis" wrote:

I have a script I need to understand so I'm starting from the top with the
delcarations. added a column and now the report doesn't print but I don't
know which column was added because I don't have the original sheet.
-----declarations-------
Option Explicit
Private Const ServiceGroupColumn as string ="$I"
Private Const FirstDataRow as Integer =12
Private Const FirstDataCell As string = "a12"
Private Const DCTColumnOffset As Integer = 4
PRivate Const ServiceGroupColumnRelative As String = "I"
Private Const StreamsColumnOffset as Integer = 1
Private const FreezePaneCell As String = "A12"
Private Const HomesColumnOffset as Integer = 5

One question I have is why is the ServiceGroupColumn $I and the
ServiceGroupColumnRelative also I?

The other question is Why is the DCTColumnOffset =4 mean? Why does it say
offset? The DCTCount Column is "D" . THe streamscolumn offset is "G". Is
that the problem with the printing? The ServiceGroupColumn is "H". The
Homes column is "C". I'm not quite sure why they don't just make the columns
a letter instead of calling it the offset so I want to be sure.

The first data row is 12 so that checks out.
If you can help me get this printed Monday would be a happy day.

tia,

Thanks for your explanation its still not clear where the problem lies.
There is a missing/extra column in sheetMain and also possibly in the other
sheet that is used to compare and update the one I'm working on I'll call it
sheetUpdate.

If I insert a column D to sheetMain and a column E to dct_count.xls the
macros work but it has to be fixed of course for theusers.
The numberOfDCT's is column E value on dct_count.xls. So if I insert a
column E then this column moves to F but as I said this causes the macros to
work.

Here is something that might help, the intersect range in the code, this
should be where it compares the two columns and updates the count. It isn't
very complicated really the code just the references:

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)

For each C in Intersect (range("c12:c64000"), ActiveSheet.UsedRange)
DCT= 0 ' resets count to zero

DCT = application.worksheetfunction.sumif(DCT_count_rang e, c.value,
dct_count_range.offset(0.2))
C.offset(0,2).value = dct

next C

Thanks for your help, I await your reply :-)

"Janis" wrote:

. it does print but it is skewed. it could also be in a sheet that gets
compared with this one. In that the dct count that updates this one is in
column E. thanks,

"JLatham" wrote:

First, I'm not sure why it doesn't print with just the addition of a column.
I could understand if it printed improperly, but not printing at all? Have
you checked out File | Page Setup | [Sheet] tab and looked to see what's set
for the Print Range?

It's hard to be absolutely definitive in my interpretation of these
constants without seeing the code they're being used in. But I'll give it a
try:

I suspect that ServiceGroupColumn ($I) is being used to create formulas to
reference things in that column and they want that formula to refer to column
I (the dollar symbol makes it unchanging even if columns are inserted/deleted
to the left of I - which can create problems with columns inserted/deleted
after the formula is put in place). Along that same line, I think
ServiceGroupColumnRelative value is probably used somewhere to calculate an
offset either to or from that column for some other actions.

Regarding "Offset" - these values are (most likely) used to determine a
relative position from one column (most likely) or row to another related to
it. Consider this statement:
X = Range("I1").Offset(0, 4)
That actually says 'give me the value in the cell 4 columns to the right of
column I on same row; i.e. the value at M1.

I suspect, and I stress suspect, that the offset values are based on column
offsets from column A. Why? Because of the FirstDataCell value pointing to
A12. It almost begins to look like someone may have deleted a column or
perhaps moved it from somewhere early (column B, C or D) to a position
farther to the righ on the sheet. But it is possible that one or more of the
...Offset constants are related to different columns.

Personally I think it's rather bad form to set up Offset values as constants
unless you warn the user about the potentially disastrous results of adding
or deleting columns/rows without changing the values. It is better to
calculate those at run time. Consider this (a simplistic example, but it
will do):
Const BaseCell = "A1"
Const HomeEntryColumn = "C"
Const HomeEntryOffset = 2

Now you try to put something into the Home Entry Column using an offset as:
Range(BaseCell).Offset(0, HomeEntryOffset) = "Ranch, 1940 sq ft"
it works in the original - that gets put into column C. But now you add a
new column C in front of the old column C, so HomeEntryColumn *should* get
changed to D and the HomeEntryOffset constant *should* be changed to 3, but
we don't know that should be done, so when
Range(BaseCell).Offset(0, HomeEntryOffset) = "Ranch, 1940 sq ft"
is executed, the entry still goes into column C instead of D where it really
belongs after our change.

A better way to have done this would be
#1 inform the end user that they need to change Const declarations for
columns used.
#2 - do this kind of thing in the actual running code:

Sub SomeCode()
Dim HomeEntryOffset As Long ' could be Integer, but Long is safe

HomeEntryOffset = Range(HomeEntryColumn & "1").Column - _
Range(BaseCell).Column

so when Home entry column is C, HomeEntryOffset is 2, and when we insert
that new column AND IF we change Const HomeEntryColumn to = "D" vs "C" then
the offset value self-repairs at runtime.

Again, without seeing the code that uses these Const values, it's difficult
to be certain in my interpretation. For example, you say that DCTCount
column is D while the DCTColumnOffset value is set to 4. That does seem odd,
since an offset from column A to column D would be 3, not 4 (and that's part
of why I guessed earlier that a column may have been deleted or moved out of
the BCD group of columns). If DCTCount column had been E earlier, it would
make sense.
"Janis" wrote:

I have a script I need to understand so I'm starting from the top with the
delcarations. added a column and now the report doesn't print but I don't
know which column was added because I don't have the original sheet.
-----declarations-------
Option Explicit
Private Const ServiceGroupColumn as string ="$I"
Private Const FirstDataRow as Integer =12
Private Const FirstDataCell As string = "a12"
Private Const DCTColumnOffset As Integer = 4
PRivate Const ServiceGroupColumnRelative As String = "I"
Private Const StreamsColumnOffset as Integer = 1
Private const FreezePaneCell As String = "A12"
Private Const HomesColumnOffset as Integer = 5

One question I have is why is the ServiceGroupColumn $I and the
ServiceGroupColumnRelative also I?

The other question is Why is the DCTColumnOffset =4 mean? Why does it say
offset? The DCTCount Column is "D" . THe streamscolumn offset is "G". Is
that the problem with the printing? The ServiceGroupColumn is "H". The
Homes column is "C". I'm not quite sure why they don't just make the columns
a letter instead of calling it the offset so I want to be sure.

The first data row is 12 so that checks out.
If you can help me get this printed Monday would be a happy day.

tia,

Here is one other declaration that may be useful after your explanation:

const FirstColumnRelative As String "A"
const numberOfColumns as Integer 52

Also, the serviceGroupColumn should be I but it is currently "H". That is
why when I add a column B it moves it over and it works? Also I counted the
number of columns and it is 42 not 52?

I noticed that these are declarations for the whole module not just my
subprocedure? Most of these aren't called in the sub but it probably gives
the orientation of how it should be. For the sub procedu

C as Range
R as Range
FoundRange as Range
DCT as Long
Wb as workbook
DCT_Count_Range as Range
Streams_needed as Range

Set Streams_needed = intersect(range("h12:h6400"), activesheet.usedrange)

The Streams needed column is currently "G"


After the code for the Range comparison in my previous e-mail there is one
place where it asks for the streams_needed

Hope I haven't confused you.
Thanks,


"JLatham" wrote:

What do you mean by skewed? If it's missing a column/row from an edge, that
probably means that the Print Range (see 1st paragraph of other post) is set
wrong.

If you mean some things are showing up in wrong columns, then maybe identify
a couple of "where they are --- where they should be" examples and maybe we
can figure this out.

"Janis" wrote:

. it does print but it is skewed. it could also be in a sheet that gets
compared with this one. In that the dct count that updates this one is in
column E. thanks,

"JLatham" wrote:

First, I'm not sure why it doesn't print with just the addition of a column.
I could understand if it printed improperly, but not printing at all? Have
you checked out File | Page Setup | [Sheet] tab and looked to see what's set
for the Print Range?

It's hard to be absolutely definitive in my interpretation of these
constants without seeing the code they're being used in. But I'll give it a
try:

I suspect that ServiceGroupColumn ($I) is being used to create formulas to
reference things in that column and they want that formula to refer to column
I (the dollar symbol makes it unchanging even if columns are inserted/deleted
to the left of I - which can create problems with columns inserted/deleted
after the formula is put in place). Along that same line, I think
ServiceGroupColumnRelative value is probably used somewhere to calculate an
offset either to or from that column for some other actions.

Regarding "Offset" - these values are (most likely) used to determine a
relative position from one column (most likely) or row to another related to
it. Consider this statement:
X = Range("I1").Offset(0, 4)
That actually says 'give me the value in the cell 4 columns to the right of
column I on same row; i.e. the value at M1.

I suspect, and I stress suspect, that the offset values are based on column
offsets from column A. Why? Because of the FirstDataCell value pointing to
A12. It almost begins to look like someone may have deleted a column or
perhaps moved it from somewhere early (column B, C or D) to a position
farther to the righ on the sheet. But it is possible that one or more of the
...Offset constants are related to different columns.

Personally I think it's rather bad form to set up Offset values as constants
unless you warn the user about the potentially disastrous results of adding
or deleting columns/rows without changing the values. It is better to
calculate those at run time. Consider this (a simplistic example, but it
will do):
Const BaseCell = "A1"
Const HomeEntryColumn = "C"
Const HomeEntryOffset = 2

Now you try to put something into the Home Entry Column using an offset as:
Range(BaseCell).Offset(0, HomeEntryOffset) = "Ranch, 1940 sq ft"
it works in the original - that gets put into column C. But now you add a
new column C in front of the old column C, so HomeEntryColumn *should* get
changed to D and the HomeEntryOffset constant *should* be changed to 3, but
we don't know that should be done, so when
Range(BaseCell).Offset(0, HomeEntryOffset) = "Ranch, 1940 sq ft"
is executed, the entry still goes into column C instead of D where it really
belongs after our change.

A better way to have done this would be
#1 inform the end user that they need to change Const declarations for
columns used.
#2 - do this kind of thing in the actual running code:

Sub SomeCode()
Dim HomeEntryOffset As Long ' could be Integer, but Long is safe

HomeEntryOffset = Range(HomeEntryColumn & "1").Column - _
Range(BaseCell).Column

so when Home entry column is C, HomeEntryOffset is 2, and when we insert
that new column AND IF we change Const HomeEntryColumn to = "D" vs "C" then
the offset value self-repairs at runtime.

Again, without seeing the code that uses these Const values, it's difficult
to be certain in my interpretation. For example, you say that DCTCount
column is D while the DCTColumnOffset value is set to 4. That does seem odd,
since an offset from column A to column D would be 3, not 4 (and that's part
of why I guessed earlier that a column may have been deleted or moved out of
the BCD group of columns). If DCTCount column had been E earlier, it would
make sense.
"Janis" wrote:

I have a script I need to understand so I'm starting from the top with the
delcarations. added a column and now the report doesn't print but I don't
know which column was added because I don't have the original sheet.
-----declarations-------
Option Explicit
Private Const ServiceGroupColumn as string ="$I"
Private Const FirstDataRow as Integer =12
Private Const FirstDataCell As string = "a12"
Private Const DCTColumnOffset As Integer = 4
PRivate Const ServiceGroupColumnRelative As String = "I"
Private Const StreamsColumnOffset as Integer = 1
Private const FreezePaneCell As String = "A12"
Private Const HomesColumnOffset as Integer = 5

One question I have is why is the ServiceGroupColumn $I and the
ServiceGroupColumnRelative also I?

The other question is Why is the DCTColumnOffset =4 mean? Why does it say
offset? The DCTCount Column is "D" . THe streamscolumn offset is "G". Is
that the problem with the printing? The ServiceGroupColumn is "H". The
Homes column is "C". I'm not quite sure why they don't just make the columns
a letter instead of calling it the offset so I want to be sure.

The first data row is 12 so that checks out.
If you can help me get this printed Monday would be a happy day.

tia,

One other thing, I tried changing the StreamsNeeded range to G instead of H
and it didn't work. It added the DCT reset count field to 0 and added this
as a new row under every other row. It might be doing the recount but it
definitely adds an unneeded row after every other row.
please help.

"JLatham" wrote:

What do you mean by skewed? If it's missing a column/row from an edge, that
probably means that the Print Range (see 1st paragraph of other post) is set
wrong.

If you mean some things are showing up in wrong columns, then maybe identify
a couple of "where they are --- where they should be" examples and maybe we
can figure this out.

"Janis" wrote:

. it does print but it is skewed. it could also be in a sheet that gets
compared with this one. In that the dct count that updates this one is in
column E. thanks,

"JLatham" wrote:

First, I'm not sure why it doesn't print with just the addition of a column.
I could understand if it printed improperly, but not printing at all? Have
you checked out File | Page Setup | [Sheet] tab and looked to see what's set
for the Print Range?

It's hard to be absolutely definitive in my interpretation of these
constants without seeing the code they're being used in. But I'll give it a
try:

I suspect that ServiceGroupColumn ($I) is being used to create formulas to
reference things in that column and they want that formula to refer to column
I (the dollar symbol makes it unchanging even if columns are inserted/deleted
to the left of I - which can create problems with columns inserted/deleted
after the formula is put in place). Along that same line, I think
ServiceGroupColumnRelative value is probably used somewhere to calculate an
offset either to or from that column for some other actions.

Regarding "Offset" - these values are (most likely) used to determine a
relative position from one column (most likely) or row to another related to
it. Consider this statement:
X = Range("I1").Offset(0, 4)
That actually says 'give me the value in the cell 4 columns to the right of
column I on same row; i.e. the value at M1.

I suspect, and I stress suspect, that the offset values are based on column
offsets from column A. Why? Because of the FirstDataCell value pointing to
A12. It almost begins to look like someone may have deleted a column or
perhaps moved it from somewhere early (column B, C or D) to a position
farther to the righ on the sheet. But it is possible that one or more of the
...Offset constants are related to different columns.

Personally I think it's rather bad form to set up Offset values as constants
unless you warn the user about the potentially disastrous results of adding
or deleting columns/rows without changing the values. It is better to
calculate those at run time. Consider this (a simplistic example, but it
will do):
Const BaseCell = "A1"
Const HomeEntryColumn = "C"
Const HomeEntryOffset = 2

Now you try to put something into the Home Entry Column using an offset as:
Range(BaseCell).Offset(0, HomeEntryOffset) = "Ranch, 1940 sq ft"
it works in the original - that gets put into column C. But now you add a
new column C in front of the old column C, so HomeEntryColumn *should* get
changed to D and the HomeEntryOffset constant *should* be changed to 3, but
we don't know that should be done, so when
Range(BaseCell).Offset(0, HomeEntryOffset) = "Ranch, 1940 sq ft"
is executed, the entry still goes into column C instead of D where it really
belongs after our change.

A better way to have done this would be
#1 inform the end user that they need to change Const declarations for
columns used.
#2 - do this kind of thing in the actual running code:

Sub SomeCode()
Dim HomeEntryOffset As Long ' could be Integer, but Long is safe

HomeEntryOffset = Range(HomeEntryColumn & "1").Column - _
Range(BaseCell).Column

so when Home entry column is C, HomeEntryOffset is 2, and when we insert
that new column AND IF we change Const HomeEntryColumn to = "D" vs "C" then
the offset value self-repairs at runtime.

Again, without seeing the code that uses these Const values, it's difficult
to be certain in my interpretation. For example, you say that DCTCount
column is D while the DCTColumnOffset value is set to 4. That does seem odd,
since an offset from column A to column D would be 3, not 4 (and that's part
of why I guessed earlier that a column may have been deleted or moved out of
the BCD group of columns). If DCTCount column had been E earlier, it would
make sense.
"Janis" wrote:

I have a script I need to understand so I'm starting from the top with the
delcarations. added a column and now the report doesn't print but I don't
know which column was added because I don't have the original sheet.
-----declarations-------
Option Explicit
Private Const ServiceGroupColumn as string ="$I"
Private Const FirstDataRow as Integer =12
Private Const FirstDataCell As string = "a12"
Private Const DCTColumnOffset As Integer = 4
PRivate Const ServiceGroupColumnRelative As String = "I"
Private Const StreamsColumnOffset as Integer = 1
Private const FreezePaneCell As String = "A12"
Private Const HomesColumnOffset as Integer = 5

One question I have is why is the ServiceGroupColumn $I and the
ServiceGroupColumnRelative also I?

The other question is Why is the DCTColumnOffset =4 mean? Why does it say
offset? The DCTCount Column is "D" . THe streamscolumn offset is "G". Is
that the problem with the printing? The ServiceGroupColumn is "H". The
Homes column is "C". I'm not quite sure why they don't just make the columns
a letter instead of calling it the offset so I want to be sure.

The first data row is 12 so that checks out.
If you can help me get this printed Monday would be a happy day.

tia,

The bits and pieces from hither and yon, and now to find out that 2 workbooks
are involved? is making it all terribly difficult. Any possibility of you
attaching the 2(?) books to an email and sending it to me to examine as one
cohesive entity?

HelpFrom @ jlathamsite.com (without spaces) will get it to me, if you can.

"Janis" wrote:

Thanks for your explanation its still not clear where the problem lies.
There is a missing/extra column in sheetMain and also possibly in the other
sheet that is used to compare and update the one I'm working on I'll call it
sheetUpdate.

If I insert a column D to sheetMain and a column E to dct_count.xls the
macros work but it has to be fixed of course for theusers.
The numberOfDCT's is column E value on dct_count.xls. So if I insert a
column E then this column moves to F but as I said this causes the macros to
work.

Here is something that might help, the intersect range in the code, this
should be where it compares the two columns and updates the count. It isn't
very complicated really the code just the references:

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)

For each C in Intersect (range("c12:c64000"), ActiveSheet.UsedRange)
DCT= 0 ' resets count to zero

DCT = application.worksheetfunction.sumif(DCT_count_rang e, c.value,
dct_count_range.offset(0.2))
C.offset(0,2).value = dct

next C

Thanks for your help, I await your reply :-)

"Janis" wrote:

. it does print but it is skewed. it could also be in a sheet that gets
compared with this one. In that the dct count that updates this one is in
column E. thanks,

"JLatham" wrote:

First, I'm not sure why it doesn't print with just the addition of a column.
I could understand if it printed improperly, but not printing at all? Have
you checked out File | Page Setup | [Sheet] tab and looked to see what's set
for the Print Range?

It's hard to be absolutely definitive in my interpretation of these
constants without seeing the code they're being used in. But I'll give it a
try:

I suspect that ServiceGroupColumn ($I) is being used to create formulas to
reference things in that column and they want that formula to refer to column
I (the dollar symbol makes it unchanging even if columns are inserted/deleted
to the left of I - which can create problems with columns inserted/deleted
after the formula is put in place). Along that same line, I think
ServiceGroupColumnRelative value is probably used somewhere to calculate an
offset either to or from that column for some other actions.

Regarding "Offset" - these values are (most likely) used to determine a
relative position from one column (most likely) or row to another related to
it. Consider this statement:
X = Range("I1").Offset(0, 4)
That actually says 'give me the value in the cell 4 columns to the right of
column I on same row; i.e. the value at M1.

I suspect, and I stress suspect, that the offset values are based on column
offsets from column A. Why? Because of the FirstDataCell value pointing to
A12. It almost begins to look like someone may have deleted a column or
perhaps moved it from somewhere early (column B, C or D) to a position
farther to the righ on the sheet. But it is possible that one or more of the
...Offset constants are related to different columns.

Personally I think it's rather bad form to set up Offset values as constants
unless you warn the user about the potentially disastrous results of adding
or deleting columns/rows without changing the values. It is better to
calculate those at run time. Consider this (a simplistic example, but it
will do):
Const BaseCell = "A1"
Const HomeEntryColumn = "C"
Const HomeEntryOffset = 2

Now you try to put something into the Home Entry Column using an offset as:
Range(BaseCell).Offset(0, HomeEntryOffset) = "Ranch, 1940 sq ft"
it works in the original - that gets put into column C. But now you add a
new column C in front of the old column C, so HomeEntryColumn *should* get
changed to D and the HomeEntryOffset constant *should* be changed to 3, but
we don't know that should be done, so when
Range(BaseCell).Offset(0, HomeEntryOffset) = "Ranch, 1940 sq ft"
is executed, the entry still goes into column C instead of D where it really
belongs after our change.

A better way to have done this would be
#1 inform the end user that they need to change Const declarations for
columns used.
#2 - do this kind of thing in the actual running code:

Sub SomeCode()
Dim HomeEntryOffset As Long ' could be Integer, but Long is safe

HomeEntryOffset = Range(HomeEntryColumn & "1").Column - _
Range(BaseCell).Column

so when Home entry column is C, HomeEntryOffset is 2, and when we insert
that new column AND IF we change Const HomeEntryColumn to = "D" vs "C" then
the offset value self-repairs at runtime.

Again, without seeing the code that uses these Const values, it's difficult
to be certain in my interpretation. For example, you say that DCTCount
column is D while the DCTColumnOffset value is set to 4. That does seem odd,
since an offset from column A to column D would be 3, not 4 (and that's part
of why I guessed earlier that a column may have been deleted or moved out of
the BCD group of columns). If DCTCount column had been E earlier, it would
make sense.
"Janis" wrote:

I have a script I need to understand so I'm starting from the top with the
delcarations. added a column and now the report doesn't print but I don't
know which column was added because I don't have the original sheet.
-----declarations-------
Option Explicit
Private Const ServiceGroupColumn as string ="$I"
Private Const FirstDataRow as Integer =12
Private Const FirstDataCell As string = "a12"
Private Const DCTColumnOffset As Integer = 4
PRivate Const ServiceGroupColumnRelative As String = "I"
Private Const StreamsColumnOffset as Integer = 1
Private const FreezePaneCell As String = "A12"
Private Const HomesColumnOffset as Integer = 5

One question I have is why is the ServiceGroupColumn $I and the
ServiceGroupColumnRelative also I?

The other question is Why is the DCTColumnOffset =4 mean? Why does it say
offset? The DCTCount Column is "D" . THe streamscolumn offset is "G". Is
that the problem with the printing? The ServiceGroupColumn is "H". The
Homes column is "C". I'm not quite sure why they don't just make the columns
a letter instead of calling it the offset so I want to be sure.

The first data row is 12 so that checks out.
If you can help me get this printed Monday would be a happy day.

tia,