+ Reply to Thread
Results 1 to 5 of 5

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

Hybrid 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.

  2. #2
    Registered User
    Join Date
    05-19-2011
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2010
    Posts
    33

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

    Try replacing this line...
    ' Write data to worksheet
        RowCount = Worksheets("Custom Built Button").Range("A3").CurrentRegion.Rows.Count
    ...with this...
    ' Write data to worksheet
       RowCount = Worksheets("Custom Built Button").Cells(Rows.Count,"A").End(xlUp).Row-2

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

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

    Quote Originally Posted by JS411 View Post
    Try replacing this line...
    ' Write data to worksheet
        RowCount = Worksheets("Custom Built Button").Range("A3").CurrentRegion.Rows.Count
    ...with this...
    ' Write data to worksheet
       RowCount = Worksheets("Custom Built Button").Cells(Rows.Count,"A").End(xlUp).Row-2
    That seems to do the trick! I will have to wait until monday to test it fully, but I believe that will work. Thanks!

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

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

    Worked like a charm. I do have one additional question that you may be able to help with. I was hoping to have it copy down the formatting from above, specifically the boarders. I have everything with information set up to show all boarders. I know when you use the Excel Data Form, it will copy those boards down to a new row. Can this be done in a User Form?

  5. #5
    Registered User
    Join Date
    05-19-2011
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2010
    Posts
    33

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

    If the formatting (including borders) will be the same as the row above where the new data is being entered, then you can copy the previous row and PasteSpecial(xlPasteFormats) into the new row.
    You'll have to consider how this would be handled for the first row of data if you have a header row with different formatting.

    The other option would be to add the borders to the new row of data. You can record a macro to get the syntax if you decide to go that route.

+ Reply to Thread

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