The following code
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Dim YrColNumber As Integer
Dim YrColAddress As String
Dim IntColNumber As Integer
Dim IntColAddress As String
Dim CycleNumber As Integer
CycleNumber = 1
YrColNumber = 4 + (CycleNumber - 1) * 7
YrColAddress = Split(Cells(, YrColNumber).Address, "$")(1)
IntColNumber = YrColNumber + 281
IntColAddress = Split(Cells(, IntColNumber).Address, "$")(1)
...</code>Works fine for YrColAddress ("D" in this case) but gives a runtime error 1004 "Application-defined or Object-defined error" for IntColAddress.
I have also tried declaring the ...ColNumber variables as long or variants - to no avail. Also I have tested with constant numbers <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">Split(Cells(, 500).Address, "$")(1)</code> and that does work. Its just variables it seems to not like.
Why would the same formula on similarly declared variables behave so differently. What am I doing wrong here and how can I fix it. If not, is this a known bug for this cell.address operation (where numbers > 200ish sent as variables lead to runtime errors)?
Any kind help on this will be immensely appreciated. Thanks in advance.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Dim YrColNumber As Integer
Dim YrColAddress As String
Dim IntColNumber As Integer
Dim IntColAddress As String
Dim CycleNumber As Integer
CycleNumber = 1
YrColNumber = 4 + (CycleNumber - 1) * 7
YrColAddress = Split(Cells(, YrColNumber).Address, "$")(1)
IntColNumber = YrColNumber + 281
IntColAddress = Split(Cells(, IntColNumber).Address, "$")(1)
...</code>Works fine for YrColAddress ("D" in this case) but gives a runtime error 1004 "Application-defined or Object-defined error" for IntColAddress.
I have also tried declaring the ...ColNumber variables as long or variants - to no avail. Also I have tested with constant numbers <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">Split(Cells(, 500).Address, "$")(1)</code> and that does work. Its just variables it seems to not like.
Why would the same formula on similarly declared variables behave so differently. What am I doing wrong here and how can I fix it. If not, is this a known bug for this cell.address operation (where numbers > 200ish sent as variables lead to runtime errors)?
Any kind help on this will be immensely appreciated. Thanks in advance.