Combine Cell Text Details-CodePudding

Is there a way to combine these three lines to be shorter? I use these lines multiple times for different column indexes, so it is a lot of repeating. I am wondering if there is a better way to approach this method. For sysnum it is defined in code that I did not include because it did not seem relevant, but it is a text string of numbers.

Here is my code:

Dim lastrow As Long, sysnum as String 
lastrow = wb.Worksheets(sysnum).Cells(Rows.Count, 1).End(xlUp).row 

wb.Worksheets(sysnum).Rows(lastrow   1).Insert
wb.Worksheets(sysnum).Cells(lastrow   1, 2).Value = sysnum
wb.Worksheets(sysnum).Cells(lastrow   1, 2).Font.Bold = True

wb.Worksheets(sysnum).Cells(lastrow   1, 3).Value = "Passed"
wb.Worksheets(sysnum).Cells(lastrow   1, 3).Font.Bold = True
wb.Worksheets(sysnum).Cells(lastrow   1, 3).Interior.Color = vbGreen

End If

CodePudding user response：

Create a Method For Repeating Code

Usage

Sub CallSysNumInsert()

    ' These two are already declared somewhere in your code!
    ' They are here only for the code to be able to compile.
    Dim wb As Workbook
    Dim sysnum As String

    ' Call the method.    
    SysNumInsert wb, sysnum

End Sub

The Method

Rename the method appropriately.

Sub SysNumInsert(ByVal wb As Workbook, ByVal WorksheetName As String)
    With wb.Worksheets(WorksheetName)
        Dim dRow As Long: dRow = .Cells(.Rows.Count, "A").End(xlUp).Row   1
        .Rows(dRow).Insert
        With .Cells(dRow, "B")
            .Value = WorksheetName
            .Font.Bold = True
        End With
        With .Cells(dRow, "C")
            .Value = "Passed"
            .Font.Bold = True
            .Interior.Color = vbGreen
        End With
    End With
End Sub

CodePudding user response：

It looks a little better if you do this:

  Dim lastrow As Long, sysnum As String
  

  With wb.Worksheets(sysnum)
      lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
      .Rows(lastrow   1).Insert
      .Cells(lastrow   1, 2).Value = sysnum
      .Cells(lastrow   1, 2).Font.Bold = True
      
      .Cells(lastrow   1, 3).Value = "Passed"
      .Cells(lastrow   1, 3).Font.Bold = True
      .Cells(lastrow   1, 3).Interior.Color = vbGreen
  End With

but any time you find yourself copy/pasting the same code everywhere - you should try to abstract that code into a sub-routine. Perhaps:

Sub ResetColors(ByVal sheetNumber As Integer)
    With wb.Worksheets(sheetNumber)
        lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
        .Rows(lastrow   1).Insert
        .Cells(lastrow   1, 2).Value = sysnum
        .Cells(lastrow   1, 2).Font.Bold = True
        
        .Cells(lastrow   1, 3).Value = "Passed"
        .Cells(lastrow   1, 3).Font.Bold = True
        .Cells(lastrow   1, 3).Interior.Color = vbGreen
    End With
End Sub

Then you call that subroutine by calling "ResetColors 1" ... or the activesheet.index