+ Reply to Thread
Results 1 to 7 of 7

Need help simplifying vba code

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-01-2010
    Location
    Niles, Michigan
    MS-Off Ver
    Excel 2010
    Posts
    137

    Need help simplifying vba code

    Does anyone know how to simplify this code? It's doing the same thing except it's looking at different cell values. And I really don't feel like creating 8 different userforms that the command buttons will be assigned to these. There's got to be a better way of having a command button look at the ranges.

    Private Sub CommandButton1_Click()
        Application.ScreenUpdating = False
        Dim Found As Range
        Dim ProductCode As Range
            Set ProductCode = Sheets("Moulder 1").Range("B4")
            Set Found = Sheets("Product Code List").Range("A2:A300").Find(What:=ProductCode.value, _
                                                           LookIn:=xlValues, _
                                                           LookAt:=xlWhole, _
                                                           SearchOrder:=xlByRows, _
                                                           SearchDirection:=xlNext, _
                                                           MatchCase:=False)
            Found.Offset(0, 2).value = Me.TextPackSize.value
        Application.ScreenUpdating = True
    End Sub
    Private Sub CommandButton2_Click()
        Application.ScreenUpdating = False
        Dim Found As Range
        Dim ProductCode As Range
            Set ProductCode = Sheets("Moulder 1").Range("B19")
            Set Found = Sheets("Product Code List").Range("A2:A300").Find(What:=ProductCode.value, _
                                                           LookIn:=xlValues, _
                                                           LookAt:=xlWhole, _
                                                           SearchOrder:=xlByRows, _
                                                           SearchDirection:=xlNext, _
                                                           MatchCase:=False)
            Found.Offset(0, 2).value = Me.TextPackSize.value
        Application.ScreenUpdating = True
    End Sub
    Private Sub CommandButton3_Click()
        Application.ScreenUpdating = False
        Dim Found As Range
        Dim ProductCode As Range
            Set ProductCode = Sheets("Moulder 1").Range("B34")
            Set Found = Sheets("Product Code List").Range("A2:A300").Find(What:=ProductCode.value, _
                                                           LookIn:=xlValues, _
                                                           LookAt:=xlWhole, _
                                                           SearchOrder:=xlByRows, _
                                                           SearchDirection:=xlNext, _
                                                           MatchCase:=False)
            Found.Offset(0, 2).value = Me.TextPackSize.value
        Application.ScreenUpdating = True
    End Sub
    Private Sub CommandButton4_Click()
        Application.ScreenUpdating = False
        Dim Found As Range
        Dim ProductCode As Range
            Set ProductCode = Sheets("Moulder 1").Range("B49")
            Set Found = Sheets("Product Code List").Range("A2:A300").Find(What:=ProductCode.value, _
                                                           LookIn:=xlValues, _
                                                           LookAt:=xlWhole, _
                                                           SearchOrder:=xlByRows, _
                                                           SearchDirection:=xlNext, _
                                                           MatchCase:=False)
            Found.Offset(0, 2).value = Me.TextPackSize.value
        Application.ScreenUpdating = True
    End Sub

  2. #2
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Need help simplifying vba code

    Private Sub CommandButton1_Click()
        Application.ScreenUpdating = False
        Dim Found As Range
        Dim ProductCodes()
        Dim ProductCode
        ProductCodes = Array("B4", "B19", "B34", "B49")
            For i = 0 To UBound(ProductCodes)
            ProductCode = Sheets("Moulder 1").Range(ProductCodes(i)).Value
            Set Found = Sheets("Product Code List").Range("A2:A300").Find(What:=ProductCode, _
                                                           LookIn:=xlValues, _
                                                           LookAt:=xlWhole, _
                                                           SearchOrder:=xlByRows, _
                                                           SearchDirection:=xlNext, _
                                                           MatchCase:=False)
            If Not Found Is Nothing Then Found.Offset(0, 2).Value = Me.TextPackSize.Value
            Next
        Application.ScreenUpdating = True
    End Sub
    I'm interested in starting a career working with VBA, if anyone knows of any opportunities!

  3. #3
    Forum Contributor
    Join Date
    07-01-2010
    Location
    Niles, Michigan
    MS-Off Ver
    Excel 2010
    Posts
    137

    Re: Need help simplifying vba code

    Genius. Thank you very much!

  4. #4
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Need help simplifying vba code

    Glad to help. If you are satisfied with the solution, please mark this thread as SOLVED, and any reputation points, (this * in the lower left of the posts) is appreciated.

  5. #5
    Forum Contributor
    Join Date
    07-01-2010
    Location
    Niles, Michigan
    MS-Off Ver
    Excel 2010
    Posts
    137

    Re: Need help simplifying vba code

    Maybe I should bring this up in another topic but I have to ask. I am trying to figure out how to show a userform if cell B4>0 and if cell I4=0. Then I would just have it as Userform10.show. I know it'll need to be in a worksheet change event but I can't figure out how to get it to show when those conditions are met.

  6. #6
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Need help simplifying vba code

    Yeah, per forum rules this needs to be a new post. Start a new thread with an appropriate title and I'll post the code.

  7. #7
    Forum Contributor
    Join Date
    07-01-2010
    Location
    Niles, Michigan
    MS-Off Ver
    Excel 2010
    Posts
    137

    Re: Need help simplifying vba code

    Will do. But back to this code. What you have posted works except for the following. If I enter anything in the text box and click ok it does what it's supposed to do. Meaning it looks up the value in cell B4 and paste it to the other sheet. Now if I go down and enter a new value in the text box for cell B19 or any of the other cells in the array, it overwrites all of the values I typed in previously. Can you help me out with this? Not sure on how I should go about this. Meaning could I have it in here where if there's a value there then it won't do anything.

    Example.
    B4 has a value of 1009. I4 (which is a vlookup of cell B4) has no value in it at all. So I bring up the userform and say I type in 100 and hit ok it paste that value where it's supposed to and now cell I4 is showing 100
    B19 has a value of 1012. I19 (same vlookup formula looking up the value in cell B19) has no value in it. Again I bring up the userform and I type in 200 this time. Now I4 and I19 are showing a value of 200.
    And on down the line like this.

+ 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] New to VBA need help with simplifying code.
    By ntsun in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-24-2014, 09:11 AM
  2. [SOLVED] Help in simplifying code
    By RaquelAR in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-17-2013, 02:04 PM
  3. Simplifying code
    By cg0789 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-15-2012, 05:28 PM
  4. Need help simplifying a code Please
    By Lostinexcel2002 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-10-2008, 07:54 PM
  5. Simplifying the Code
    By marino72 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-28-2008, 06:40 PM
  6. Simplifying the code
    By walid66 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-20-2008, 07:53 PM
  7. Simplifying code
    By T De Villiers in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-23-2007, 06:50 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