VBA to set dynamic formula

skamat · Jan 15th 2013

I had requested for a formula earlier for which I got the perfect reply and the solution worked very well.

Now I seek further assistance for VBA. The formula I have entered in VBA is as follows

"=SUMPRODUCT(--(data!R5C1:R100C1=RC3)*--(MONTH(data!R5C2:R100C2)=MONTH(R5C)))"

Now the thing is that it will search only till line 100.
I want it to search till the end of the line, for eg. if the data is till 50 then it should search till 50, if the data is till 500 then it should search till 500.

Again I repeat the above formula is to be used in VBA.

**Trebor76** · Jan 15th 2013

Re: VBA to set dynamic formula

Hi skamat,

Please note my change (this time) to your old title of just "VBA" to something more internet search friendly. Please make sure the title of your future posts adhere to this.

With regard to your query - as long as there's some data in the "data" tab the following will work:

Code

Option Explicit


Sub Macro1()


    'Written by Trebor76
    'Visit my website [URL="http://www.excelguru.net.au"]www.excelguru.net.au[/URL]
    Dim lngEndRow As Long
    
    lngEndRow = Sheets("data").Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ActiveCell.Formula = "=SUMPRODUCT(--(data!R5C1:R" & lngEndRow & "C1=RC3)*--(MONTH(data!R5C2:R" & lngEndRow & "C2)=MONTH(R5C)))"


End Sub

Display More

Regards,

Robert

skamat · Jan 15th 2013

Re: VBA to set dynamic formula

Dear Sir

Not working perfectly fine.
Below is the full VBA that I have written

Code

Private Sub CommandButton1_Click()
Me.Hide
Dim ws1 As Worksheet
Worksheets("TM Wise").Activate
Range("D7:P37").ClearContents
Set ws1 = Worksheets("TM Wise")
Sheets("TM Wise").Activate
Range("B3").Value = "TOTAL NUMBER OF SPARE PARTS USED"
ws1.Range("D7:O" & ws1.Cells(Rows.Count, "B").End(xlUp).Row).FormulaR1C1 = _
"=SUMPRODUCT(--(data!R5C1:R10000C1=RC3)*--(MONTH(data!R5C2:R10000C2)=MONTH(R5C)))"
ws1.Range("P7:P" & ws1.Cells(Rows.Count, "B").End(xlUp).Row).FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
ws1.Range("D" & ws1.Cells(Rows.Count, "B").End(xlUp).Row + 2).Resize(, ws1.Cells(5, Columns.Count).End(xlToLeft).Column - 3).FormulaR1C1 = "=SUM(R[-30]C:R[-2]C)"
ws1.Range("A1:P" & ws1.Cells(Rows.Count, "B").End(xlUp).Row + 4).Copy
ws1.Range("A1").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
End Sub

Display More

jolivanes · Jan 15th 2013

Re: VBA to set dynamic formula

skamat.
Under your name it states "I agreed to these rules"
Please read these rules again, in this case pay particular attention to number 1.

skamat · Jan 16th 2013

Re: VBA to set dynamic formula

Thanks works fine with your formula

VBA to set dynamic formula

Participate now!

Share