[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Problem with running consecutive MACRO's

Posted on 2007-11-23
9
Medium Priority
?
670 Views
Last Modified: 2008-02-01
Hi,
I am tryign to runt he two macros below consecutively and can not work out what the second one doesn't do anything.

Can anyone suggest a solution?

Thanks,

Andrew

Sub CopyRows2amended2()
    Dim c As Range, Row As Integer, i As Integer
    With Worksheets("Sheet1")
        For Row = .Range("B1").End(xlDown).Row To 1 Step -1
            If .Cells(Row, 1).Value = 1 Then
                For i = 1 To 6
                    .Cells(Row, 1).EntireRow.Insert
                Next i
                Worksheets("Sheet2").Range("A1:Z5").Copy
                .Cells(Row, 2).Select
                ActiveCell.PasteSpecial xlPasteValues
                ActiveCell.PasteSpecial xlPasteFormats
                Cells(Row + 2, 4) = .Cells(Row + 6, 2)
            End If
           
        Next Row
       
        End With
       

       
         Columns("AB:AB").Select
    Selection.Copy

    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
   
 
End Sub




Sub OfficendPresentHolder()
    Dim c As Range, Row As Integer, i As Integer
    With Worksheets("Sheet1")
        For Row = .Range("B1").End(xlDown).Row To 1 Step -1
            If .Cells(Row, 1).Value = 1 Then
                For i = 1 To 2
                    .Cells(Row, 1).EntireRow.Insert
                Next i
                Worksheets("Sheet2").Range("A1:Z2").Copy
                .Cells(Row, 2).Select
                ActiveCell.PasteSpecial xlPasteValues
                ActiveCell.PasteSpecial xlPasteFormats
                'Cells(Row + 2, 4) = .Cells(Row + 6, 2)
            End If
           
        Next Row
       
        End With
   
 
 
End Sub

0
Comment
Question by:AndrewMcLaughlin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 20338465
Well before the end sub...place this code...this will run the second run macro...

call OfficendPresentHolder

This will run the macro once the first macro gets over..
0
 

Author Comment

by:AndrewMcLaughlin
ID: 20338561
Sorry  - I didn't explain that very well.  The problem is that if I run the furst macro it works, but then the second macro doesn't do anything.  The same happens if I reverse the order.  I don't understand what is going on?!




Sub CopyRows2amended2()
    Dim c As Range, Row As Integer, i As Integer
    With Worksheets("Sheet1")
        For Row = .Range("B1").End(xlDown).Row To 1 Step -1
            If .Cells(Row, 28).Value = 1 Then
                For i = 1 To 6
                    .Cells(Row, 1).EntireRow.Insert
                Next i
                Worksheets("Sheet2").Range("A1:Z5").Copy
                .Cells(Row, 2).Select
                ActiveCell.PasteSpecial xlPasteValues
                ActiveCell.PasteSpecial xlPasteFormats
                Cells(Row + 2, 4) = .Cells(Row + 6, 2)
            End If
           
        Next Row
       
        End With
       

       
         
 
End Sub




Sub OfficendPresentHolder()
    Dim c As Range, Row As Integer, i As Integer
    With Worksheets("Sheet1")
        For Row = .Range("B1").End(xlDown).Row To 1 Step -1
            If .Cells(Row, 29).Value = 1 Then
                For i = 1 To 2
                    .Cells(Row, 1).EntireRow.Insert
                Next i
                Worksheets("Sheet2").Range("A1:Z2").Copy
                .Cells(Row, 2).Select
                ActiveCell.PasteSpecial xlPasteValues
                ActiveCell.PasteSpecial xlPasteFormats
                Cells(Row + 1, 4) = .Cells(Row + 2, 3)
            End If
           
        Next Row
       
        End With
   
 
 
End Sub
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 20338573
Well Yeah Coz u can run a macro only once...now from that macro to call another macro u need to give a command...

and if u enter this command what i told u..after the first macro is complete its gonna call the second macro...and will start running...

here is the command..just paste it before end sub in first macro

call OfficendPresentHolder


0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 20338583
Can you post a workbook so we can see what you're working with?
Have you tried stepping into the second macro and then running it line by line (using f8) to see what is going on?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20338598
AndrewMcLaughlin said:
>>    Dim c As Range, Row As Integer, i As Integer

Saurabh seems to have nailed what you're asking about.  Here are two friendly pieces of
advice:

1) Never, ever, ever use an Integer data type for a row number.  Integer tops out at ~32,767 or so;
and Excel 97-2003 worksheet can have 65,536 rows, and an Excel 2007 worksheet can have a
smidge over a million rows.  If you get in the habit of using Integer for rown numbers, one of
these days you will get an overflow error.  Use Long instead.  For that matter, just stop using
Integer altogether and always use Long: under many circumstances, Integer actually runs
*slower* than Long (because the arithmetic engine has to do implicit conversions from your
Integer variable to Long and then back to Integer).

2) It is almost never necessary to actually select ranges of worksheets.  Using unnecessary
Select or Activate statements merely slows down your code.

Regards,

Patrick
0
 

Author Comment

by:AndrewMcLaughlin
ID: 20338620
Thanks for the tips matthewspatrick.

However Saurabh's solution doesn't work.

I have figured that the problem is that after running either of the macros I introduce blank cells into column b and the second macro stops at the first blank line it meets.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 20338631
Oh yes - use:
For Row = .Cells(.Rows.Count, "B").End(xlUp).Row To 1 Step -1

instead.
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 20338672
lol...me didnt took not of that...i thought u want to run the second macro automatically.. once u done with the first macro...
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 20338674
oops...note....
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question