Results 1 to 5 of 5

Enter Information in next blank link (even if some columns have formulas

Threaded View

  1. #1
    Registered User
    Join Date
    06-22-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2007
    Posts
    18

    Enter Information in next blank link (even if some columns have formulas

    Okay, so I am trying to set this User Form to autofill the next blank line. It should, and does, fill the information into columns A:D. However, I have formulas in columns E:L and when I fill those formula's down (so that they will autofill as new information is added through the form) the macro forces the information to the bottom of the rows (the first row with no formula's filled down in E:L)

    How can I change this to have it fill based on the blanks only being found in A:D (and ignore the formula's in E:L)

    Someone fix this to insert the info in the next blank line between A:D (formulas are in E and on, so its skipping to the bottom of where the formulas are)

    Problem Coding:
    ' Write data to worksheet
        RowCount = Worksheets("Custom Built Button").Range("A3").CurrentRegion.Rows.Count
        With Worksheets("Custom Built Button").Range("A3")
            .Offset(RowCount, 0).Value = Me.IC_Name.Value
            .Offset(RowCount, 1).Value = Me.cmboOpportunity.Value
            .Offset(RowCount, 2).Value = Me.CostCenter.Value
            .Offset(RowCount, 3).Value = Me.ExpenseCat.Value
        End With
    Entire Code:
    Private Sub UserForm_Initialize()
        Dim c As Range
        With Sheets("SFICInfo")
            For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
                If c.Value <> "" Then IC_Name.AddItem c.Value
            Next c
        End With
        With Sheets("SFWorkOrder")
            For Each c In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
                If c.Value <> "" Then cmboOpportunity.AddItem c.Value
            Next c
        End With
    End Sub
    Private Sub cmdOK_Click()
        Dim RowCount As Long
        Dim ctl As Control
        If Me.IC_Name.Value = "" Then
            MsgBox "Please enter IC Name.", vbExclamation, "Information Missing"
            Me.IC_Name.SetFocus
            Exit Sub
        End If
        If Me.cmboOpportunity.Value = "" Then
            MsgBox "Please choose an Opportunity.", vbExclamation, "Information Missing"
            Me.cmboOpportunity.SetFocus
            Exit Sub
        End If
            If Me.CostCenter.Value = "" Then
            MsgBox "Please enter a Cost Center.", vbExclamation, "Information Missing"
            Me.CostCenter.SetFocus
            Exit Sub
        End If
            If Me.ExpenseCat.Value = "" Then
            MsgBox "Please enter an Expense Catagory.", vbExclamation, "Information Missing"
            Me.ExpenseCat.SetFocus
            Exit Sub
        End If
    ' Write data to worksheet
        RowCount = Worksheets("Custom Built Button").Range("A3").CurrentRegion.Rows.Count
        With Worksheets("Custom Built Button").Range("A3")
            .Offset(RowCount, 0).Value = Me.IC_Name.Value
            .Offset(RowCount, 1).Value = Me.cmboOpportunity.Value
            .Offset(RowCount, 2).Value = Me.CostCenter.Value
            .Offset(RowCount, 3).Value = Me.ExpenseCat.Value
        End With
    ' Clear the from
      For Each ctl In Me.Controls
            If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
                ctl.Value = ""
            End If
        Next ctl
    End Sub
    edit: I would post the file, but due to security reasons, I am not really supposed to.
    Last edited by kgreave1; 06-22-2012 at 06:57 PM.

Thread Information

Users Browsing this Thread

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

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