Variable Column Products using Macros

Allienne

New Member
Joined
May 2, 2018
Messages
13
Hello,

I have a variable number of columns. Column N is where I need the product of Columns L and M. I also have a variable number of rows for Column N to fill.

I have no idea how to even begin to write this.

I have this so far but it doesn't even begin to work.


Dim lngLstCol2 As Long, lngLstRow2 As Long

lngLstRow2 = ActiveSheet.UsedRange.Rows.Count
lngLstCol2 = ActiveSheet.UsedRange.Columns.Count

For Each rngCell2 In Range(lngLstRow2)
If rngCell2.Value > "" Then
r2 = rngCell2.Row
C2 = rngCell2.Column
Range(Cells(r2, C2), Cells(r2, lngLstCol2)).Select
With Selection
.Formula = "=PRODUCT(lngLstCol2-2, lngLstCol2-1)"
End With
End If
Next

Any help at all would be appreciated.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,657
Office Version
365, 2010
Platform
Windows, Mobile
Column N is where I need the product of Columns L and M.
Is column N the last column on the sheet?
Is there anything in column N when the code runs (including if it has a header already there)?
What are you supposed to be looping through in the line below?
Code:
For Each rngCell2 In Range(lngLstRow2)
 

Allienne

New Member
Joined
May 2, 2018
Messages
13
Mark,

The thing is this test run is Column N but when it is actually being used the columns will be variable. I may have 10 columns one time and 40 columns the next. I wanted a way to add the formula to the last column regardless of how many columns it has. It will have a header but I can add the header afterwards if I need to.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,657
Office Version
365, 2010
Platform
Windows, Mobile
It will have a header but I can add the header afterwards if I need to
.
I need to know for definite if the header is already there or not as it affects how the code is written.
You also haven't answered the question below
What are you supposed to be looping through in the line below?
Code:
For Each rngCell2 In Range(lngLstRow2)
as it doesn't make any sense as written? Are you intending it to loop through either column L or M (as per current layout) testing through cells that aren't blank or something else?

Btw, are your current columns L & M constants i.e. not formulas.

Please answer the questions asked, I don't need any other info.
 

Allienne

New Member
Joined
May 2, 2018
Messages
13
I need to know for definite if the header is already there or not as it affects how the code is written.
There is no header in the column yet but there are headers in the previous columns.

You also haven't answered the question below as it doesn't make any sense as written? Are you intending it to loop through either column L or M (as per current layout) testing through cells that aren't blank or something else?
I had taken previously used code and tried to make it work for this as well but I am new to VBA and don't fully understand how to edit code to do what I want yet. I only want the formula in Column N in rows that have text in Columns L & M.

Btw, are your current columns L & M constants i.e. not formulas.
They are constants.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,657
Office Version
365, 2010
Platform
Windows, Mobile
Try the code below on a copy of your data.

Code:
Sub GetProduct()
Dim lc As Long, lr As Long

lr = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
lc = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

Range(Cells(2, lc), Cells(lr, lc)).SpecialCells(xlCellTypeConstants).Offset(, 1).FormulaR1C1 = "=PRODUCT(RC[-2],RC[-1])"
End Sub
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,657
Office Version
365, 2010
Platform
Windows, Mobile
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,099,112
Messages
5,466,741
Members
406,495
Latest member
Arlind Elezi

This Week's Hot Topics

Top