Results 1 to 2 of 2

Thread: Vary row number in a VBA formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2011
    Posts
    1,510
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vary row number in a VBA formula

    Using Excel 2000
    Hi,

    I have a formula sumproduct running in the VBA =SUMPRODUCT(--($I$8:$L$8=C8:F8)) I want a loop once this formula is filled restart again filling with change of row number =SUMPRODUCT(--($I$9:$L$9=C8:F8)) change as long as data find in the column C and finish the process

    Code:
    Sub FillFormlas_UMPRODUCT()
        
        Dim lngLastRow As Long
        lngLastRow = Cells(Rows.Count, "C").End(xlUp).Row
        
        Range("G8:G" & lngLastRow).Formula = "=SUMPRODUCT(--($I$8:$L$8=C8:F8))"
        Range("G9:G" & lngLastRow) = Range("G9:G" & lngLastRow).Value 'Convert Values
         
          
    End Sub
    Is it possible?

    Thank you in advance

    Regards,
    Kishan
    Last edited by Kishan; Oct 6th, 2019 at 02:35 AM.

  2. #2
    Board Regular
    Join Date
    Mar 2011
    Posts
    1,510
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vary row number in a VBA formula

    Please does any idea how to change the above sumproduct normal formula to R1C1 and than how to use it into a VBA?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •