Need Help on populating a newly inserted row

[eainmetlae]

Hi all,
I m quite new to in-depth vba programming and i need help for this!
Scenario
I've some record of books (wth 6 columns- code, title, author,
published year, entry date, and loan status). I m trying to write some
macro to add a book (increase quantity) to existing title.
the code field contains two part say, 'b001-01" the part behind 01
indicates there s one book in this title. If a book of same title s
to be added, the code of new book wil be 'b001-02", etc and all other
data are the same as of b001-01 except for code(obvious) and entry
date (which is the current date).
Following is my code. But i m stuck at inserting and populating the
new row. any suggestion???


Private Sub cmdAdd_Click()

Dim strFind As String
Dim n As Integer
Dim lno As Long
Dim c, bookdata, newbdata As Range
Dim dummystr, strCode, strNewCode, strTitle, strAuthor, strYear,
strEntDate, strStatus As String
'Dim bookdata As Variant


If CStr(lst_SearchRes.Value) = "" Then
MsgBox ("Please select one of the titles from the search
result!")
Else
strFind = lst_SearchRes.Value
MsgBox ("Warning: You are adding another book of the same
title!")
'look for last occuring row
Set c = Sheets("Main
Data").Range("B:B").Find(What:=myFindString, After:=Range("B1"),
LookIn:=xlValues, LookAt:=xlPart, Searchdirection:=xlPrevious)
Set bookdata = c.EntireRow
Sheets("Main Data").Range("A1").Select
Selection.End(xlDown).Select
Selection.EntireRow.Insert

'error saying type mismatch
With ActiveSheet.UsedRange
lno = Cells(Rows.Count, "A").End(xlUp).Rows.Count
lno = lno - 1
End With


'Retrieve first code
strCode = ExtractElement(CStr(bookdata.Cells(1, 1)), 2, "-")
dummystr = Right(CStr(bookdata.Cells(1, 1)), 1)
If IsNumeric(dummystr) = True Then
n = dummystr
End If

n = n + 1
strNewCode = CStr(strCode & "-" & CStr(n))

strTitle = CStr(bookdata.Cells(1, 2))
strAuthor = CStr(bookdata.Cells(1, 3))
strYear = CStr(bookdata.Cells(1, 4))

strStatus = CStr(bookdata.Cells(1, 6))

'add record

newbdata = Range(Cells(lno, 1), Cells(lno, 6))
newbdata.Cells(lno, 1) = strNewCode
newbdata.Cells(lno, 2) = strTitle
newbdata.Cells(lno, 3) = strAuthor
newbdata.Cells(lno, 4) = strYear
newbdata.Cells(lno, 5) = Now()
newbdata.Cells(lno, 6) = strStatus

'sort data
Worksheets("Main Data").Range("A1").Sort _
Key1:=Worksheets("Main Data").Columns("A"), _
Header:=xlGuess

End If
End Sub


Your help is very much appreciated!
Thanks in advance,
NooN_YUki

 

[Joel]

I made some changes. You don't need to use CSTR because excel automatically
will convert to a string. Her are some tips


FirstRow = 4
LastRow = 8
Range(Firstrow & ":" LastRow).copy

MyRow = 7
Range("A1") = Range("A" & MyRow)

See code below. I changed the Sort statement and a few other changes to
show you a different way of doing some of the operations.

Private Sub cmdAdd_Click()

Dim strFind As String
Dim n As Integer
Dim lno As Long
Dim c, bookdata, newbdata As Range
Dim dummystr, strCode, strNewCode, strTitle, strAuthor, strYear,
strEntDate, strStatus As String
'Dim bookdata As Variant


If CStr(lst_SearchRes.Value) = "" Then
MsgBox ("Please select one of the titles from the search
result!")
Else
strFind = lst_SearchRes.Value
MsgBox ("Warning: You are adding another book of the same
title!")
'look for last occuring row
Set c = Sheets("Main
Data").Range("B:B").Find(What:=myFindString, After:=Range("B1"),
LookIn:=xlValues, LookAt:=xlPart, Searchdirection:=xlPrevious)
Set bookdata = c.EntireRow
set lastrow = Sheets("Main Data").Range("A1").End(xlDown)
Last.EntireRow.Insert

'error saying type mismatch
With ActiveSheet.UsedRange
lno = Cells(Rows.Count, "A").End(xlUp).Rows.Count
lno = lno - 1
End With


'Retrieve first code
strCode = ExtractElement(CStr(bookdata.Cells(1, 1)), 2, "-")
dummystr = Right(CStr(bookdata.Cells(1, 1)), 1)
If IsNumeric(dummystr) = True Then
n = dummystr
End If

n = n + 1
'don't use cstr because it add a blank space in front of the number
'The blank space is put in incase you have a + or - infront of the
number
strNewCode = strCode & "-" & CStr(n)

strTitle = bookdata.Cells(1, 2)
strAuthor = bookdata.Cells(1, 3)
strYear = bookdata.Cells(1, 4)

strStatus = bookdata.Cells(1, 6)

'add record

newbdata = Range(Cells(lno, 1), Cells(lno, 6))
newbdata.Cells(lno, 1) = strNewCode
newbdata.Cells(lno, 2) = strTitle
newbdata.Cells(lno, 3) = strAuthor
newbdata.Cells(lno, 4) = strYear
newbdata.Cells(lno, 5) = Now()
newbdata.Cells(lno, 6) = strStatus


'sort data
Worksheets("Main Data").Rows("1:" & lno).Sort _
Key1:=Worksheets("Main Data").Columns("A"), _
Header:=xlGuess

End If
End Sub

 

[eainmetlae]

I made some changes. You don't need to use CSTR because excel automatically
will convert to a string. Her are some tips

FirstRow = 4
LastRow = 8
Range(Firstrow & ":" LastRow).copy

MyRow = 7
Range("A1") = Range("A" & MyRow)

See code below. I changed the Sort statement and a few other changes to
show you a different way of doing some of the operations.

Private Sub cmdAdd_Click()

Dim strFind As String
Dim n As Integer
Dim lno As Long
Dim c, bookdata, newbdata As Range
Dim dummystr, strCode, strNewCode, strTitle, strAuthor, strYear,
strEntDate, strStatus As String
'Dim bookdata As Variant

If CStr(lst_SearchRes.Value) = "" Then
MsgBox ("Please select one of the titles from the search
result!")
Else
strFind = lst_SearchRes.Value
MsgBox ("Warning: You are adding another book of the same
title!")
'look for last occuring row
Set c = Sheets("Main
Data").Range("B:B").Find(What:=myFindString, After:=Range("B1"),
LookIn:=xlValues, LookAt:=xlPart, Searchdirection:=xlPrevious)
Set bookdata = c.EntireRow
set lastrow = Sheets("Main Data").Range("A1").End(xlDown)
Last.EntireRow.Insert

'error saying type mismatch
With ActiveSheet.UsedRange
lno = Cells(Rows.Count, "A").End(xlUp).Rows.Count
lno = lno - 1
End With

'Retrieve first code
strCode = ExtractElement(CStr(bookdata.Cells(1, 1)), 2, "-")
dummystr = Right(CStr(bookdata.Cells(1, 1)), 1)
If IsNumeric(dummystr) = True Then
n = dummystr
End If

n = n + 1
'don't use cstr because it add a blank space in front of the number
'The blank space is put in incase you have a + or - infront of the
number
strNewCode = strCode & "-" & CStr(n)

strTitle = bookdata.Cells(1, 2)
strAuthor = bookdata.Cells(1, 3)
strYear = bookdata.Cells(1, 4)

strStatus = bookdata.Cells(1, 6)

'add record

newbdata = Range(Cells(lno, 1), Cells(lno, 6))
newbdata.Cells(lno, 1) = strNewCode
newbdata.Cells(lno, 2) = strTitle
newbdata.Cells(lno, 3) = strAuthor
newbdata.Cells(lno, 4) = strYear
newbdata.Cells(lno, 5) = Now()
newbdata.Cells(lno, 6) = strStatus

'sort data
Worksheets("Main Data").Rows("1:" & lno).Sort _
Key1:=Worksheets("Main Data").Columns("A"), _
Header:=xlGuess

End If
End Sub

Hi Joel
Thanks for your prompt reply.. and i ve a question. (and sorry for
being so thick)
i ve no idea where to put these lines.

FirstRow = 4
LastRow = 8
Range(Firstrow & ":" LastRow).copyMyRow = 7
Range("A1") = Range("A" & MyRow)

Thnx agn
NooN

 

[Joel]

those rows weren't meant to be putting into a macro. there were just example
of a different style of programming where excel will automatically combine a
string and a number together.