string values do not equal cells, then return msg
I have a cell with "A,AA,AAA" minus the quotes. On another sheet I have
4 cells, each labeled, "A", "AA", "AAA", "AAAA" The "A,AA,AAA" will be pulled apart using a split command as as string, and compared to the 4 cells on the other sheet, If any part of the now split string does not match any of the values in the 4 cells, then return a msg. So if on the first cell I had "A,AA,AAB" , "AAB" does not match any of the 4 cell values on the 2nd sheet and will return an error. If all of the now split cell values having a matching cell on the 2nd sheet, then do nothing. Any suggestions? |
string values do not equal cells, then return msg
One way:
Option Explicit Sub testme() Dim mySplit As Variant Dim myStr As String Dim iCtr As Long Dim myRng As Range Dim AMatchWasFound As Boolean Set myRng = Worksheets("Sheet2").Range("a1:A4") myStr = "a,aa,aaa" mySplit = Split(myStr, ",") AMatchWasFound = True For iCtr = LBound(mySplit) To UBound(mySplit) If Application.CountIf(myRng, mySplit(iCtr)) = 0 Then 'not found AMatchWasFound = False Exit For Else 'found End If Next iCtr If AMatchWasFound Then 'no message Else MsgBox "at least one wasn't a match" End If End Sub Kevin O'Neill wrote: I have a cell with "A,AA,AAA" minus the quotes. On another sheet I have 4 cells, each labeled, "A", "AA", "AAA", "AAAA" The "A,AA,AAA" will be pulled apart using a split command as as string, and compared to the 4 cells on the other sheet, If any part of the now split string does not match any of the values in the 4 cells, then return a msg. So if on the first cell I had "A,AA,AAB" , "AAB" does not match any of the 4 cell values on the 2nd sheet and will return an error. If all of the now split cell values having a matching cell on the 2nd sheet, then do nothing. Any suggestions? -- Dave Peterson |
string values do not equal cells, then return msg
Thanks. This is what we eventually came up with.
Dim k As Integer For k = 0 To 5 'stud input loop Dim mycell mycell = Cells(60, 3 + k) Dim valueme() As String valueme = Split(mycell, ",") Dim d1 As Double d1 = 0 Dim avar As Boolean Dim i As Integer, j As Integer Dim top top = (Sheet12.Range("B1000").End(xlUp).Row - [Bookmark].Row + 2) / 4 - 1 For i = 0 To UBound(valueme) 'input loop split avar = False valueme(i) = Trim(valueme(i)) For j = 0 To top 'load combination name loop name = Sheet12.Range("B" & [Bookmark].Row + 2 + j * 4).Value If valueme(i) = name Then avar = True Next j If avar = False Then MsgBox "You have entered and undefined Load Combination on the Input Sheet!" Exit Sub End If Next i Next k |
All times are GMT +1. The time now is 12:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com