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