Incriment veriable in a VBA formula

[ThaiLove]

Hi Guys,

I've been working on this for a while, I feel like I'm close, but I'm not playing horse shoes... I wouldn't bother with VBA, but Excel can't seem to be able to pick up the pattern

My goal is to get the following formulas in to cells I3 through I100

I3 should have: =INDEX(J$2:J$2,MAX(IF((C$2:C$2=C$3),ROW(J$2:J$2)-1)))
I4 should have: =INDEX(J$2:J$3,MAX(IF((C$2:C$3=C$4),ROW(J$2:J$3)-1)))
I5 should have: =INDEX(J$2:J$4,MAX(IF((C$2:C$4=C$5),ROW(J$2:J$4)-1)))
I6 should have: =INDEX(J$2:J$5,MAX(IF((C$2:C$5=C$6),ROW(J$2:J$5)-1)))
...
I100 should have =INDEX(J$2:J$99,MAX(IF((C$2:C$99=C$100),ROW(J$2:J$99)-1)))

Thanks a bunch,

ThaiLove


Sub FindPrevious()
Dim LastV As Byte
Dim CurV As Byte

LastV = 2
CurV = 3
For Row = 3 To 100

wanted = "=INDEX(J$2:J$LastV,MAX(IF((C$2:C$LastV=C$CurV),ROW(J$2:J$LastV)-1)))"

Cells(Row, "I").FormulaR1C1 = wanted
LastV + 1
CurV + 1
Next Row
End Sub

 

[circledchicken]

Hi,

You don't seem to be using the dollar signs correctly. Perhaps try this in I3 and drag down:
=INDEX(J$2:J2,MAX(IF((C$2:C2=C3),ROW(J$2:J2)-1)))

Here are some resources if you want to explore further:

• Office 2010 Class #25: Excel Cell References: Relative, Absolute, Mixed - YouTube
• Switch between relative, absolute, and mixed references - Excel - Office.com
• Relative And Absolute Range References

Also if you wanted to do this with VBA, you don't need to loop through each cell - you can apply the formula in a single step like this, and it will automatically increment the references where required:

Sub example()

    Range("I3:I100").Formula = "=INDEX(J$2:J2,MAX(IF((C$2:C2=C3),ROW(J$2:J2)-1)))"

End Sub

 

[p45cal]

try:

Sub blah()
For Rw = 3 To 100
Cells(Rw, "I").FormulaR1C1 = "=INDEX(R2C[1]:R" & Rw - 1 & "C[1],MAX(IF((R2C[-6]:R" & Rw - 1 & "C[-6]=R" & Rw & "C[-6]),ROW(R2C[1]:R" & Rw - 1 & "C[1])-1)))"
Next Rw
End Sub

edit post posting: though circledchicken seems to have it right; the eqiuvalent with R1C1 formulae is
Range("I3:I8").FormulaR1C1 ="=INDEX(R2C[1]:R[-1]C[1],MAX(IF((R2C[-6]:R[-1]C[-6]=RC[-6]),ROW(R2C[1]:R[-1]C[1])-1)))"

 

[ThaiLove]

Thank you both, I've tested all 3 solutions & they work great!

 

[circledchicken]

Thank you both, I've tested all 3 solutions & they work great!

You're welcome - thanks for the feedback.

 

[ThaiLove]

You're welcome - thanks for the feedback.

Thanks CircleChicken, the links you provided were very helpful also! Learnt a few of the basics I haven't learnt elsewhere

 

[circledchicken]

Thanks CircleChicken, the links you provided were very helpful also! Learnt a few of the basics I haven't learnt elsewhere

Nice, I'm glad you found them useful.

There is also a more general and comprehensive list of resources listed by hiker95 here:
http://www.mrexcel.com/forum/genera...training-courses-suggestions.html#post3203454

 

[ThaiLove]

I'm sooooo close to my ultimate goal, it's not funny. I've created a user form like the image shown & all I need to do is figure out how to make the formula we've made "ActiveCell" aware. Below is my code, I just need the formula to be relative to the row being submitted, any ideas?

I need when the active cell is 6, the formula entered in cell I6 to be : =INDEX(J$2:J5,MAX(IF((C$2:C5=C6),ROW(J$2:J5)-1)))
And if the active cell is 10, the formula entered in cell I6 to be : =INDEX(J$2:J9,MAX(IF((C$2:C9=C10),ROW(J$2:J9)-1)))

