I'm having a problem with the following function. For some reason, th
function returns before the first MsgBox call. It succesfully delete
the column, but it exits right after. Any help?

Code
-------------------

Sub main()
Dim numRates As Integer
Dim numDates As Integer

Const RateOffset As Integer = 5
Const DateOffset As Integer = 3

Dim columns As Range
Dim ratesRange As Range

numRates = Range("IntTimeSeriesRates").Value
numDates = Range("IntTimeSeriesDates").Value

Set columns = Range(Cells(RateOffset, DateOffset + numDates - 1), Cells(RateOffset + numDates, DateOffset + numDates - 1))
columns.Delete

MsgBox "Hello1"
Set columns = Range(Cells(RateOffset, DateOffset), Cells(RateOffset + numRates, DateOffset))
columns.Insert shift:=xlToRight

MsgBox "Hello2"
End Sub

-------------------

--
brai
-----------------------------------------------------------------------
brain's Profile: http://www.excelforum.com/member.php...fo&userid=3299
View this thread: http://www.excelforum.com/showthread.php?threadid=52818

That suggests that the variable columns is getting an invalid value. Step
through the code and check the value of all the variables in the line that
sets it.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"brain" wrote in message
...

I'm having a problem with the following function. For some reason, the
function returns before the first MsgBox call. It succesfully deletes
the column, but it exits right after. Any help?

Code:
--------------------

Sub main()
Dim numRates As Integer
Dim numDates As Integer

Const RateOffset As Integer = 5
Const DateOffset As Integer = 3

Dim columns As Range
Dim ratesRange As Range

numRates = Range("IntTimeSeriesRates").Value
numDates = Range("IntTimeSeriesDates").Value

Set columns = Range(Cells(RateOffset, DateOffset + numDates - 1),
Cells(RateOffset + numDates, DateOffset + numDates - 1))
columns.Delete

MsgBox "Hello1"
Set columns = Range(Cells(RateOffset, DateOffset), Cells(RateOffset +
numRates, DateOffset))
columns.Insert shift:=xlToRight

MsgBox "Hello2"
End Sub

--------------------


--
brain
------------------------------------------------------------------------
brain's Profile:
http://www.excelforum.com/member.php...o&userid=32991
View this thread: http://www.excelforum.com/showthread...hreadid=528185

There was a problem with one of the variables, but nothing that was
invalid. I fixed it and I'm still getting the problem. I can change
the font of- columns -and it works, but if I try to delete it or clear
it, the function exits.


--
brain
------------------------------------------------------------------------
brain's Profile: http://www.excelforum.com/member.php...o&userid=32991
View this thread: http://www.excelforum.com/showthread...hreadid=528185

brain,
How do think this routine works ?
Private Sub CountAllcolumnsOnWS()
MsgBox Columns.Count
End Sub

Hint: How many columns do you think Excel is trying to delete ?

NickHK

"brain" wrote in message
...

I'm having a problem with the following function. For some reason, the
function returns before the first MsgBox call. It succesfully deletes
the column, but it exits right after. Any help?

Code:
--------------------

Sub main()
Dim numRates As Integer
Dim numDates As Integer

Const RateOffset As Integer = 5
Const DateOffset As Integer = 3

Dim columns As Range
Dim ratesRange As Range

numRates = Range("IntTimeSeriesRates").Value
numDates = Range("IntTimeSeriesDates").Value

Set columns = Range(Cells(RateOffset, DateOffset + numDates - 1),
Cells(RateOffset + numDates, DateOffset + numDates - 1))
columns.Delete

MsgBox "Hello1"
Set columns = Range(Cells(RateOffset, DateOffset), Cells(RateOffset +
numRates, DateOffset))
columns.Insert shift:=xlToRight

MsgBox "Hello2"
End Sub

--------------------


--
brain
------------------------------------------------------------------------
brain's Profile:
http://www.excelforum.com/member.php...o&userid=32991
View this thread: http://www.excelforum.com/showthread...hreadid=528185

I don't think that is it, at least it is not in my testing. The code defines
a variable called columns. Now that may not be a practice that you or I
would endorse, but it does seem to 'work' as he thinks. It certainly
wouldn't hurt though if he changed the name and tried again.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"NickHK" wrote in message
...
brain,
How do think this routine works ?
Private Sub CountAllcolumnsOnWS()
MsgBox Columns.Count
End Sub

Hint: How many columns do you think Excel is trying to delete ?

NickHK

"brain" wrote in
message
...

I'm having a problem with the following function. For some reason, the
function returns before the first MsgBox call. It succesfully deletes
the column, but it exits right after. Any help?

Code:
--------------------

Sub main()
Dim numRates As Integer
Dim numDates As Integer

Const RateOffset As Integer = 5
Const DateOffset As Integer = 3

