Thread: VBA: Entering Entry for New/Existing Data

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!

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

thanks