CODEWITHSUNDEEP
vbaexcel
J Doe
J Doe

Reputation: 65

Excel VBA - How to use ByVal

Someone at code review was helping optimize my code and said I should be using the following code to avoid duplicate code. I'm not sure how to use it properly...From what I can tell I have to type in ConvertToNumeric when I want the code to run but I'm not sure how to change the variables like which sheet or which column to find the last cell on. The concept makes sense to me though.

    Private Sub ConvertToNumeric(ByVal sheetToUse As Worksheet, ByVal columnToFindLastCellOn As String)
        Dim helperCell As Range
        Set helperCell = sheetToUse.Range("AK1")
            helperCell.Value = "1"
            helperCell.Copy

        Dim lastCell As Range
        Set lastCell = sheetToUse.Cells(Rows.Count, columnToFindLastCellOn).End(xlUp)

        Dim modifyRange As Range
        Set modifyRange = sheetToUse.Range(lastCell, lastCell.End(xlUp))
        modifyRange.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

        helperCell.ClearContents
    End Sub

Upvotes: 0

Views: 693

Answers (1)

paul bica
paul bica

Reputation: 10715

To use the sub mentioned at Code Review you can call it like this

ConvertToNumeric Worksheets(2), "A"

or

ConvertToNumeric Worksheets("Sheet1"), 3
  • sheetToUse is the sheet you want to work with
  • columnToFindLastCellOn is the column letter (or number) that the last used cell is based on

Upvotes: 1

Related Questions