How to add new value with Previous value

**sijostephen** · 06-08-2019, 04:50 AM

Can anyone help me on this , how to fix this . i want to Add new value with Previous Value

Capture.PNG

Private Sub Worksheet_Change(ByVal Target As Range)

Dim FixRange As Range

 On Error Resume Next
   If Not Application.Intersect(FixRange, Range(Target.Address)) Is Nothing Then
            If Target.Value = "S" Then
               Target.Offset(0, -1).Value = "Master." & Target.Offset(0, -1).Value
            End If
            
            If Target.Value = "D" Then
                Target.Offset(0, 1).Value = "MISS." & Target.Offset(0, -1).Value
            End If
            
    End If
  Target = UCase(Target)
    End Sub

**mjr veverka** · 06-08-2019, 05:18 AM

Try:

Option Explicit

Const FixRangeAddress As String = "B2:B4"

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range(FixRangeAddress)) Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
    If UCase(Target.Value) = "D" Then
        Target.Offset(0, 1).Value = "MISS." & Target.Offset(0, -1).Value
    ElseIf UCase(Target.Value) = "S" Then
        Target.Offset(0, -1).Value = "Master." & Target.Offset(0, -1).Value
    End If
    Target.Value = UCase(Target.Value)
    
    Application.EnableEvents = True
End Sub

**sijostephen** · 06-08-2019, 05:38 AM

Sorry Sir , i changed but no error and no changes here is the file attached for ur reference

**alansidman** · 06-08-2019, 08:33 AM

@sijostephen

Administrative Note:

You have been a member here long enough to know our forum rules.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

**sijostephen** · 06-08-2019, 01:35 PM

Thank you for ur information , i changed ..

**mjr veverka** · 06-08-2019, 05:58 PM

Originally Posted by sijostephen

... no error and no changes ...

For me this code works, please try again in this way:

Option Explicit

Const FixRangeAddress As String = "F3:F400"

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range(FixRangeAddress)) Is Nothing Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    If Trim(Target.Value) = "" Then Exit Sub
    
    Application.EnableEvents = False
    
    If Left(UCase(Target.Value), 1) = "D" Then
        Target.Offset(0, 1).Value = "MISS." & Target.Offset(0, -1).Value
        Target.Value = "D"
    ElseIf Left(UCase(Target.Value), 1) = "S" Then
        Target.Offset(0, -1).Value = "Master." & Target.Offset(0, -1).Value
        Target.Value = "S"
    End If
    
    Application.EnableEvents = True
End Sub

**sijostephen** · 06-08-2019, 11:34 PM

Thank you sir, is it possible to all target to uppercase automatically ?

**mjr veverka** · 06-09-2019, 06:43 PM

Try:

Option Explicit

Const FixRangeAddress As String = "F3:F400"

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range(FixRangeAddress)) Is Nothing Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    If Trim(Target.Value) = "" Then Exit Sub
    
    Application.EnableEvents = False
    
    If Left(UCase(Target.Value), 1) = "D" Then
        Target.Offset(0, 1).Value = "MISS." & UCase(Target.Offset(0, -1).Value)
        Target.Value = "D"
    ElseIf Left(UCase(Target.Value), 1) = "S" Then
        Target.Offset(0, -1).Value = "MASTER." & UCase(Target.Offset(0, -1).Value)
        Target.Value = "S"
    End If
    
    Application.EnableEvents = True
End Sub

How to add new value with Previous value

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to add new value with Previous value

Re: How to add new value with Previous value

Re: How to add new value with Previous value

Re: How to add new value with Previous value

Re: How to add new value with Previous value

Re: How to add new value with Previous value

Re: How to add new value with Previous value

Re: How to add new value with Previous value

Thread Information

Users Browsing this Thread

Similar Threads

Show difference from previous date without showing previous date in pivot table

Selection.Previous/ActiveCell.Previous Problem

[SOLVED] Find certain word, copy and paste cell in previous row if previous row is blank

[SOLVED] Find last previous non blank value and summarise previous 6 months

if the previous cell is blank then use the previous formula

Table - lines compared to previous entries and values in another (the previous sheet0

calculating previous week sunday and previous saturday

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions