Look for target value with prefix to start functions

Vincent88

Active Member
Joined
Mar 5, 2021
Messages
268
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
I want the function to implement only
VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 
  Dim LastColumn As Long
 
  Select Case Sh.Name
    Case "Agents"
      Exit Sub
    Case Else
  End Select
 
  If Target.Column > 4 Or Target.CountLarge > 1 Then Exit Sub
  If Target.Row = 1 Then Exit Sub
  LastColumn = Sh.Range("A1").CurrentRegion.Columns.Count
 
  Application.EnableEvents = False

  If Cells(Target.Row, "A").Value <> "" And Cells(Target.Row, "B") <> "" Then Exit Sub
  If InStr(Cells(Target.Row, "A").Value, "REQ000000") > 0 And Cells(Target.Row, "B") <> "" Then
 
        Cells(Target.Row, "C") = ActiveSheet.Name
        Cells(Target.Row, "C").Font.Name = "Times New Roman"
        Cells(Target.Row, "C").Font.Size = 12
        Cells(Target.Row, "C").HorizontalAlignment = xlRight
        Cells(Target.Row, "D").ShrinkToFit = True
        Cells(Target.Row, "A").Font.Name = "Times New Roman"
        Cells(Target.Row, "A").Font.Size = 12
        Cells(Target.Row, "A").HorizontalAlignment = xlLeft
        Cells(Target.Row, "B").Font.Name = "Times New Roman"
        Cells(Target.Row, "B").Font.Size = 12
        Cells(Target.Row, "B").HorizontalAlignment = xlLeft
      
  Else
        Cells(Target.Row, "C").ClearContents
        Cells(Target.Row, "D").ShrinkToFit = False
  End If
 
   Application.EnableEvents = True

End Sub
when the target cell(s) has prefix with REQ000000. My code does not work. Please advise how to retify
 

Attachments

  • instr.png
    instr.png
    37.7 KB · Views: 1

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,943
Office Version
  1. 365
Platform
  1. Windows
I see an issue with these two lines:
VBA Code:
  If Cells(Target.Row, "A").Value <> "" And Cells(Target.Row, "B") <> "" Then Exit Sub
  If InStr(Cells(Target.Row, "A").Value, "REQ000000") > 0 And Cells(Target.Row, "B") <> "" Then
I don't think you can ever get a TRUE value for the second IF.

You first IF says that if both columns A and B are NOT blank, then exit the sub procedure.
You second IF says if "REQ000000" is found in column A, and column B is not blank, then go on to the following.
But that can never happen, because the first IF is already telling it to exit the sub if both columns A and B have something in them!
So it would be impossible to satisfy a condition that checks column A for "REQ000000" and column B not empty!
 

Vincent88

Active Member
Joined
Mar 5, 2021
Messages
268
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
Hi Joe4,
I tried this code but it also not works.

If Cells(Target.Row, "A") <> "" And InStr(Cells(Target.Row, "A").Value, "REQ000000") > 0 And Cells(Target.Row, "B") <> "" Then
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,943
Office Version
  1. 365
Platform
  1. Windows
Can you explain, in plain English, how you want/expect this to work?
Also, which sheets do you wish to apply this to?
 

Vincent88

Active Member
Joined
Mar 5, 2021
Messages
268
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Hi Joe4,
I attached my file for easy reference.
All functions should work in the dynamic range of all sheets except the sheet called "Agents".
There is a dynamic range (A to E) in each sheet. In column A of the dynamic range, whenever I input a 6-digit number, it will generate a prefix "REQ000000" and functions in other column of the same row will be activated.
The problem is if the last entry is in row 5. When I enter alphabets in A7 or below, a error will pop up, or if I enter some numbers, it will add the prefix and all other functions in column B, C , D and E will be activated too ( I do not want any functions to work outside the dynamic range ).
File
 

Attachments

  • A7 error.png
    A7 error.png
    55.7 KB · Views: 4

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,943
Office Version
  1. 365
Platform
  1. Windows
OK, you have a lot more going on in this sheet than you have mentioned.
If you hit the "Debug" button when you get that error pop-up, it highlights the offending line of code, which isn't even in the section of code that you posted.
Rich (BB code):
'Formate Column A
If Target.Column = 1 Then
    Dim s As String
    Dim arr As Variant

    s = Target.Value
    If s = "" Then
      Target.NumberFormat = "General"
    Else
      With CreateObject("vbscript.regexp")
        .Pattern = "[^0-9]"
        .Global = True
        .IgnoreCase = True
        arr = Split(Application.Trim(.Replace(s, " ")), " ")
      End With
      Target.Value = arr
      Target.Value = Target.Value * 1
      Target.NumberFormat = """REQ0000000""General"
    End If
  End If
That line of code is trying to multiply whatever you enter in column A by 1. However, if you enter in a text value like "ui", that will cause an error, as you cannot multiply alpha values (it makes no sense).
I am not sure why you would skip rows and enter values like "ui" in the column. It doesn't really seem like that is how it is supposed to work.

Did you create this code yourself?
It seems to me that maybe you are using code that someone else developed, and may not be quite sure how it is supposed to work, based on the structure of the table and the VBA code already there.
 

Vincent88

Active Member
Joined
Mar 5, 2021
Messages
268
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
Hi Joe4,
Other functions are working fine so am just pinpointed to the only error this moment. I used code from other.
 

Forum statistics

Threads
1,141,001
Messages
5,703,656
Members
421,309
Latest member
ray crad

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