VBA: Entering Entry for New/Existing Data

[danzing87]

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!

[danzing87]

By the way, the formbox is working fine. So I doubt there is any issue from the formbox codes.

thanks

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here