+ Reply to Thread
Results 1 to 2 of 2

Formula added via a user form

  1. #1
    Registered User
    Join Date
    09-11-2013
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Formula added via a user form

    I am trying to set up a user form that will add new employees to a database. I have a couple of columns that will just have formulas, here is what I am working with:

    RowCount = historyWks.Range("A1").CurrentRegion.Rows.Count
    With historyWks.Range("A1")
    .Offset(RowCount, 0).Value = Me.txtEmployeeID.Value
    .Offset(RowCount, 1).Value = Me.txtFirstName.Value
    .Offset(RowCount, 2).Value = Me.txtMiddleName.Value
    .Offset(RowCount, 3).Value = Me.txtLastName.Value
    .Offset(RowCount, 4).Value = Me.txtUnionClass.Value
    .Offset(RowCount, 5).Value = DateValue(Me.txtDOH.Value)
    .Offset(RowCount, 6).Value = Me.cboJob.Value
    .Offset(RowCount, 7).Value = "=VLOOKUP(G2,Projects!$A$1:$F$88,2)"
    .Offset(RowCount, 13).Value = Format(Now, "mm/dd/yyyy hh:mm:ss am/pm")
    .Offset(RowCount, 14).Value = Application.UserName
    End With

    Is it possible to get it to update to the correct row, so that if the data is being entered on line 500 the formula will change to G500 instead of G2?
    Thanks!

  2. #2
    Forum Contributor
    Join Date
    01-02-2007
    Location
    Australia NSW
    MS-Off Ver
    2013
    Posts
    494

    Re: Formula added via a user form

    When doing this, i insert a new row, and have any formula copied from the below row with an example below:

    Sheets("names").Visible = True
    With Sheets("names")
    .Select
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    ' .Offset codes here
    ' End With
    Unload Me
    Sheets("MAIN").Activate

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] User Form to execute search and return all values to the user form for editing
    By allwrighty in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-11-2013, 10:40 PM
  2. [SOLVED] Excel vba user form- open directly to user form not worksheet
    By PANTECH in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 04-24-2013, 05:07 PM
  3. [SOLVED] Excel user form- If/Then statement outcome to show on user form
    By PANTECH in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-18-2013, 09:16 AM
  4. Print preview from user form opened from a user form
    By Brunstgnegg in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-13-2011, 05:12 AM
  5. Filling form fields via worksheet dropdowns, user update via form, change form color
    By Demosthenes&Locke in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2010, 08:58 AM

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