I tried the below, It does put the formula in the proper cell, but it doesn't set the values to what I need, it's entered exactly as seen:

ActiveCell.Offset(0, 8).Formula = "=INDEX(J$2:J2,MAX(IF((C$2:C2=C3),ROW(J$2:J2)-1)))"

And I tried the below, I feel like this is closer to what I need as the formula would be relative to the Rw number, but it just goes into debug mode...

Rw = ActiveCell
ActiveCell.Offset(0, 8).FormulaR1C1 = "=INDEX(R2C[1]:R" & Rw - 1 & "C[1],MAX(IF((R2C[-6]:R" & Rw - 1 & "C[-6]=R" & Rw & "C[-6]),ROW(R2C[1]:R" & Rw - 1 & "C[1])-1)))"

I'm sure my code is not the cleanest, but I'll just be happy to have a working script

My code:

' CLICK ON CLOSE
Private Sub cmdCancel_Click()
    Unload Me
End Sub
' CLICK ON CLEAR
Private Sub cmdClearForm_Click()
    Call UserForm_Initialize
End Sub
' CLICK ON SUBMIT
Private Sub cmdOK_Click()
    ActiveWorkbook.Sheets("Investment transactions").Activate
    Range("A1").Select
    Do
    If IsEmpty(ActiveCell) = False Then
        ActiveCell.Offset(1, 0).Select
    End If
    Loop Until IsEmpty(ActiveCell) = True
    
    ' When Column A is clear of text do the following...
    ActiveCell.Value = txtDate.Value
    ActiveCell.Offset(0, 5) = TBTradeFee.Value
    ActiveCell.Offset(0, 2) = StockChoice.Value
                ActiveCell.Offset(0, 3) = TBNumOfShares.Value
                ActiveCell.Offset(0, 4) = TBPricePerShare.Value
                ActiveCell.Offset(0, 9) = TBTotalShares.Value
                ActiveCell.Offset(0, 14) = TBTotalPrice.Value

If OptBuy = True And optDirect Then
            Call UserForm_BuyDirect

ElseIf OptBuy = True And optInverted Then
            Call UserForm_BuyInverted

End If

If OptSell = True And optDirect Then
            Call UserForm_SellDirect

ElseIf OptSell = True And optInverted Then
            Call UserForm_SellInverted
End If
        Range("A1").Select
            Call UserForm_Initialize
End Sub
' CLICK ON Direct Stock Purchase
Private Sub optDirect_Click()
        If OptBuy = True Then
            Call UserForm_BuyDirect
        ElseIf optInverted = True Then
            Call UserForm_SellDirect
        End If
End Sub
' CLICK ON Inverted Stock Purchase
Private Sub optInverted_Click()
        If OptBuy = True Then
            Call UserForm_BuyInverted
        ElseIf optInverted = True Then
            Call UserForm_SellInverted
        End If
End Sub
' INITIAL VALUES OF THE FORM
Private Sub UserForm_Initialize()
    txtDate.Value = "DD/MM/YYYY"
    txtDate.SetFocus
    OptBuy = True
    TBTradeFee.Value = ""
    With StockChoice
        .AddItem "Bimcor Income Fund"
        .AddItem "TDAM Balanced Index Fund"
        .AddItem "TDAM Canadian Bond Index Fund"
        .AddItem "TDAM Canadian Equity Index Fund"
        .AddItem "TDAM U.S. Equity Index Fund"
        .AddItem "TDAM International Equity Index Fund"
        .AddItem "XIC.TO"
        .AddItem "BCE.TO"
        .AddItem "LLBO"
        .AddItem "MOAT"
        .AddItem "XWD.TO"
        .AddItem "XBB.TO"
    End With
    StockChoice.Value = ""
    optDirect = True
    TBNumOfShares.Value = ""
    TBPricePerShare.Value = ""
    TBTotalShares.Value = ""
    TBTotalPrice.Value = ""
    TBTotalShares.Locked = True
    TBTotalShares.Visible = False
    LabeTotalShares.Visible = False
    TBTotalPrice.Locked = True
    TBTotalPrice.Visible = False
    LabelTotalPrice.Visible = False

