CODEWITHSUNDEEP
vbaexcelselectmultiple-columns
nw201827
nw201827

Reputation: 21

Using one macro to run multiple macros

I have the following macro that will search for three different column headings, select the data under those headings and change the data from a currency format to a number format. The data is stored in Sheet 2 of my workbook and the macro works fine when I run it on it's own in this Sheet. 

Sub ProjectFundingFormat()
'
Dim WS As Worksheet
Dim lastCol As Long, lastRow As Long, srcRow As Range
Dim found1 As Range, found2 As Range

Set WS = Workbooks("Workbook1.xlsm").Worksheets("Sheet2") 'Needs to be open

    With WS
    lastCol = .Cells(1, Columns.count).End(xlToLeft).Column
    Set srcRow = .Range("A1", .Cells(1, lastCol))
    Set found1 = srcRow.Find(What:="2018FundingLabor", LookAt:=xlWhole, MatchCase:=False)

    If Not found1 Is Nothing Then
            lastRow = .Cells(Rows.count, found1.Column).End(xlUp).Row
            .Range(.Cells(2, found1.Column), .Cells(lastRow, found1.Column)).Select
            Selection.NumberFormat = "0.00"
        End If
End With


With WS
    lastCol = .Cells(1, Columns.count).End(xlToLeft).Column
    Set srcRow = .Range("A1", .Cells(1, lastCol))
    Set found1 = srcRow.Find(What:="2018FundingNonlabor", LookAt:=xlWhole, MatchCase:=False)

    If Not found1 Is Nothing Then
            lastRow = .Cells(Rows.count, found1.Column).End(xlUp).Row
            .Range(.Cells(2, found1.Column), .Cells(lastRow, found1.Column)).Select
            Selection.NumberFormat = "0.00"
        End If
End With

    With WS
    lastCol = .Cells(1, Columns.count).End(xlToLeft).Column
    Set srcRow = .Range("A1", .Cells(1, lastCol))
    Set found1 = srcRow.Find(What:="2018 Total Funding", LookAt:=xlWhole, MatchCase:=False)

    If Not found1 Is Nothing Then
            lastRow = .Cells(Rows.count, found1.Column).End(xlUp).Row
            .Range(.Cells(2, found1.Column), .Cells(lastRow, found1.Column)).Select
            Selection.NumberFormat = "0.00"
        End If
End With
End Sub

I am wanting to combine this macro with another two so that I can go into Sheet 1, click a "run" button I have inserted and all my macros will run together to update my data.
However, I am getting the error

Run time error 1004 - select method of range class failed

at the line 

.Range(.Cells(2, found1.Column), .Cells(lastRow, found1.Column)).Select

Does anyone know what might be wrong with my code? I am confused since it works fine on its own, but won't run when combined with my other macros.
I am using the following macro to combine my two existing macros: 

Sub ProjectUpdate()
Call ProjectName
Call ProjectFunding
Call ProjectFundingFormat

MsgBox "Done"

End Sub

Upvotes: 0

Views: 4802

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57753

You should really avoid to use .Select and .Activate at all! This slows down your code a lot and leads into many issues. It is a very bad practice. If you are interested in writing stable and good quality code I really recommend to read and follow: How to avoid using Select in Excel VBA

Your code can be reduced to the following:

Option Explicit

Public Sub ProjectFundingFormat()
    Dim Ws As Worksheet
    Set Ws = Workbooks("Workbook1.xlsm").Worksheets("Sheet2") 'Needs to be open

    With Ws
        Dim srcRow As Range
        Set srcRow = .Range("A1", .Cells(1, .Columns.Count).End(xlToLeft))

        Dim lastRow As Long
        Dim found1 As Range

        Set found1 = srcRow.Find(What:="2018FundingLabor", LookAt:=xlWhole, MatchCase:=False)
        If Not found1 Is Nothing Then
            lastRow = .Cells(.Rows.Count, found1.Column).End(xlUp).Row
            .Range(.Cells(2, found1.Column), .Cells(lastRow, found1.Column)).NumberFormat = "0.00"
        End If

        Set found1 = srcRow.Find(What:="2018FundingNonlabor", LookAt:=xlWhole, MatchCase:=False)
        If Not found1 Is Nothing Then
            lastRow = .Cells(.Rows.Count, found1.Column).End(xlUp).Row
            .Range(.Cells(2, found1.Column), .Cells(lastRow, found1.Column)).NumberFormat = "0.00"
        End If

        Set found1 = srcRow.Find(What:="2018 Total Funding", LookAt:=xlWhole, MatchCase:=False)
        If Not found1 Is Nothing Then
            lastRow = .Cells(.Rows.Count, found1.Column).End(xlUp).Row
            .Range(.Cells(2, found1.Column), .Cells(lastRow, found1.Column)).NumberFormat = "0.00"
        End If
    End With
End Sub
  1. Throw out unnecessary repeating With statements.

  2. Last column number needs not to be determined you can use the last cell of row 1 direclty with .Cells(1, .Columns.Count).End(xlToLeft).

  3. Throw out all .Select and .Activate.

Makes it much cleaner and more stable.

Or you even use an additional procedure to avoid repeating code at all, which is a good practice to not repeat code and better have procedures instead:

Option Explicit

Public Sub ProjectFundingFormat()
    Dim Ws As Worksheet
    Set Ws = Workbooks("Workbook1.xlsm").Worksheets("Sheet2") 'Needs to be open

    With Ws
        Dim srcRow As Range
        Set srcRow = .Range("A1", .Cells(1, .Columns.Count).End(xlToLeft))

        FindAndFormat srcRow, "2018FundingLabor"
        FindAndFormat srcRow, "2018FundingNonlabor"
        FindAndFormat srcRow, "2018 Total Funding"
    End With
End Sub

Public Sub FindAndFormat(srcRow As Range, FindWhat As String)
    With srcRow.Parent
        Dim found1 As Range
        Set found1 = srcRow.Find(What:=FindWhat, LookAt:=xlWhole, MatchCase:=False)
        If Not found1 Is Nothing Then
            Dim lastRow As Long
            lastRow = .Cells(.Rows.Count, found1.Column).End(xlUp).Row
            .Range(.Cells(2, found1.Column), .Cells(lastRow, found1.Column)).NumberFormat = "0.00"
        End If
    End With
End Sub

Upvotes: 1

Related Questions