+ Reply to Thread
Results 1 to 7 of 7

Adapt VBA code to clear the related cells instead of update

  1. #1
    Forum Contributor
    Join Date
    07-02-2013
    Location
    abbots langley
    MS-Off Ver
    Excel 2010
    Posts
    296

    Adapt VBA code to clear the related cells instead of update

    Hi i have this code that saves changes to a worksheet i want to adapt it to clear the cells. i assume i would need to replace value?


    Private Sub btnSaveChanges_Click()

    Dim l As Long
    Dim lRow As Long
    Dim rngTarget As Range

    lRow = ActiveWorkbook.Sheets("Active Equip").Range("A56635").End(xlUp).Row

    For l = 4 To lRow
    Set rngTarget = Sheets("Active Equip").Range("A" & l)
    If UserForm2.tbxHospital.Value = rngTarget.Value Then
    rngTarget.Offset(, 1).Value = UserForm2.cboTM.Value
    rngTarget.Offset(, 2).Value = UserForm2.cboMHCP.Value
    rngTarget.Offset(, 4).Value = UserForm2.cboPSQOutcome.Value
    rngTarget.Offset(, 5).Value = UserForm2.tbxPSQScore.Value
    rngTarget.Offset(, 6).Value = UserForm2.cboProgramme.Value
    rngTarget.Offset(, 7).Value = UserForm2.cboReference.Value
    rngTarget.Offset(, 8).Value = UserForm2.tbxInstallBase.Value
    rngTarget.Offset(, 9).Value = UserForm2.tbxInstallBaseMDT.Value
    rngTarget.Offset(, 11).Value = UserForm2.tbxNPNP.Value
    rngTarget.Offset(, 12).Value = UserForm2.tbxIndustryInstallBase
    rngTarget.Offset(, 15).Value = UserForm2.tbxTotalPumps.Value
    If Not UserForm2.TextBoxMeeting.Value = "" Then
    rngTarget.Offset(, 16).Value = DateValue(UserForm2.TextBoxMeeting.Value)
    End If
    rngTarget.Offset(, 17).Value = strNames()
    If Not UserForm2.tbxCycle1.Value = "" Then
    rngTarget.Offset(, 22).Value = DateValue(UserForm2.tbxCycle1.Value)
    End If
    If Not UserForm2.tbxCycle2.Value = "" Then
    rngTarget.Offset(, 23).Value = DateValue(UserForm2.tbxCycle2.Value)
    End If
    If Not UserForm2.tbxCycle3.Value = "" Then
    rngTarget.Offset(, 24).Value = DateValue(UserForm2.tbxCycle3.Value)
    End If
    If Not UserForm2.tbxPlannedExit.Value = "" Then
    rngTarget.Offset(, 25).Value = DateValue(UserForm2.tbxPlannedExit.Value)
    End If

    rngTarget.Offset(, 27).Value = UserForm2.cboScheduleExit.Value
    rngTarget.Offset(, 29).Value = UserForm2.tbxLeadContact.Value
    rngTarget.Offset(, 30).Value = UserForm2.tbxLeadContactNo.Value
    rngTarget.Offset(, 31).Value = UserForm2.tbxSecondaryContact.Value
    rngTarget.Offset(, 32).Value = UserForm2.tbxSecondaryContactNo.Value
    rngTarget.Offset(, 33).Value = UserForm2.tbxComments.Value
    End If

    Next l
    Unload Me


    End Sub
    Be grateful if someone could advise.

  2. #2
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Re: Adapt VBA code to clear the related cells instead of update

    Hi,

    You could use = "" or .clear instead of .value

  3. #3
    Forum Contributor
    Join Date
    07-02-2013
    Location
    abbots langley
    MS-Off Ver
    Excel 2010
    Posts
    296

    Re: Adapt VBA code to clear the related cells instead of update

    Thanks TC

    I have tried clear but receive an expected function or variable error message?

    Please Login or Register  to view this content.
    ANy ideas?

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Adapt VBA code to clear the related cells instead of update

    I think you need:
    Please Login or Register  to view this content.
    instead of
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-17-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Adapt VBA code to clear the related cells instead of update

    What TC1980 meant was using the following.
    Please Login or Register  to view this content.
    instead of

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    07-02-2013
    Location
    abbots langley
    MS-Off Ver
    Excel 2010
    Posts
    296

    Re: Adapt VBA code to clear the related cells instead of update

    that works thanks fro replying and helping out.

  7. #7
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Re: Adapt VBA code to clear the related cells instead of update

    Sorry Michelle, didn't have access to my usual PC and couldn't remember my details to log in! Glad you're all sorted though.

+ 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] Clear various cells in worksheet when changing related cells
    By Corneli in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-24-2012, 06:36 AM
  2. Clear Contents on Update when other code exists in the Worksheet_Change thingy
    By JRS_ in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-09-2011, 12:11 PM
  3. Adapt code to delete row
    By Seraph84 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-20-2011, 07:39 AM
  4. What is the code to clear all cells below a sepecified row?
    By skysurfer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-07-2010, 09:12 PM
  5. [SOLVED] Please help me to adapt this code.
    By Rob Hargreaves in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-02-2005, 05:06 PM

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