Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help on populating a newly inserted row
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help on populating a newly inserted row
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 " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Carrying over formulas into newly inserted row | Excel Discussion (Misc queries) | |||
Automatically fill one cell in any newly inserted row | Excel Discussion (Misc queries) | |||
Naming a newly inserted sheet using VBA | Excel Discussion (Misc queries) | |||
Get the name of a newly copied worksheet | Excel Programming | |||
Add Hyperlink to newly added worksheet? | Excel Programming |