Results 1 to 14 of 14

Next empty row - excluding formulas

Threaded View

mackenzie.costello Next empty row - excluding... 03-11-2015, 04:16 PM
TMS Re: Next empty row -... 03-11-2015, 04:29 PM
mackenzie.costello Re: Next empty row -... 03-13-2015, 08:38 AM
TMS Re: Next empty row -... 03-13-2015, 08:56 AM
TMS Re: Next empty row -... 03-11-2015, 04:31 PM
TMS Re: Next empty row -... 03-13-2015, 08:53 AM
mackenzie.costello Re: Next empty row -... 03-13-2015, 09:15 AM
JOHN H. DAVIS Re: Next empty row -... 03-13-2015, 09:26 AM
mackenzie.costello Re: Next empty row -... 03-13-2015, 09:42 AM
JOHN H. DAVIS Re: Next empty row -... 03-13-2015, 10:42 AM
TMS Re: Next empty row -... 03-13-2015, 11:15 AM
mackenzie.costello Re: Next empty row -... 03-13-2015, 11:32 AM
TMS Re: Next empty row -... 03-13-2015, 12:07 PM
TMS Re: Next empty row -... 03-13-2015, 12:01 PM
  1. #1
    Registered User
    Join Date
    03-11-2015
    Location
    London, Canada
    MS-Off Ver
    2013
    Posts
    24

    Next empty row - excluding formulas

    Hello all,

    I am making a userform for data entry and I am wanting my data to go in the next empty row, however there is one column that contains a formula all the way down the spreadsheet. Is there a way to have my code go to the next empty row ignoring the formulas?

    Here is what I have

    Private Sub CommandButton1_Click()
    
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("MSDS DATABASE")
    
    'find first empty row in database
    iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
    'check for a part number
    If Trim(Me.txtreview.Value) = "" Then
      Me.txtchem.SetFocus
      MsgBox "Please enter a review date"
      Exit Sub
    End If
    
    'copy the data to the database
    'use protect and unprotect lines,
    '     with your password
    '     if worksheet is protected
    With ws
    '  .Unprotect Password:="password"
      .Cells(iRow, 1).Value = Me.txtchem.Value
      .Cells(iRow, 2).Value = Me.txtform.Value
      .Cells(iRow, 3).Value = Me.txtconc.Value
      .Cells(iRow, 4).Value = Me.txtpro.Value
      .Cells(iRow, 5).Value = Me.txtamount.Value
      .Cells(iRow, 6).Value = Me.txtman.Value
      .Cells(iRow, 7).Value = Me.txtlab.Value
      .Cells(iRow, 9).Value = Me.txtreview.Value
      .Cells(iRow, 11).Value = Me.txtpg.Value
    
    '  .Protect Password:="password"
    End With
    
    'clear the data
    Me.txtchem.Value = ""
    Me.txtform.Value = ""
    Me.txtconc.Value = ""
    Me.txtpro.Value = ""
    Me.txtamount.Value = ""
    Me.txtman.Value = ""
    Me.txtlab.Value = ""
    Me.txtreview.Value = ""
    Me.txtpg.Value = ""
    Me.txtchem.SetFocus
    
    
    If Me.cbA.Value = True Then
    ws.Cells(iRow, 8).Value = "A"
    Else
    ws.Cells(iRow, 8).Value = ""
    End If
    
    If Me.cbB.Value = True Then
    ws.Cells(iRow, 8).Value = "B"
    Else
    ws.Cells(iRow, 8).Value = ""
    End If
    
    If Me.cbC.Value = True Then
    ws.Cells(iRow, 8).Value = "C"
    Else
    ws.Cells(iRow, 8).Value = ""
    End If
    
    If Me.cbD1A.Value = True Then
    ws.Cells(iRow, 8).Value = "D1A"
    Else
    ws.Cells(iRow, 8).Value = ""
    End If
    
    If Me.cbD1B.Value = True Then
    ws.Cells(iRow, 8).Value = "D1B"
    Else
    ws.Cells(iRow, 8).Value = ""
    End If
    
    If Me.cbD2A.Value = True Then
    ws.Cells(iRow, 8).Value = "D2A"
    Else
    ws.Cells(iRow, 8).Value = ""
    End If
    
    If Me.cbD2B.Value = True Then
    ws.Cells(iRow, 8).Value = "D2B"
    Else
    ws.Cells(iRow, 8).Value = ""
    End If
    
    If Me.cbD3.Value = True Then
    ws.Cells(iRow, 8).Value = "D3"
    Else
    ws.Cells(iRow, 8).Value = ""
    End If
    
    If Me.cbE.Value = True Then
    ws.Cells(iRow, 8).Value = "E"
    Else
    ws.Cells(iRow, 8).Value = ""
    End If
    
    If Me.cbF.Value = True Then
    ws.Cells(iRow, 8).Value = "F"
    Else
    ws.Cells(iRow, 8).Value = ""
    End If
    
    End Sub
    Last edited by mackenzie.costello; 03-13-2015 at 09:15 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 08-29-2014, 08:31 AM
  2. [SOLVED] Macro to Consolidate workbooks containing empty rows in between and excluding a column
    By retrospikz in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-17-2013, 01:28 AM
  3. find first empty row excluding column A
    By timarcarze in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-03-2011, 10:38 PM
  4. function to count numbers excluding empty cells
    By nickelcell1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-07-2010, 03:10 PM
  5. Average Formula Excluding Empty Cells
    By Alhazred in forum Excel General
    Replies: 5
    Last Post: 03-21-2008, 08:31 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1