Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / June 2006

Tip: Looking for answers? Try searching our database.

Write data in Last Row-column "B"

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
maperalia - 23 Jun 2006 19:12 GMT
I have I macro that write a database into the last row starting in the column
A (see program below).
However, I have a table that has the column A already filled out with
numbers. Therefore, I would like my macro to start writing the last empty row
in the column B.
Could you please tell me what are the modifications I need to do to my macro
in order to make it work in the way I mentioned above.

Thanks in advance.
Maperalia

'Start Program

'********************************
'CREATE DATABASE

Sub Create database()
Dim sr As Range
Dim dr As Range
Dim dwb As Workbook
Dim Lr As Long
Dim hl As String

'Where:
   'Lr = Last Row
   'dr = destination Range
   'dwb = destination WorkBook
   'sr = source range
'***************************************************
'***************************************************
'OPEN THE FILE TO ARCHIVE INFORMATION

   Application.ScreenUpdating = False
 
   If bIsBookOpen("Database.xls") Then
       Set dwb = Workbooks("Database.xls")
   Else
       
       Set dwb = Workbooks.Open("C:\Database\Database.xls")
   End If
   
'*******************************************
'WRITE THE DATABASE

   Application.ScreenUpdating = False

   Lr = LastRow(dwb.Worksheets("Database")) + 1
   Set sr = ThisWorkbook.Worksheets("Form").Range("V2:AE2")
   
   '***********************************
   'If Shear Log is Open
   On Error Resume Next
    dwb.Close True
   SetAttr "C:\Test\Database.xls", vbNormal
   Set dwb = Workbooks.Open("C:\Test\Database.xls")
   On Error GoTo 0
   
   '***********************************
   
Sheets("Database").Select
   
   Set dr = dwb.Worksheets("Database").Range("A" & Lr)
   sr.Copy
   dr.PasteSpecial xlPasteValues, , False, False
   Application.CutCopyMode = False
   
'********************************************************
   dwb.Close True
   Application.ScreenUpdating = False

End sub

'********END CREATE DATABASE*****************************

Function bIsBookOpen(ByRef szBookName As String) As Boolean

Application.ScreenUpdating = False
 
  On Error Resume Next
   bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)

End Function

Function LastRow(sh As Worksheet)

Application.ScreenUpdating = False
   
   On Error Resume Next
   LastRow = sh.Cells.Find(What:="*", Lookat:=xlPart, LookIn:=xlFormulas,
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
   On Error GoTo 0
End Function
Tom Ogilvy - 23 Jun 2006 20:05 GMT
Function LastRow(sh As Worksheet)

Application.ScreenUpdating = False
   
   On Error Resume Next
   LastRow = sh.Cells.Find(What:="*", Lookat:=xlPart, LookIn:=xlFormulas,
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
   On Error GoTo 0
End Function

would become

Function LastRow(sh As Worksheet)
   LastRow = sh.cells(rows.count,"B").End(xlup).Row
End Function

Signature

Regards,
Tom Ogilvy

> I have I macro that write a database into the last row starting in the column
> A (see program below).
[quoted text clipped - 88 lines]
>     On Error GoTo 0
> End Function
maperalia - 23 Jun 2006 21:41 GMT
Tom;
Thank you very much. It is working WORDEFULL!!!...

Kind regards.
Maperalia

> Function LastRow(sh As Worksheet)
>
[quoted text clipped - 104 lines]
> >     On Error GoTo 0
> > End Function
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.