1

I've written the following code that adds a new line to a range of rows within a section in a spreadsheet. As there are several of these sections, sBudgetLine is passed in as the start value of the range (Section name) which is added with an extra 1 to miss the headers of the section (date, description and price).

All works fine, however when the row is added occasionally the formatting that I have hard coded doesn't work and adds an extra line further down the page as well as the new row which is in the right place.

Sub AddNewAllocToSpendLine(sBudgetLine As String, Optional sSheetName As String = c_Alloc2SpendSheetName)

Dim c As Range
Dim N As Long
Dim lastRow As Long
Dim formula As Range
Dim rng As Range

With Worksheets(sSheetName)

    Set c = .Columns(1).Find(sBudgetLine, LookIn:=xlValues)
    If Not (c Is Nothing) Then

        lastRow = .Cells(c.Row, 2).End(xlDown).Row

        Worksheets(sSheetName).Activate
        Worksheets(sSheetName).Range("B" & lastRow + 1, "E" & lastRow + 1).Activate

        Selection.EntireRow.Insert Shift:=xlDown

        Selection.Range("B" & ActiveCell.Row, "E" & ActiveCell.Row).Interior.Color = RGB(255, 255, 255)

        With Selection.Range("B" & ActiveCell.Row, "D" & ActiveCell.Row).Borders
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = 1
            End With

        Selection.Cells(1, 4).formula = "=IF(" & "D" & ActiveCell.Row & "="""","""",IF(" & "D" & ActiveCell.Row & ">14999.99,""Minimum 3 formal competitive tenders - inform PSU"",IF(" & "D" & ActiveCell.Row & ">2499.99,""Minimum 3 written quotes based on written specification"",IF(" & "D" & ActiveCell.Row & ">999.99,""Minimum 3 written quotes"",IF(" & "D" & ActiveCell.Row & ">499.99,""Minimum 3 oral quotes"",IF(" & "D" & ActiveCell.Row & ">249.99,""One written or verbal quote"",""One written or verbal quote""))))))"

    End If
End With
End Sub

I have tried all sorts and can't figure whats wrong, could this be a vba glitch? If this is the case, is there a better way of providing the formatting with the added row?

user3565164
  • 421
  • 9
  • 21

1 Answers1

1

You started off with a With ... End With statement referencing the parent worksheet then seemed to abandon it. If a larger block of cells encompassing the cells you want to .Activate is already the .Selection then the selection does not change, only the ActiveCell property.

Best to avoid using the Range .Select method and Range .Activate method altogether¹.

Sub AddNewAllocToSpendLine(sBudgetLine As String, Optional sSheetName As String = c_Alloc2SpendSheetName)

    Dim rw As Variant, nr As Long

    With Worksheets(sSheetName)
        rw = Application.Match(sBudgetLine, .Columns(1), 0)
        If Not IsError(rw) Then
            nr = .Cells(rw, "B").End(xlDown).Row + 1
            .Rows(nr).EntireRow.Insert Shift:=xlDown
            With .Range(.Cells(nr, "B"), .Cells(nr, "E"))
                .Interior.Color = RGB(255, 255, 255)
                With .Borders
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                    .ColorIndex = 1
                End With
                .Cells(1, 4).formula = _
                    "=IF(D" & nr & "=TEXT(,), TEXT(,), " & _
                     "IF(D" & nr & ">14999.99, ""Minimum 3 formal competitive tenders - inform PSU"", " & _
                     "IF(D" & nr & ">2499.99, ""Minimum 3 written quotes based on written specification"", " & _
                     "IF(D" & nr & ">999.99, ""Minimum 3 written quotes"", " & _
                     "IF(D" & nr & ">499.99, ""Minimum 3 oral quotes"", " & _
                     "IF(D" & nr & ">249.99, ""One written or verbal quote"", " & _
                        """One written or verbal quote""))))))"
            End With
        End If

    End With
End Sub

TEXT(,) is just a way of writing "" without having to write it as """" in a string.

.¹ See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

Community
  • 1
  • 1