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:
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