Function exists prematurely

[brain]

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?

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

[Bob Phillips]

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" <brain.25hsqm_1143738603.653@excelforum-nospam.com> wrote in message
news:brain.25hsqm_1143738603.653@excelforum-nospam.com...
>
> 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
>

[brain]

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.

[NickHK]

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" <brain.25hsqm_1143738603.653@excelforum-nospam.com> wrote in message
news:brain.25hsqm_1143738603.653@excelforum-nospam.com...
>
> 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 Phillips]

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" <TungCheWah@Invalid.com> wrote in message
news:ebwAQ2IVGHA.1728@TK2MSFTNGP11.phx.gbl...
> 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" <brain.25hsqm_1143738603.653@excelforum-nospam.com> wrote in
message
> news:brain.25hsqm_1143738603.653@excelforum-nospam.com...
> >
> > 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
> >
>
>

[NickHK]

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

NickHK

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23BOS$$JVGHA.5148@TK2MSFTNGP12.phx.gbl...
> 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" <TungCheWah@Invalid.com> wrote in message
> news:ebwAQ2IVGHA.1728@TK2MSFTNGP11.phx.gbl...
> > 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" <brain.25hsqm_1143738603.653@excelforum-nospam.com> wrote in
> message
> > news:brain.25hsqm_1143738603.653@excelforum-nospam.com...
> > >
> > > 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 Phillips]

Hi Nick,

"NickHK" <TungCheWah@Invalid.com> wrote in message
news:uPeAbWKVGHA.1304@tk2msftngp13.phx.gbl...

> Yes, it works, surprisingly I think.

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

[NickHK]

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

NickHK

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:ulnhd1KVGHA.5364@tk2msftngp13.phx.gbl...
> Hi Nick,
>
> "NickHK" <TungCheWah@Invalid.com> wrote in message
> news:uPeAbWKVGHA.1304@tk2msftngp13.phx.gbl...
>
> > Yes, it works, surprisingly I think.
>
> It surprised me, my first thought was that that was the problem.
>
>

[brain]

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


[edit] changing columns to bcolumns (I'm not very creative) solved the problem. Thanks for the help! [/edit]

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.

[Bob Phillips]

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" <brain.25jf2n_1143814204.9334@excelforum-nospam.com> wrote in
message news:brain.25jf2n_1143814204.9334@excelforum-nospam.com...
>
> 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
>