Trying to run two events in worksheet that happen in the same column .

demorial

New Member
Joined
Feb 19, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Good day I am trying to run 2 change events in a excel worksheet that use the same column can anyone please help me with what is wrong with my procedure?

the code is :

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)


Dim Oldvalue As String
Dim Newvalue As String

Dim nommer As Integer
Dim finder As Range


On Error GoTo Exitsub
If Target.Column = "3" Then   <------- here is macro 2
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
    Else: If Target.Value = "" Then GoTo Exitsub Else
        Application.EnableEvents = False
        Newvalue = Target.Value
        Application.Undo
        Oldvalue = Target.Value
        If Oldvalue = "" Then
            Target.Value = Newvalue
        Else
            Target.Value = Oldvalue & "+ " & Newvalue
        End If
    End If
End If

If Not Intersect(Target, Range("J9")) Is Nothing Then
        Select Case Range("J9")
            Case "A": toets_my_ws
        End Select
    End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True

If Target.Column = "3" Then <------- here is macro 2

    nommer = ActiveCell.Value
   

    Else: If Target.Value = "" Then GoTo Exitsub Else
    Set finder = Range("B9:B40").Find(what:=ActiveCell.Value, LookIn:=xlValues, lookat:=xlWhole)



    ActiveCell.Offset(0, 3).Value = finder.Offset(0, 4).Value
    ActiveCell.Offset(0, 5).Value = finder.Offset(0, 6).Value
End If



End Sub
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,971
Try
VBA Code:
If Not Application.Intersect(Target. Range("C1:C100")) Is Nothing Then
    ' do one thing
End If
If Not Application.Intersect(Target. Range("C101:C200")) Is Nothing Then
    ' do another thing
End If

The two blocks rather than one If ElseIf End If block is incase Target contains cells from both sections.
 

demorial

New Member
Joined
Feb 19, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
If Not Application.Intersect(Target. Range("C1:C100")) Is Nothing Then
    ' do one thing
End If
If Not Application.Intersect(Target. Range("C101:C200")) Is Nothing Then
    ' do another thing
End If

The two blocks rather than one If ElseIf End If block is incase Target contains cells from both sections.
Good day mikerickson

Will this work if I am trying to make both of the macros work in the same range since I want the drop down it that is created there to be able to check if there is a value in the column next to it get that rows values and also still be able to run the first macro. see picture attached since I dont think I am explaining properly what I want.


So in the picture in column A is set number. Column B has the dropdown where the first macro was implemented which enables it to be able to get values from column A and also be able to add a value to show 2+3 , I need it then to be able to get the values of stream C and actually add them in collumn C
 

Attachments

  • value_save.png
    value_save.png
    9.7 KB · Views: 2

Watch MrExcel Video

Forum statistics

Threads
1,127,843
Messages
5,627,215
Members
416,230
Latest member
jdaitchman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top