+ Reply to Thread
Results 1 to 9 of 9

Not getting correct output problem

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Not getting correct output problem

    Hi Holger,
    Thank you for guidance. I have done the first correction successfully and getting the output of column 'g' in column 'g' but all other 2 outputs are also displaying in column 'g' which should be in column 'ac' and 'ad' respectively.

    I didn't understand the second suggestion of break point using f9. Please explain it little more for me for better understanding. On which line the break point should be place?
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    With Target
      If .Count = 1 Then
        Select Case .Column
            
                                                         Case 28 'Column AB
                                                         On Error Resume Next
            Range("b" & .Row + 1).Select
            On Error GoTo 0
            
            Case 6 'Column F
                    On Error Resume Next
            Target.Offset(, 2).Select         'This will select the next column to the right from the changed cell
            On Error GoTo 0
            
            Case 13 'Column M
                    On Error Resume Next
            Target.Offset(, 2).Select         'This will select the next column to the right from the changed cell
            On Error GoTo 0
                    
                    
                    End Select
                    End If
                    End With
    
    'CONCATENATE column g
    
    If Target.Row < 7 Then Exit Sub
    Dim vTemp As Variant
    Dim var As Variant
    Dim lngArr As Long
    
    var = Array("C", "d", "e", "f")
    
    Application.EnableEvents = False
    
    For lngArr = LBound(var) To UBound(var)
      vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value & " "
    Next lngArr
    vTemp = Replace(Trim(vTemp), "-", " ")
    Cells(Target.Row, 7).Value = vTemp
    
    Application.EnableEvents = True
    
    
    'CONCATENATE column ac
    
    var = Array("z", "aa", "ab")
    
    Application.EnableEvents = False
    
    For lngArr = LBound(var) To UBound(var)
      vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value & " "
    Next lngArr
    vTemp = Replace(Trim(vTemp), "-", " ")
    Cells(Target.Row, 7).Value = vTemp
    
    Application.EnableEvents = True
    
    'CONCATENATE column ad
    
    var = Array("aa", "ab")
    
    Application.EnableEvents = False
    
    For lngArr = LBound(var) To UBound(var)
      vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value & " "
    Next lngArr
    vTemp = Replace(Trim(vTemp), "-", " ")
    Cells(Target.Row, 7).Value = vTemp
    
    Application.EnableEvents = True
    
    End Sub
    Thank you.

    Have a nice day!

    Regards,

    Mukesh

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: Not getting correct output problem

    Hi, Mukesh

    there is a lot of unnecessary movement in the code, and I pretty much doubt that you know what you are putting down as comment should be put down in the code as well:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim vTemp As Variant
    Dim var As Variant
    Dim lngArr As Long
    
    If Target.Row < 7 Then Exit Sub
    
    Application.EnableEvents = False
    
    'CONCATENATE column g
    var = Array("C", "d", "e", "f")
    
    For lngArr = LBound(var) To UBound(var)
      vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value & " "
    Next lngArr
    vTemp = Replace(Trim(vTemp), "-", " ")
    Cells(Target.Row, "G").Value = vTemp
    
    'CONCATENATE column ac
    var = Array("z", "aa", "ab")
    
    For lngArr = LBound(var) To UBound(var)
      vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value & " "
    Next lngArr
    vTemp = Replace(Trim(vTemp), "-", " ")
    Cells(Target.Row, "ac").Value = vTemp
    
    'CONCATENATE column ad
    var = Array("aa", "ab")
    
    For lngArr = LBound(var) To UBound(var)
      vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value & " "
    Next lngArr
    vTemp = Replace(Trim(vTemp), "-", " ")
    Cells(Target.Row, "ad").Value = vTemp
    
    Application.EnableEvents = True
    
    End Sub
    I didn't understand the second suggestion of break point using f9. Please explain it little more for me for better understanding. On which line the break point should be place?
    Put the cursor into the first executable line of code and press F9, from my sample this would be the line
    If Target.Row < 7 Then Exit Sub
    If the code is fired it will hold on that line and you can step through the rest of the code by pressing F8.

    Ciao,
    Holger
    Last edited by HaHoBe; 02-19-2014 at 07:02 AM. Reason: Replaced the original posted F9 (false) with F8 (correct)
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Error in sum formula not correct output
    By mukeshbaviskar in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-31-2014, 11:40 AM
  2. [SOLVED] Not getting correct output value( Label & (i-6) & .Top)
    By annpricks in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-31-2012, 03:44 AM
  3. Find and output correct path
    By 9437excel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-07-2011, 02:20 PM
  4. problem with CSV output
    By duxis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-21-2008, 06:16 AM
  5. Month Function Not Displaying Correct Output?
    By NewExcelUser in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-29-2007, 03:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1