Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: Matching cells across columns, and returning equal values | Excel Discussion (Misc queries) | |||
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row | Excel Worksheet Functions | |||
Formula to find equal values with blank cells | Excel Discussion (Misc queries) | |||
lineup equal values by inserting empty cells | Excel Discussion (Misc queries) | |||
SOS VBA Code Emergency: need to copy tell to empty cell direct below where values in adjacent cells in different column are equal to each other. | Excel Programming |