Hi guys
Currently, this is how my spreadsheet works. I have a list of data which actually list out the amount of stock data which I am currently purchasing. I categorise them under each of their specific stock codes and the two countries which I am purchasing under. So in this case, my spreadsheet resembles something like this:
COUNTRY.STOCK CODE.STOCK NAME
USA..........123.................GOOGLE
USA..........123.................GOOGLE
USA..........123.................GOOGLE
........................................................
USA..........456.................APPLE
USA..........456..................APPLE
........................................................
KOREA......789.................SAMSUNG
KOREA.......789................SAMSUNG
of which each of this entry are purchased in different dates.
Now, I want to create a VBA macro button (which releases a form box) that enters in a new row and update any purchase I have done today.
But here is the problem, if today I continue to purchase GOOGLE, I will want the macro to insert a new row right under GOOGLE but if I made a totally new purchase, I want it to leave a blank row, then create a new row right under the country which it falls under and then insert the relevant data.
Here is the code which I have created but somehow, it just doesn't work.
-----------------------------------------------------------------------------------------------------------------
Sub NewEntry()
Dim i, LastRow
Dim myForm As NewEntry
Set myForm = New NewEntry
LastRow = Range("B" & Rows.Count).End(xlUp).Row
On Error GoTo errhandler
stkCode = InputBox("Please enter the Equity Code.", "Stock Code")
If stkCode = "" Then
Exit Sub
End If
myForm.Show
Select Case myForm.Tag
Case 1
countryType = "USA"
Case 2
countryType = "KOREA"
End Select
Unload myForm
Set myForm = Nothing
For i = LastRow + 1 To 1 Step -1
If Cells(i - 1, "B").Value = countryType And Cells(i - 1, "E").Value = stkCode Then
Rows(i & ":" & i).Select
Selection.Insert Shift:=xlUp
Cells(i, "B").Select
Cells(i, "B").Value = secType
Cells(i, "E").Value = stkCode
'Other relevant data input to be entered here.
ElseIf Cells(i - 1, "B").Value = countryType Then
Rows(i + 1 & ":" & i + 1).Select
Selection.Insert Shift:=xlUp
Cells(i + 1, "B").Select
Cells(i + 1, "B").Value = secType
Cells(i + 1, "E").Value = stkCode
'Other relevant data input to be entered here.
Rows(i + 2 & ":" & i + 2).Select
Selection.Insert Shift:=xlUp
End If
Exit Sub
errhandler:
Cells(LastRow + 1, "B").Value = secType
Cells(LastRow + 1, "C").Value = stkCode
MsgBox "Error!", _
vbInformation, "ERROR"
Next
End Sub
-----------------------------------------------------------------------------------------------------------------------
Please help! Many thanks!
Bookmarks