VBA inserts columns then doesn't continue to other steps

  • Thread starter Thread starter Nancy
  • Start date Start date
N

Nancy

In an Excel spreadsheet of data I would like to do the following:

1. Insert 4 columns to the left of column N
2. Add titles to each of the four new columns
3. Add formulas to each of the four new columns in the third row
4. Copy the formulas down the rows thereby filling in the columns
with data

I have the following coding:

For Step 1:
Sub InsertColumns()

Columns("N:Q").Insert

End Sub

For Step 2-4:
Sub InsertFormulas()

Dim Lrow As Long
Dim CopyRange As Range

' Add Titles
Range("Q3").Select
ActiveCell.FormulaR1C1 = "Line Temp"
Range("P3").Select
ActiveCell.FormulaR1C1 = "Line"
Range("O3").Select
ActiveCell.FormulaR1C1 = "Length"
Range("N3").Select
ActiveCell.FormulaR1C1 = "Pos Val"

' Add Formulas
Range("Q6").Select
ActiveCell.FormulaR1C1 = "=MID(RC[1],2,2)"
Range("P6").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[1])"
Range("O6").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[3])"
Range("N6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]>0,RC[-4],0)"
Range("N6:Q6").Select
Lrow = Range("r65536").End(xlUp).Row
MsgBox Lrow

' Copy and Paste to end of target range
Selection.Copy
Range("N7:Q" & Lrow).Select
ActiveSheet.Paste


Range("A1").Select

End Sub

Each of these subroutines function properly if called separately.
However I would like to call only one subroutine, either by combining
the code into one or by having a subroutine call each of these two
subroutines such as:

Sub DoBoth()
InsertColumns
InsertFormulas
End Sub

However when I do either of those combining methods, it only runs the
"InsertColumns" part and then stops (no error messages). Could
someone please tell me what I am missing - why it will not continue on
and run the steps of the "InsertFormulas" subroutine?

Thanks,
Nancy
 
This is how I ran it...I had no trouble except that if there is nothing in
Row R then the paste function can give a funky result.

Option Explicit

Sub InsertColumns()

Dim Lrow As Long
Dim CopyRange As Range

Columns("N:Q").Insert

' Add Titles
Range("Q3").Select
ActiveCell.FormulaR1C1 = "Line Temp"
Range("P3").Select
ActiveCell.FormulaR1C1 = "Line"
Range("O3").Select
ActiveCell.FormulaR1C1 = "Length"
Range("N3").Select
ActiveCell.FormulaR1C1 = "Pos Val"

' Add Formulas
Range("Q6").Select
ActiveCell.FormulaR1C1 = "=MID(RC[1],2,2)"
Range("P6").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[1])"
Range("O6").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[3])"
Range("N6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]>0,RC[-4],0)"
Range("N6:Q6").Select
Lrow = Range("R65536").End(xlUp).Row
MsgBox Lrow

' Copy and Paste to end of target range
Selection.Copy
Range("N7:Q" & Lrow).Select
ActiveSheet.Paste


Range("A1").Select

End Sub


--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain

Nancy said:
In an Excel spreadsheet of data I would like to do the following:

1. Insert 4 columns to the left of column N
2. Add titles to each of the four new columns
3. Add formulas to each of the four new columns in the third row
4. Copy the formulas down the rows thereby filling in the columns
with data

I have the following coding:

For Step 1:
Sub InsertColumns()

Columns("N:Q").Insert

End Sub

For Step 2-4:
Sub InsertFormulas()

Dim Lrow As Long
Dim CopyRange As Range

' Add Titles
Range("Q3").Select
ActiveCell.FormulaR1C1 = "Line Temp"
Range("P3").Select
ActiveCell.FormulaR1C1 = "Line"
Range("O3").Select
ActiveCell.FormulaR1C1 = "Length"
Range("N3").Select
ActiveCell.FormulaR1C1 = "Pos Val"

' Add Formulas
Range("Q6").Select
ActiveCell.FormulaR1C1 = "=MID(RC[1],2,2)"
Range("P6").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[1])"
Range("O6").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[3])"
Range("N6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]>0,RC[-4],0)"
Range("N6:Q6").Select
Lrow = Range("r65536").End(xlUp).Row
MsgBox Lrow

' Copy and Paste to end of target range
Selection.Copy
Range("N7:Q" & Lrow).Select
ActiveSheet.Paste


Range("A1").Select

End Sub

Each of these subroutines function properly if called separately.
However I would like to call only one subroutine, either by combining
the code into one or by having a subroutine call each of these two
subroutines such as:

Sub DoBoth()
InsertColumns
InsertFormulas
End Sub

However when I do either of those combining methods, it only runs the
"InsertColumns" part and then stops (no error messages). Could
someone please tell me what I am missing - why it will not continue on
and run the steps of the "InsertFormulas" subroutine?

Thanks,
Nancy
Click to expand...
 
I ran it with another sub calling the two supplied subs, and I had no
problem either.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Michael J. Malinsky said:
This is how I ran it...I had no trouble except that if there is nothing in
Row R then the paste function can give a funky result.

Option Explicit

Sub InsertColumns()

Dim Lrow As Long
Dim CopyRange As Range

Columns("N:Q").Insert

' Add Titles
Range("Q3").Select
ActiveCell.FormulaR1C1 = "Line Temp"
Range("P3").Select
ActiveCell.FormulaR1C1 = "Line"
Range("O3").Select
ActiveCell.FormulaR1C1 = "Length"
Range("N3").Select
ActiveCell.FormulaR1C1 = "Pos Val"

' Add Formulas
Range("Q6").Select
ActiveCell.FormulaR1C1 = "=MID(RC[1],2,2)"
Range("P6").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[1])"
Range("O6").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[3])"
Range("N6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]>0,RC[-4],0)"
Range("N6:Q6").Select
Lrow = Range("R65536").End(xlUp).Row
MsgBox Lrow

' Copy and Paste to end of target range
Selection.Copy
Range("N7:Q" & Lrow).Select
ActiveSheet.Paste


Range("A1").Select

End Sub


--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain

Nancy said:
In an Excel spreadsheet of data I would like to do the following:

1. Insert 4 columns to the left of column N
2. Add titles to each of the four new columns
3. Add formulas to each of the four new columns in the third row
4. Copy the formulas down the rows thereby filling in the columns
with data

I have the following coding:

For Step 1:
Sub InsertColumns()

Columns("N:Q").Insert

End Sub

For Step 2-4:
Sub InsertFormulas()

Dim Lrow As Long
Dim CopyRange As Range

' Add Titles
Range("Q3").Select
ActiveCell.FormulaR1C1 = "Line Temp"
Range("P3").Select
ActiveCell.FormulaR1C1 = "Line"
Range("O3").Select
ActiveCell.FormulaR1C1 = "Length"
Range("N3").Select
ActiveCell.FormulaR1C1 = "Pos Val"

' Add Formulas
Range("Q6").Select
ActiveCell.FormulaR1C1 = "=MID(RC[1],2,2)"
Range("P6").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[1])"
Range("O6").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[3])"
Range("N6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]>0,RC[-4],0)"
Range("N6:Q6").Select
Lrow = Range("r65536").End(xlUp).Row
MsgBox Lrow

' Copy and Paste to end of target range
Selection.Copy
Range("N7:Q" & Lrow).Select
ActiveSheet.Paste


Range("A1").Select

End Sub

Each of these subroutines function properly if called separately.
However I would like to call only one subroutine, either by combining
the code into one or by having a subroutine call each of these two
subroutines such as:

Sub DoBoth()
InsertColumns
InsertFormulas
End Sub

However when I do either of those combining methods, it only runs the
"InsertColumns" part and then stops (no error messages). Could
someone please tell me what I am missing - why it will not continue on
and run the steps of the "InsertFormulas" subroutine?

Thanks,
Nancy
Click to expand...
Click to expand...
 
I opened a new data file, created a new module and cut and pasted the
code from the posting by Michael J. Malinsky. I did this in case
there was something unknown in the file I had been using. I also
included two msgboxes so I could see progression through the code.

MsgBox "At start"
Columns("N:Q").Insert
MsgBox "Next step"
' Add Titles
Range("Q3").Select
etc...

However it still does not progress beyond inserting the columns. It
shows the "At start" msgbox, then inserts columns then stops (the
cursor goes back to the standard "+").

I am using Excel 97 SR-2 which seems to be the problem. I just went
and ran it on a colleague's computer that has Excel 2002 and it worked
perfectly. Any ideas how to get around this with Excel 97?

Again, thanks,
Nancy



Bob Phillips said:
I ran it with another sub calling the two supplied subs, and I had no
problem either.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Michael J. Malinsky said:
This is how I ran it...I had no trouble except that if there is nothing in
Row R then the paste function can give a funky result.

Option Explicit

Sub InsertColumns()

Dim Lrow As Long
Dim CopyRange As Range

Columns("N:Q").Insert

' Add Titles
Range("Q3").Select
ActiveCell.FormulaR1C1 = "Line Temp"
Range("P3").Select
ActiveCell.FormulaR1C1 = "Line"
Range("O3").Select
ActiveCell.FormulaR1C1 = "Length"
Range("N3").Select
ActiveCell.FormulaR1C1 = "Pos Val"

' Add Formulas
Range("Q6").Select
ActiveCell.FormulaR1C1 = "=MID(RC[1],2,2)"
Range("P6").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[1])"
Range("O6").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[3])"
Range("N6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]>0,RC[-4],0)"
Range("N6:Q6").Select
Lrow = Range("R65536").End(xlUp).Row
MsgBox Lrow

' Copy and Paste to end of target range
Selection.Copy
Range("N7:Q" & Lrow).Select
ActiveSheet.Paste


Range("A1").Select

End Sub


--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain

Nancy said:
In an Excel spreadsheet of data I would like to do the following:

1. Insert 4 columns to the left of column N
2. Add titles to each of the four new columns
3. Add formulas to each of the four new columns in the third row
4. Copy the formulas down the rows thereby filling in the columns
with data

I have the following coding:

For Step 1:
Sub InsertColumns()

Columns("N:Q").Insert

End Sub

For Step 2-4:
Sub InsertFormulas()

Dim Lrow As Long
Dim CopyRange As Range

' Add Titles
Range("Q3").Select
ActiveCell.FormulaR1C1 = "Line Temp"
Range("P3").Select
ActiveCell.FormulaR1C1 = "Line"
Range("O3").Select
ActiveCell.FormulaR1C1 = "Length"
Range("N3").Select
ActiveCell.FormulaR1C1 = "Pos Val"

' Add Formulas
Range("Q6").Select
ActiveCell.FormulaR1C1 = "=MID(RC[1],2,2)"
Range("P6").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[1])"
Range("O6").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[3])"
Range("N6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]>0,RC[-4],0)"
Range("N6:Q6").Select
Lrow = Range("r65536").End(xlUp).Row
MsgBox Lrow

' Copy and Paste to end of target range
Selection.Copy
Range("N7:Q" & Lrow).Select
ActiveSheet.Paste


Range("A1").Select

End Sub

Each of these subroutines function properly if called separately.
However I would like to call only one subroutine, either by combining
the code into one or by having a subroutine call each of these two
subroutines such as:

Sub DoBoth()
InsertColumns
InsertFormulas
End Sub

However when I do either of those combining methods, it only runs the
"InsertColumns" part and then stops (no error messages). Could
someone please tell me what I am missing - why it will not continue on
and run the steps of the "InsertFormulas" subroutine?

Thanks,
Nancy
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with my VBA and formula 8
Insert columns and values programmatically with IF statement 9
File Name changes with dates and Deleting Rows of Zeros 0
"=TEXT(RC[-1],"MMM")" 4
Geometric progression in VBA 10
macro to copy rows that contain a cell with a certain number 2
Data samples 3
Macro that deletes and pastes filterd rows and then some 2

Back
Top