Dim columns As Range
Dim ratesRange As Range

numRates = Range("IntTimeSeriesRates").Value
numDates = Range("IntTimeSeriesDates").Value

Set columns = Range(Cells(RateOffset, DateOffset + numDates - 1),
Cells(RateOffset + numDates, DateOffset + numDates - 1))
columns.Delete

MsgBox "Hello1"
Set columns = Range(Cells(RateOffset, DateOffset), Cells(RateOffset +
numRates, DateOffset))
columns.Insert shift:=xlToRight

MsgBox "Hello2"
End Sub

--------------------


--
brain
------------------------------------------------------------------------
brain's Profile:
http://www.excelforum.com/member.php...o&userid=32991
View this thread:
http://www.excelforum.com/showthread...hreadid=528185

Bob,
Yes, it works, surprisingly I think.
So I guess it's your suggetsion of the an invalid value for columns.

NickHK

"Bob Phillips" wrote in message
...
I don't think that is it, at least it is not in my testing. The code
defines
a variable called columns. Now that may not be a practice that you or I
would endorse, but it does seem to 'work' as he thinks. It certainly
wouldn't hurt though if he changed the name and tried again.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"NickHK" wrote in message
...
brain,
How do think this routine works ?
Private Sub CountAllcolumnsOnWS()
MsgBox Columns.Count
End Sub

Hint: How many columns do you think Excel is trying to delete ?

NickHK

"brain" wrote in
message
...

I'm having a problem with the following function. For some reason,
the
function returns before the first MsgBox call. It succesfully deletes
the column, but it exits right after. Any help?

Code:
--------------------

Sub main()
Dim numRates As Integer
Dim numDates As Integer

Const RateOffset As Integer = 5
Const DateOffset As Integer = 3

Dim columns As Range
Dim ratesRange As Range

numRates = Range("IntTimeSeriesRates").Value
numDates = Range("IntTimeSeriesDates").Value

Set columns = Range(Cells(RateOffset, DateOffset + numDates - 1),
Cells(RateOffset + numDates, DateOffset + numDates - 1))
columns.Delete

MsgBox "Hello1"
Set columns = Range(Cells(RateOffset, DateOffset), Cells(RateOffset
+
numRates, DateOffset))
columns.Insert shift:=xlToRight

MsgBox "Hello2"
End Sub

--------------------


--
brain

------------------------------------------------------------------------
brain's Profile:
http://www.excelforum.com/member.php...o&userid=32991
View this thread:
http://www.excelforum.com/showthread...hreadid=528185

Hi Nick,

"NickHK" wrote in message
...

Yes, it works, surprisingly I think.

It surprised me, my first thought was that that was the problem.

Bob,
Another indication I should test what I write before posting.

NickHK

"Bob Phillips" wrote in message
...
Hi Nick,

"NickHK" wrote in message
...

Yes, it works, surprisingly I think.

It surprised me, my first thought was that that was the problem.

Nick and Bob, thanks for your time and comments.

Looking over the comments that you have both made it occurs to me that
columns is a bad variable name as it is used for other things as well.
I will make a very simple example with a different variable name and
check if it works.

In my spreadsheet, I have a row of dates. Under each date, is a column
of data. What I want to do is:
1) Delete the right most column from the range
2) Shift all of the columns over by one
3) Insert a new date on the left side

Bob Phillips Wrote:
I don't think that is it, at least it is not in my testing. The code
defines
a variable called columns. Now that may not be a practice that you or
I
would endorse, but it does seem to 'work' as he thinks. It certainly
wouldn't hurt though if he changed the name and tried again.



--
brain
------------------------------------------------------------------------
brain's Profile: http://www.excelforum.com/member.php...o&userid=32991
View this thread: http://www.excelforum.com/showthread...hreadid=528185

That makes a lot of sense. Try it and post back if you still have problems.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"brain" wrote in
message ...

Nick and Bob, thanks for your time and comments.

Looking over the comments that you have both made it occurs to me that
columns is a bad variable name as it is used for other things as well.
I will make a very simple example with a different variable name and
check if it works.

In my spreadsheet, I have a row of dates. Under each date, is a column
of data. What I want to do is:
1) Delete the right most column from the range
2) Shift all of the columns over by one
3) Insert a new date on the left side

Bob Phillips Wrote:
I don't think that is it, at least it is not in my testing. The code
defines
a variable called columns. Now that may not be a practice that you or
I
would endorse, but it does seem to 'work' as he thinks. It certainly
wouldn't hurt though if he changed the name and tried again.



--
brain
------------------------------------------------------------------------
brain's Profile:
http://www.excelforum.com/member.php...o&userid=32991
View this thread: http://www.excelforum.com/showthread...hreadid=528185