Run Macro Based On Values In Column

  • I have a similar question as lbaca. I also like to run a different macro based on the value in a cell in a column.

    ie A. In this column can be different numbers and based on witch number it is i would like to run a specific macro.


    I know how to make a button and connect it to the macro to run. Only the macro i create isn't working.


    I have a Import and a Main sheet



    =================================

    In the "import" sheet i can see this:

    Private Sub Worksheet_Change(ByVal Target As Range)

    End Sub

    ------------------------------------------------------

    I put this code in my module:

    Code
    1. Sub Import()
    2. If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    3. If Target.Value = "0" Then Call MacroA
    4. If Target.Value = "1" Then Call MacroB
    5. If Target.Value = "2" Then Call MacroC
    6. If Target.Value = "3" Then Call MacroD
    7. If Target.Value = "4" Then Call MacroE
    8. End With
    9. End Sub

    When i run the macro, i receive a error: Missing object in this line; If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

    Also when i click this line it say's ; Target is empty


    What am i doing wrong?

  • Code Tags Added

    Your post does not comply with our Forum RULES. Use code tags around code.


    Posting code between

    Code

    tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.


    Highlight your code and click the # icon at the top of your post window.


    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

  • You cannot use Target like that. You do not give Target a value


    You have an end with without the opening with.


    You seem to be mixing your macro with Event code. If you want to run the code automatically then it needs to be in the worksheet event code


    The above is a WorkSheet event code and should be placed in the WorkSheet Module. If you aren't sure what I mean then read:


    Where to paste code

  • Hi Roy,


    thanks for your fast reply. I have read the information on your website. I tried both options.


    I have put the code (from the second block) in the worksheet event code.


    Now when i change the value in a cell in the "whole" import sheet the connected macro is activated.


    ------------------------------------------------------------------------------------------------------------------------------------

    After that i have put the code (from the first blok) in the worksheet Module.


    Now when i put a value (let say 2) in a cell in column A:A and then click on the button to run this macro (import); depending on the value in the active cell (2), the connected macro (2) is starting.


    Both option work perfectly. :) Unfortunately this is not actually what i would like to happen.


    My situation:

    I have a .txt file with a list of numbers. This list i want to copy into my "import" sheet and then based on the value's in the column A:A run the "import" macro witch let's the different macro's run based on their value's. i.e. if the value is 2 then macro2 should start to run. etc.


    The import macro should look at the value in every cell in column A:A and then activate the connected macro. Then go further to the next cell and activate the connected macro.


    For now i can copy and the numbers from the txt file into the import sheet. Later-on i guess i can import the list by a macro but...


    How can i do this?

  • On the import sheet i placed a button called import. This one should trigger the other macro's to do there work.

    At the moment These results show on the import sheet.


    Column A shows witch player has made a goal or won a game

    Now when you select a "active" cel in A:A then click on import a result is add.

    These results should go to the Main sheet instead of here.


    numbers.txt is a list with numbers from the player who had made a goal or won a game as example.


  • i found this piece of code to import de import.txt file i create a new macro and placed a button to start it. This works fine.

  • Thanks Roy.


    The import part is working. The only thing what still isn't working is that the macro is reading the numbers in column A:A and based on every value is a cell starts the connected macro by itself. Now i need to click every number in the next cell and then click on it to run the attached macro.


    How can i make the macro to do this by itself?


    I came up with ActiveCell.Offset(1, 0).Select 'Select the next cell (the cell below: offset(1,0) . This will tel the macro to go to the next cell under the active cell.


    However it doesn't seem to do it.



  • This should run until the activecell is empty


  • I think you probably need to specify the sheet like this


  • If that is what you need then you can have one macro instead of five.


  • Pleased to help


    The code can actuall;y be shortened



  • Add your update to the sheet. Also updated the import for the txt file with the results/numbers. You now can pick a txt file from your pc and it gets imported to the import sheet.


    Normally this line it ends with Row + 1. Wich means it import the text/numbers to the next empty row. When you start with a empty row it hops 1 row over. In this Players Results it's not useful. So adapt if you like for your own import needs.


    Code
    1. Row = Range("A" & Rows.Count).End(xlUp).Row + 0



    Thanks again to Roy :)