none
Required code instead of package Name RRS feed

  • Question

  • Dear All

    solve the query kindly, i have two column E and F, in column F i have packages names like Zahi250, WP500, siscson some others and in column E i have their codes name like 25458,32654,25688 so on..

    i want in column J when i write package name should show code instead of package name(in same cell), means if i write zahi250 in any cell of column J it represents code which is appearing in front of package name in column E..!!

    Nabeel Gondal

    Wednesday, February 8, 2017 6:23 AM

Answers

  • Hi Nabeel Gondal,

    you had mentioned that,"i should give you real scenario, just used sheet 2 column A & B for code and packages and sheet 1 column R as result in code".

    so what I understand from that,

    you have 2 Sheets (Sheet1, Sheet2).

    in Sheet2 there are 2 columns. Column A for Code and Column B for Package Name.

    Sheet1 have Column R where you will enter package name and you want the code of that.

    so please refer code below.

    Sheet2 :

    Sheet1(Output):

    code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sht, sht2 As Worksheet
    Dim LastRow As Long
    Dim val As String
    Dim iComp As Integer
    Set sht = ThisWorkbook.Worksheets("Sheet1")
    Set sht2 = ThisWorkbook.Worksheets("Sheet2")
    LastRow = sht.Cells(sht.Rows.Count, "R").End(xlUp).Row
        Dim KeyCells As Range
        Set KeyCells = sht.Range("R1:R" & LastRow)
        
        If Not Application.Intersect(KeyCells, sht.Range(Target.Address)) _
               Is Nothing Then
    
           val = Target.Value
           Dim i As Long
           
            For i = 1 To sht2.Cells(sht2.Rows.Count, "B").End(xlUp).Row
            iComp = StrComp(sht2.Cells(i, 2).Value, val, vbTextCompare)
            If iComp = 0 Then
            Target.Value = sht2.Cells(i, 2).Offset(0, -1).Value
            
            End If
            
            Next i
        End If
    End Sub
    

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 10, 2017 1:03 AM
    Moderator
  • Hi Nabeel Gondal,

    Updated code to generate code for columns individually.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sht, sht2 As Worksheet
    Dim LastRow As Long
    Dim val As String
    Dim iComp As Integer
    Set sht = ThisWorkbook.Worksheets("Sheet1")
    Set sht2 = ThisWorkbook.Worksheets("Sheet2")
    
       
       
        If Target.Column = 18 Or Target.Column = 19 Or Target.Column = 20 Or Target.Column = 21 Or Target.Column = 22 Then
      
        val = Target.Value
           Dim i As Long
           
            For i = 1 To sht2.Cells(sht2.Rows.Count, "B").End(xlUp).Row
            iComp = StrComp(sht2.Cells(i, 2).Value, val, vbTextCompare)
            If iComp = 0 Then
            Target.Value = sht2.Cells(i, 2).Offset(0, -1).Value
            
            End If
            
            Next i
        End If
        
    End Sub

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 16, 2017 6:19 AM
    Moderator

