Make vba macro functional for all workbooks

[abramo]

I have located here at mrexcel.com a macro to multiply values in two adjacent columns A, B, and provide the result in column C - it works fine, however only at Sheet1 of workbook "personal.xlsm". Tried but was unsuccessful to make it work at any workbook. Shall much appreciate help to make this macro functional at any workbook. Thanks!

Sub MultiplyColumns()
    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Long

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' Set the range to loop through (column A in this case)
    Set rng = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)

    ' Loop through each cell in the range
    For i = 1 To rng.Count
        ' Multiply column A and B values and put the result in column C
        ws.Cells(i, "C").Value = ws.Cells(i, "A").Value * ws.Cells(i, "B").Value
    Next i
End Sub

 

[JerrExcel]

Hi there,

A few issues with you existing code:

1. Referring to the Sheet "Sheet1". This was hardcoded, hence, if the workbook do not contain sheet named "Sheet1", then the macro will not work.
Here, I suggest use

• If there will be only 1 sheet for each workbook, use Sheets(1), so that the macro will always run on the first created sheet of another workbook.

' Set the worksheet
Set ws = ThisWorkbook.Sheets(1)

• If there is a consistent name for the sheet name in another workbook, Create a sheet of "Dashboard", with buttons for Run Macro, choose a cell to reference the Sheet name. Example, put the sheet name in B2.

' Set the worksheet
Set ws = ThisWorkbook.Sheets(Sheets("Dashboard").Range("B2").Value)



2. In the loop, if there is header or strings in either Column A or Column B, then the macro will not work because strings cannot be multiply.
Here, I suggest use Error Handling:

' Loop through each cell in the range
For i = 1 To rng.Count
On Error Resume Next
' Multiply column A and B values and put the result in column C
ws.Cells(i, "C").Value = ws.Cells(i, "A").Value * ws.Cells(i, "B").Value
Next i

Full Code as below:

Sub MultiplyColumns()
Dim ws As Worksheet
Dim rng As Range
Dim i As Long

' Set the worksheet
Set ws = ThisWorkbook.Sheets(1)

' Set the range to loop through (column A in this case)
Set rng = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)

' Loop through each cell in the range
For i = 1 To rng.Count
On Error Resume Next
' Multiply column A and B values and put the result in column C
ws.Cells(i, "C").Value = ws.Cells(i, "A").Value * ws.Cells(i, "B").Value
Next i
End Sub

or

Sub MultiplyColumns()
    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Long

    ' Set the worksheet
   [COLOR=rgb(226, 80, 65)] Set ws = ThisWorkbook.Sheets(Sheets("Dashboard").Range("B2").Value)[/COLOR]

    ' Set the range to loop through (column A in this case)
    Set rng = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)

    ' Loop through each cell in the range
    For i = 1 To rng.Count
    [COLOR=rgb(97, 189, 109)]On Error Resume Next[/COLOR]
        ' Multiply column A and B values and put the result in column C
        ws.Cells(i, "C").Value = ws.Cells(i, "A").Value * ws.Cells(i, "B").Value
    Next i
End Sub

Give this a try. Thanks!

 

[abramo]

Deleted

 

[abramo]

@JerrExcel : many thanks, excellent : we cross posted, so I deleted my post.
I tested your suggestion and got"
Compile error for:

   [COLOR=rgb(226, 80, 65)] Set ws = ThisWorkbook.Sheets(Sheets("Dashboard").Range("B2").Value)[/COLOR]

 

[JerrExcel]

@abramo

Hi,

Sorry for my mistake in formatting the VBA code.

Try this:

Sub MultiplyColumns()
Dim ws As Worksheet
Dim rng As Range
Dim i As Long

' Set the worksheet
Set ws = ThisWorkbook.Sheets(1)

' Set the range to loop through (column A in this case)
Set rng = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)

' Loop through each cell in the range
For i = 1 To rng.Count
On Error Resume Next
' Multiply column A and B values and put the result in column C
ws.Cells(i, "C").Value = ws.Cells(i, "A").Value * ws.Cells(i, "B").Value
Next i
End Sub

or

Sub MultiplyColumns()
    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Long

    ' Set the worksheet
  Set ws = ThisWorkbook.Sheets(Sheets("Dashboard").Range("B2").Value)

    ' Set the range to loop through (column A in this case)
    Set rng = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)

    ' Loop through each cell in the range
    For i = 1 To rng.Count
    [COLOR=rgb(97, 189, 109)]On Error Resume Next[/COLOR]
        ' Multiply column A and B values and put the result in column C
        ws.Cells(i, "C").Value = ws.Cells(i, "A").Value * ws.Cells(i, "B").Value
    Next i
End Sub

 

[jkpieterse]

Key here is you are using ThisWorkbook, which refers to the personal.xlsb workbook. Instead, you should be using ActiveWorkbook if your code is to work on any workbook, or Workbooks("TheNameOfTheWorkbook.xlsx") if this is about a specific workbook open in Excel.

 

[abramo]

Thanks!

First vba version : works only at "personal.xlsm"
Second vba version : compile error :

    [COLOR=rgb(97, 189, 109)]On Error Resume Next[/COLOR]

What worked for me, at any workbook, using Sheet1 and just number columns A, B, no headers is:

Sub MultiplyColumns()
    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Long

    ' Set the worksheet
    Set ws = ActiveWorkbook.Sheets("Sheet1")

    ' Set the range to loop through (column A in this case)
    Set rng = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)

    ' Loop through each cell in the range
    For i = 1 To rng.Count
        ' Multiply column A and B values and put the result in column C
        ws.Cells(i, "C").Value = ws.Cells(i, "A").Value * ws.Cells(i, "B").Value
    Next i
End Sub

Meaning :
Replacing

ThisWorkbook.Sheets

of the problematic original
by

ActiveWorkbook.Sheets

Not proper for general usage, but in practice doing exactly what I need the way I proceed.

Even so, it would be nice to have a proper general use code, and not my anateurish solution

 

[abramo]

@jkpieterse : many thanks, it is exactly as per your suggestion! I just expanded the response to let anybody else know.

 

[abramo]

I believe the general usage solution/version of this code:

Multiply Columns A, B (containing numbers) and write results in column C

should be able to do this at any selected adjacent pair of columns with numbers in a Sheet and write the results in the immediately next column to the right. It should be able to function with column headers or no headers.

For my own purpose, the code already kindly provided does all I need - and many thanks indeed to all that took the trouble to help!