none
Help making an error message appear in excel with a macro RRS feed

  • Question

  • I am looking to make an error massage or just a message appear after I run my macro that checks the first row in a worksheet for set values in each cell. The values in the cells are different for each cell. Right now I have the cells being checked by do a find and replace since the data I am working with can come in different cases, while I need it in a set case. The order for the cells can vary so it would need to be able to handle the order not being the same each time, if it is easier to do with the headers in a set order I can work with that as well. What I would want the message to say would be to tell what values are missing or are spelled wrong in the first row. I would like it to show all of them at once or even paste the missing values in another worksheet in the same workbook.
    Thursday, September 29, 2016 7:03 PM

Answers

  • The code below will find the values in the array irrespective of the order they are in the worksheet and irrespective of the case.

    If the case matches both the spelling and case of the Array value then the interior color of the cell is Green to indicate case matched.

    If the spelling matches and case does not match then the value from the array is copied to the cell to match the case and the cell is colored Yellow to indicate it has been changed.

    When finished a MsgBox is displayed listing any values in the array not found plus a further message re the colors legend.

    The test worksheet was like the following before processing

    After processing was like the following

    Sub MatchToArray()
    
        Dim rngToTest As Range
        Dim rngToFind As Range
        Dim arrValues(1 To 8)   'Dimension a one based array with 8 elements
        Dim i As Long
        Dim strNotFound As String
        
        arrValues(1) = "Column1"
        arrValues(2) = "Column2"
        arrValues(3) = "Column3"
        arrValues(4) = "Column4"
        arrValues(5) = "Column5"
        arrValues(6) = "Column6"
        arrValues(7) = "Column7"
        arrValues(8) = "Column8"
        
        
        With Worksheets("Sheet1")   'Edit "Sheet1" to your worksheet name
            Set rngToTest = .Range(.Cells(1, 1), .Cells(1, .Columns.Count).End(xlToLeft))
        End With
        
        For i = LBound(arrValues) To UBound(arrValues)
            With rngToTest
                Set rngToFind = .Find(What:=arrValues(i), _
                                LookIn:=xlFormulas, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                
                If Not rngToFind Is Nothing Then
                    If rngToFind.Value = arrValues(i) Then
                        rngToFind.Interior.Color = vbGreen 'Case matches
                    Else
                        rngToFind.Interior.Color = vbYellow 'Case did not previously match
                        rngToFind.Value = arrValues(i)      'Change to match case in array
                    End If
                Else
                    strNotFound = strNotFound & arrValues(i) & vbCrLf
                End If
            End With
        Next i
        
        If strNotFound <> "" Then
            MsgBox "Following values not found. " & vbCrLf & _
                    strNotFound & vbCrLf & _
                    "Green matched case." & vbCrLf & _
                    "Yellow did not previously match case."
        Else
            MsgBox "All values found."
            
        End If
    End Sub
    

    Regards, OssieMac

    Tuesday, October 4, 2016 1:54 AM

All replies

  • Cannot understand the question. How do you check the data? What do you compare it to? What do you mean by "come in different cases" and "I need it in a set case" (eg. what do you mean by case? is it upper and lower case or what?

    Might be clearer if you upload a copy of the workbook to one drive with step by step instructions of what you want to occur.

    Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

    1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
    2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
    3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
    4. Go to this link.  https://onedrive.live.com
    5. Use the same login Id and Password that you use for this forum.
    6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
    7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    8. Right click the file on OneDrive and select Share.
    9. Select "Get a Link" from the popup menu.
    10. Click Copy the link and Paste into your reply on this forum.

    Regards, OssieMac

    Friday, September 30, 2016 5:58 AM
  • Hi Jarrett22,

    your overall description is little confusing.

    so I try to break the description in several parts that can achieve individually.

    you had mentioned that,"you want to display message after running macro"

    use the example below to display message.

    MsgBox "This is Demo Message"

    you had mentioned  that,"you want to check the values in first row of excel sheet"

    for that you need to loop through each value in the row.

    use the example mentioned below to loop through all values and check it.

    For Each c In ActiveSheet.Range("A1:Z1").Cells
        If c.Value = "Demo" Then
        Msgbox("Value matched")
        else
        Msgbox("Value does not match.")
        End If
    Next

    you had mentioned that ,"value for the each cell are different"

    for that you need to first use loop and store all the values of cell in the array and then use the array in the loop to compare it with other values.

    below is the example of an array.

    Dim demoarr(1 To 5) As String
    
    demoarr(1) = "value1"
    demoarr(2) = "value2"
    demoarr(3) = "value3"
    demoarr(4) = "value4"
    demoarr(5) = "value5"
    
    

    you had mentioned that ,"data can come in different cases and you need to set case and after that you need to deal with order of cells"

    here I have confusion that what is case by your point of view. how it will work here and what output it produce.

    the data you want to match with first row are also come from this cases.

    so here we need some clarification regarding case and order of cells.

    please try to provide the above mentioned information so that we can try to provide you further suggestions.

    Regards

    Deepak 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 3, 2016 6:16 AM
    Moderator
  • Hi Deepak,

    Form what I can tell you are on the right track for what I am looking for and the case is just upper case or lower case.

    As for the order I mean that when I receive the file the order will not always be the same so what is in cell A1 will not always be the same or close to the same in each file. An example would be in file1 cell A1 could be name and in cell D1 could be city, while in file2 cell A1 could be state and cell D1 could be name. So I would need it to look and just tell me if it finds the value in any cell in row 1.

    For when it finds a match can I make it change the color of the cell if it is an exact match including case for the values in the array. If it is not an exact match can I have it change the case so that it matches the value in the array, this would only be for ones where the only thing that didn't match was the case. ie: it being lower case when it needs to be upper case

    Thank you,

    Jarrett

    Monday, October 3, 2016 8:09 PM
  • Hi Jarrett22,

    in the original post you had mentioned that ,"the data I am working with can come in different cases, while I need it in a set case."

    by default it will take care of the case. it means that if your value comes in uppercase but in the cell the value is in the lowercase then the value will not get matched.

    see the example mentioned below.

    Sub demo()
    Dim str, str1 As String
    str = "demo"
    str1 = "DEMO"
    If str = str1 Then
    MsgBox ("String Matched")
    Else
    MsgBox ("String does not Matched")
    End If
    End Sub

    so here you will get accurate result.

    you had also mentioned that you need to set the case.

    so for that you can use "Ucase" and "Lcase" function.

    see the example mentioned below.

    Ucase:-

    UCase("demo")
    Result: "DEMO"
    
    Dim LResult As String
    
    LResult = UCase("This is a DEMO")
    
    Result:THIS IS DEMO

    Lcase:-

    UCase("DEMO")
    Result: "demo"
    
    Dim LResult As String
    
    LResult = LCase("This is a DEMO")
    
    Result: this is demo

    Same way you can check for the TitleCase.

    TitleCase("demo")
    
    Result: "Demo"

    if you want to match the value using case insensitive then also it is possible.

    you need to use strcomp function.

    see the example mentioned below.

    Option Explicit
    Sub strCompDemo()
    Dim iComp As Integer, i As Integer
    Dim str1 As String, str2 As String
    
    For i = 1 To 8
        str1 = Range("A" & i)
        str2 = Range("B" & i)
        iComp = StrComp(str1, str2, vbBinaryCompare)
        
        Select Case iComp
            Case 0
                Range("C" & i) = "Match"
            Case Else
                Range("C" & i) = "Not a match"
        End Select
    Next i
    
    End Sub
     

    Reference:

    Introduction - StrComp Function

    Then you had mentioned if value matched then change the color of cell.

    see the example mentioned below.

    Sub Demo()
    
    Range("B3").Interior.ColorIndex = 5
    End Sub
    

    you had mentioned that the values are not in orders.

    no need to worry about it.

    you need use nested loop for that .

    you need to store all the values that are going to be matched in an array.

    in the outer loop you will take 1 value from the cell.

    then it will go in to inner loop and it will match that 1 cell value with all the values stored in array.

    like if we take an example then Cell A1 contain the value "demo".

    your array contains the value demo on Arr(2) element.

    then the value of Cell A1 will matched with all the values of Arr[] and matched the correct one.

    Regards

    Deepak 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, October 4, 2016 12:45 AM
    Moderator
  • The code below will find the values in the array irrespective of the order they are in the worksheet and irrespective of the case.

    If the case matches both the spelling and case of the Array value then the interior color of the cell is Green to indicate case matched.

    If the spelling matches and case does not match then the value from the array is copied to the cell to match the case and the cell is colored Yellow to indicate it has been changed.

    When finished a MsgBox is displayed listing any values in the array not found plus a further message re the colors legend.

    The test worksheet was like the following before processing

    After processing was like the following

    Sub MatchToArray()
    
        Dim rngToTest As Range
        Dim rngToFind As Range
        Dim arrValues(1 To 8)   'Dimension a one based array with 8 elements
        Dim i As Long
        Dim strNotFound As String
        
        arrValues(1) = "Column1"
        arrValues(2) = "Column2"
        arrValues(3) = "Column3"
        arrValues(4) = "Column4"
        arrValues(5) = "Column5"
        arrValues(6) = "Column6"
        arrValues(7) = "Column7"
        arrValues(8) = "Column8"
        
        
        With Worksheets("Sheet1")   'Edit "Sheet1" to your worksheet name
            Set rngToTest = .Range(.Cells(1, 1), .Cells(1, .Columns.Count).End(xlToLeft))
        End With
        
        For i = LBound(arrValues) To UBound(arrValues)
            With rngToTest
                Set rngToFind = .Find(What:=arrValues(i), _
                                LookIn:=xlFormulas, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                
                If Not rngToFind Is Nothing Then
                    If rngToFind.Value = arrValues(i) Then
                        rngToFind.Interior.Color = vbGreen 'Case matches
                    Else
                        rngToFind.Interior.Color = vbYellow 'Case did not previously match
                        rngToFind.Value = arrValues(i)      'Change to match case in array
                    End If
                Else
                    strNotFound = strNotFound & arrValues(i) & vbCrLf
                End If
            End With
        Next i
        
        If strNotFound <> "" Then
            MsgBox "Following values not found. " & vbCrLf & _
                    strNotFound & vbCrLf & _
                    "Green matched case." & vbCrLf & _
                    "Yellow did not previously match case."
        Else
            MsgBox "All values found."
            
        End If
    End Sub
    

    Regards, OssieMac

    Tuesday, October 4, 2016 1:54 AM
  • OssieMac,

    Thank you this is exactly what I am looking for. It works just like I need it to.

    Tuesday, October 4, 2016 3:12 PM