All replies

  • Hi Nabeel Gondal,

    Refer the code below.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sht As Worksheet
    Dim LastRow As Long
    Dim val As String
    Set sht = ThisWorkbook.Worksheets("Sheet1")
    LastRow = sht.Cells(sht.Rows.Count, "J").End(xlUp).Row
        Dim KeyCells As Range
        Set KeyCells = Range("J1:J" & LastRow)
        
        If Not Application.Intersect(KeyCells, Range(Target.Address)) _
               Is Nothing Then
    
           val = Target.Value
           Dim i As Long
           
            For i = 1 To sht.Cells(sht.Rows.Count, "F").End(xlUp).Row
            If Cells(i, 6).Value = val Then
            Target.Value = Cells(i, 6).Offset(0, -1).Value
            
            End If
            
            Next i
        End If
    End Sub
    

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 9, 2017 1:15 AM
    Moderator
  • Hi Deepak

    This is good, But there is little issue that it should work in upper and lower case in both condition means if i write zahi250 or ZAHI250 in both case should be show code,pl

    & also little guide that if i will change the columns, where i will make changes because i can see J and F in code but did not E..

    Regards

    Nabeel Gondal


    Thursday, February 9, 2017 4:37 AM
  • Hi Nabeel Gondal,

    below is updated code that can match in any case.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sht As Worksheet
    Dim LastRow As Long
    Dim val As String
    Dim iComp As Integer
    Set sht = ThisWorkbook.Worksheets("Sheet1")
    LastRow = sht.Cells(sht.Rows.Count, "J").End(xlUp).Row
        Dim KeyCells As Range
        Set KeyCells = Range("J1:J" & LastRow)
        
        If Not Application.Intersect(KeyCells, Range(Target.Address)) _
               Is Nothing Then
    
           val = Target.Value
           Dim i As Long
           
            For i = 1 To sht.Cells(sht.Rows.Count, "F").End(xlUp).Row
            iComp = StrComp(Cells(i, 6).Value, val, vbTextCompare)
            If iComp = 0 Then
            Target.Value = Cells(i, 6).Offset(0, -1).Value
            
            End If
            
            Next i
        End If
    End Sub
    
    

    you had mentioned that," if i will change the columns, where i will make changes because i can see J and F in code but did not E."

    it depends that which column you want to change from these 3 columns.

    you can see the code for column J and F. for column E I used Offset function in code.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, February 9, 2017 6:45 AM
    Moderator
  • Hi Deepak

    now this is great and working as per our need, let me try for chnage

    Nabeel Gondal 



    Thursday, February 9, 2017 7:12 AM
  • Dear Deepak

    l think, this is little difficult for me in amned offset function,i should give you real scenario, just used sheet 2 column A & B for code and packages and sheet 1 column R as result in code,thanks

    Regards

    Nabeel Gondal 

    Thursday, February 9, 2017 7:38 AM
  • Hi Nabeel Gondal,

    you had mentioned that,"i should give you real scenario, just used sheet 2 column A & B for code and packages and sheet 1 column R as result in code".

    so what I understand from that,

    you have 2 Sheets (Sheet1, Sheet2).

    in Sheet2 there are 2 columns. Column A for Code and Column B for Package Name.

    Sheet1 have Column R where you will enter package name and you want the code of that.

    so please refer code below.

    Sheet2 :

    Sheet1(Output):

    code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sht, sht2 As Worksheet
    Dim LastRow As Long
    Dim val As String
    Dim iComp As Integer
    Set sht = ThisWorkbook.Worksheets("Sheet1")
    Set sht2 = ThisWorkbook.Worksheets("Sheet2")
    LastRow = sht.Cells(sht.Rows.Count, "R").End(xlUp).Row
        Dim KeyCells As Range
        Set KeyCells = sht.Range("R1:R" & LastRow)
        
        If Not Application.Intersect(KeyCells, sht.Range(Target.Address)) _
               Is Nothing Then
    
           val = Target.Value
           Dim i As Long
           
            For i = 1 To sht2.Cells(sht2.Rows.Count, "B").End(xlUp).Row
            iComp = StrComp(sht2.Cells(i, 2).Value, val, vbTextCompare)
            If iComp = 0 Then
            Target.Value = sht2.Cells(i, 2).Offset(0, -1).Value
            
            End If
            
            Next i
        End If
    End Sub
    

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 10, 2017 1:03 AM
    Moderator
  • Dear Deepak

    excellent, i really appreciated your understanding and skill, great work

    Thank for help

    Regards

    Nabeel Gondal 

    Friday, February 10, 2017 4:56 AM
  • Dear Deepak

    i am vary sorry to you for engaging again, need your some expertise more, hope you will not mind & will extend help for me..!!

    your given solution is working perfectly for me & saving my lot of time,

    i need little amend that i need more result like column R, in columns S,T U and V...means i am writing package name and appearing the code in column R, same result wants in columns S,T U and V..Plz

    Regards,

    Nabeel Gondal

    Tuesday, February 14, 2017 3:35 PM
  • Hi Nabeel Gondal,

    from your last post what I understand that , you will enter the package name in Column R and code will be display in column R, S,T,U,V.

    code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sht, sht2 As Worksheet
    Dim LastRow As Long
    Dim val As String
    Dim iComp As Integer
    Set sht = ThisWorkbook.Worksheets("Sheet1")
    Set sht2 = ThisWorkbook.Worksheets("Sheet2")
    LastRow = sht.Cells(sht.Rows.Count, "R").End(xlUp).Row
        Dim KeyCells As Range
        Set KeyCells = sht.Range("R1:R" & LastRow)
        
        If Not Application.Intersect(KeyCells, sht.Range(Target.Address)) _
               Is Nothing Then
    
           val = Target.Value
           Dim i As Long
           
            For i = 1 To sht2.Cells(sht2.Rows.Count, "B").End(xlUp).Row
            iComp = StrComp(sht2.Cells(i, 2).Value, val, vbTextCompare)
            If iComp = 0 Then
            Target.Value = sht2.Cells(i, 2).Offset(0, -1).Value
            Target.Offset(0, 1).Value = sht2.Cells(i, 2).Offset(0, -1).Value
            Target.Offset(0, 2).Value = sht2.Cells(i, 2).Offset(0, -1).Value
            Target.Offset(0, 3).Value = sht2.Cells(i, 2).Offset(0, -1).Value
            Target.Offset(0, 4).Value = sht2.Cells(i, 2).Offset(0, -1).Value
            End If
            
            Next i
        End If
    End Sub

    Output:

    if I misunderstand something in your requirement then let me know about that, I will try to correct it.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 15, 2017 3:16 AM
    Moderator
  • Dear Deepak 

    there is some confusion that, each column should react an individuals like if i write package name in column R convert into code than i write package name in column S it convert to code similarly if i write package name in column T it convert to code also other others..!!it should not be linked with column R

    looking forward for your kind help

    Regards

    Nabeel Gondal 


    Wednesday, February 15, 2017 9:54 AM
  • Hi Nabeel Gondal,

    Updated code to generate code for columns individually.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sht, sht2 As Worksheet
    Dim LastRow As Long
    Dim val As String
    Dim iComp As Integer
    Set sht = ThisWorkbook.Worksheets("Sheet1")
    Set sht2 = ThisWorkbook.Worksheets("Sheet2")
    
       
       
        If Target.Column = 18 Or Target.Column = 19 Or Target.Column = 20 Or Target.Column = 21 Or Target.Column = 22 Then
      
        val = Target.Value
           Dim i As Long
           
            For i = 1 To sht2.Cells(sht2.Rows.Count, "B").End(xlUp).Row
            iComp = StrComp(sht2.Cells(i, 2).Value, val, vbTextCompare)
            If iComp = 0 Then
            Target.Value = sht2.Cells(i, 2).Offset(0, -1).Value
            
            End If
            
            Next i
        End If
        
    End Sub

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 16, 2017 6:19 AM
    Moderator
  • dear deepak

    bundle of thank for help, this is good now, Best wishes for you...!!

    Regards 

    Nabeel Gondal

    Thursday, February 16, 2017 6:32 AM