End Sub

Private Sub UserForm_BuyDirect()


                ActiveCell.Offset(0, 1).Value = "Buy"
                
                LabelNumOfShares.Visible = True
                LabePricePerShare.Visible = True
                LabelTotalPrice.Visible = False
                LabeTotalShares.Visible = False
                
                TBNumOfShares.Value = ""
                TBPricePerShare.Value = ""
                
                TBNumOfShares.Locked = False
                TBPricePerShare.Locked = False
                TBTotalShares.Locked = True
                TBTotalPrice.Locked = True
                
                TBNumOfShares.Visible = True
                TBPricePerShare.Visible = True
                TBTotalShares.Visible = False
                TBTotalPrice.Visible = False
[highlight]
                Rw = ActiveCell
                ActiveCell.Offset(0, 8).FormulaR1C1 = "=INDEX(R2C[1]:R"  & Rw - 1 & "C[1],MAX(IF((R2C[-6]:R" & Rw - 1 & "C[-6]=R"  & Rw & "C[-6]),ROW(R2C[1]:R" & Rw - 1 & "C[1])-1)))"
[/highlight]
        Range("A1").Select
End Sub

Private Sub UserForm_BuyInverted()
                ActiveCell.Offset(0, 1).Value = "Buy"
                
                LabelNumOfShares.Visible = False
                LabePricePerShare.Visible = False
                LabelTotalPrice.Visible = True
                LabeTotalShares.Visible = True
                
                TBTotalShares.Value = ""
                TBTotalPrice.Value = ""
                
                TBNumOfShares.Locked = True
                TBPricePerShare.Locked = True
                TBTotalShares.Locked = False
                TBTotalPrice.Locked = False
                
                TBNumOfShares.Visible = False
                TBPricePerShare.Visible = False
                TBTotalShares.Visible = True
                TBTotalPrice.Visible = True
        Range("A1").Select
End Sub

Private Sub UserForm_SellDirect()
                ActiveCell.Offset(0, 1).Value = "Sell"
                
                LabelNumOfShares.Visible = True
                LabePricePerShare.Visible = True
                LabelTotalPrice.Visible = False
                LabeTotalShares.Visible = False
                
                TBNumOfShares.Value = ""
                TBPricePerShare.Value = ""
                
                TBNumOfShares.Locked = False
                TBPricePerShare.Locked = False
                TBTotalShares.Locked = True
                TBTotalPrice.Locked = True
                
                TBNumOfShares.Visible = True
                TBPricePerShare.Visible = True
                TBTotalShares.Visible = False
                TBTotalPrice.Visible = False
        Range("A1").Select
End Sub

Private Sub UserForm_SellInverted()
                ActiveCell.Offset(0, 1).Value = "Sell"
                
                LabelNumOfShares.Visible = False
                LabePricePerShare.Visible = False
                LabelTotalPrice.Visible = True
                LabeTotalShares.Visible = True
                
                TBNumOfShares.Value = ""
                TBPricePerShare.Value = ""
                
                TBNumOfShares.Locked = True
                TBPricePerShare.Locked = True
                TBTotalShares.Locked = False
                TBTotalPrice.Locked = False
                
                TBNumOfShares.Visible = False
                TBPricePerShare.Visible = False
                TBTotalShares.Visible = True
                TBTotalPrice.Visible = True
        Range("A1").Select
End Sub

 

[circledchicken]

Hi,

I haven't looked through all your code, but for the specific part you highlighted, maybe try:

    Rw = ActiveCell[COLOR=#800000][B].Row[/B][/COLOR]
    ActiveCell.Offset(0, 8).Formula = _
        "=INDEX(J$2:J" & Rw - 1 & ",MAX(IF((C$2:C" & Rw - 1 & "=C" & Rw & "),ROW(J$2:J" & Rw - 1 & ")-1)))"

 

[ThaiLove]

Thanks! dead on, works perfectly, the only thing I wonder is if there's a way for it to give me the output right away? When I add your code, the proper formula is there, the only thing is I have to get into the formula & click "Ctrl + Shift + Enter" to get the brackets "{}" around the formula & give me the result I want. Is there a bypass for this?

Thanks a lot TL