Consulting

Results 1 to 5 of 5

Thread: Question on Row References in VBA

  1. #1

    Question on Row References in VBA

    Hi All,
    I really apologize for posting what I am guessing is going to be a really stupid question, but I am trying to teach myself VBA and need a hand with something. I have an array formula that goes in several cells. Using what I found online threw several searches, I found the following (which is only a part of the code): MIN(ROW(Color!$A$2:$A$500))+1), ROW(1:1). It works perfectly and when I drag it down, the row reference changes from ROW(1:1) to ROW(2:2), as it should. If I wanted to use VBA to place this array in the cells though, how would I reference the ROW(1:1) and get it to change to ROW(2:2), etc.? Nothing I have tried as worked. Any suggestions would be welcome!
    Thank you!
    Amy

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,113
    Location
    [VBA]
    Dim LR As Long 'Holds the last row number you put the formula in
    Dim i As Long
    For i = 1 to LR
    Cells(i,ColumnReference).Formula = "=MIN(...etc),ROW(" & i & ":" & i & ")"[/VBA]

    If the formula goes in many cells, you will get a smaller file size doing the calculations in VBA and putting only the result in the Cell. Youcan to use an Event to trigger the procedure, (AKA, macro.)
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  3. #3
    Hi Sam,
    Thank you so much! How do I do the calculations in VBA, like you mentioned, and just put the results in the cell? I think that is really what I want to do.
    Thanks again!
    Amy

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,113
    Location
    What is the formula? Include workbook references even if they are not in the cell's formula.

    Cell formula on sheet1 example
    • =A1+Sheet2!A1
    Formula you show me
    • =Sheet1!A1+Sheet2!A1
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  5. #5
    Hi Sam,
    This is the full formula:

    =IFERROR(INDEX(Data!$A$2:$A$500, SMALL(IF(ISBLANK(Data!$A$2:$A$500)+ISERROR(Data!$A$2:$A$500)++IF(Data!$B$2: $B$500<>A$1,1,0), "", ROW(Data!$A$2:$A$500)-MIN(ROW(Data!$A$2:$A$500))+1), ROW(1:1))),"")

    Thanks!
    Amy

Posting Permissions

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