Variable Column Products using Macros

[Allienne]

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.

 

[MARK858]

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?

For Each rngCell2 In Range(lngLstRow2)

 

[Allienne]

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]

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?

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]

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]

Try the code below on a copy of your data.

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

 

[Allienne]

Mark,

That worked! Thank you so much!

 

[MARK858]

You're welcome