I've been given an assignment that I think an Excel Macro can help me with,
but I'm not sure. Here's what I need to do:

I need to search within the file for a certain code (to be located in a
certain column in the file), and once I've found that code I need to verify
that the information in the code's corresponding row meets a few certain
requirements (ie. a specific cell reads "A" not "I"). I need to do this for
1000+ codes, and be able to tell which codes do and which codes do not meet
the specified requirements.

Appreciate any input, anything from feasibility to suggested methods. Thank
you!

Definitely YES. Reply with more details so that someone here would help you.
If you have a code which you have already written post that too....

If this post helps click Yes
---------------
Jacob Skaria


"asmith87" wrote:

> I've been given an assignment that I think an Excel Macro can help me with,
> but I'm not sure. Here's what I need to do:
>
> I need to search within the file for a certain code (to be located in a
> certain column in the file), and once I've found that code I need to verify
> that the information in the code's corresponding row meets a few certain
> requirements (ie. a specific cell reads "A" not "I"). I need to do this for
> 1000+ codes, and be able to tell which codes do and which codes do not meet
> the specified requirements.
>
> Appreciate any input, anything from feasibility to suggested methods. Thank
> you!

Assuming the worksheet with codes is named as 'codes' and the worksheet with
data is named 'data' few queries

1. Any specific data range to be searched (like say A1:J1000) in the 'data'
sheet or is that unknown.

2."First, I need to ensure that a certain field in that row contains a
certain letter (ie. A, not I)". Do you know which column or if you are not
sure what is the range to be searched in the row.

3. "Second, I need to verify that another certain field within that row
contains a date that is not in the past (ie. is after today)." Again the same
question do you know the column Or ...

4. "Lastly, I need to verify that another field within that row contains one
of three possible entries (ie.
says 123, 234, or 345 only)." Again the same question do you know the column
Or ...

5. "I need some way of knowing which codes fail the above tests, possibly by
changing the cell/row's color, etc. " This is to be done in 'codes' or 'data'
worksheet . I hope it is in 'codes'


If this post helps click Yes
---------------
Jacob Skaria


"asmith87" wrote:

> Excellent. To be more specific, what I need to do is thus:
>
> 1. I have one file in which one column contains a list of Charge Codes
> ("codes"). For each of these codes, I need to locate that code within a
> different file.
> 2. Each code has its own row of information. Once the code is located, there
> are three conditions that I need to test for. First, I need to ensure that a
> certain field in that row contains a certain letter (ie. A, not I). Second, I
> need to verify that another certain field within that row contains a date
> that is not in the past (ie. is after today). Lastly, I need to verify that
> another field within that row contains one of three possible entries (ie.
> says 123, 234, or 345 only).
> 3. I need some way of knowing which codes fail the above tests, possibly by
> changing the cell/row's color, etc.
>
> It seems that having two seperate files may complicate this process, in
> which case I can easily copy the column of codes to be test from the first
> file into the second. A second possible complication is that for each code
> that I test (which comes from the first file), there will be a couple of rows
> within the second file that have the same code, but differ in other fields,
> and need to be tested. To put it another way, I need to run the test on every
> instance of the code within the second file, not just the first instance.
>
> Thank you in advance for your help!
>
>
>
> "Jacob Skaria" wrote:
>
> > Definitely YES. Reply with more details so that someone here would help you.
> > If you have a code which you have already written post that too....
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "asmith87" wrote:
> >
> > > I've been given an assignment that I think an Excel Macro can help me with,
> > > but I'm not sure. Here's what I need to do:
> > >
> > > I need to search within the file for a certain code (to be located in a
> > > certain column in the file), and once I've found that code I need to verify
> > > that the information in the code's corresponding row meets a few certain
> > > requirements (ie. a specific cell reads "A" not "I"). I need to do this for
> > > 1000+ codes, and be able to tell which codes do and which codes do not meet
> > > the specified requirements.
> > >
> > > Appreciate any input, anything from feasibility to suggested methods. Thank
> > > you!

1. In "codes", column D
2. In "data", column D
3. In "data", column E
4. In "data", column F
5. It would be fine in either way, but doing it in "codes", if easier, is
ideal.




"Jacob Skaria" wrote:

> Assuming the worksheet with codes is named as 'codes' and the worksheet with
> data is named 'data' few queries
>
> 1. Any specific data range to be searched (like say A1:J1000) in the 'data'
> sheet or is that unknown.
>
> 2."First, I need to ensure that a certain field in that row contains a
> certain letter (ie. A, not I)". Do you know which column or if you are not
> sure what is the range to be searched in the row.
>
> 3. "Second, I need to verify that another certain field within that row
> contains a date that is not in the past (ie. is after today)." Again the same
> question do you know the column Or ...
>
> 4. "Lastly, I need to verify that another field within that row contains one
> of three possible entries (ie.
> says 123, 234, or 345 only)." Again the same question do you know the column
> Or ...
>
> 5. "I need some way of knowing which codes fail the above tests, possibly by
> changing the cell/row's color, etc. " This is to be done in 'codes' or 'data'
> worksheet . I hope it is in 'codes'
>
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "asmith87" wrote:
>
> > Excellent. To be more specific, what I need to do is thus:
> >
> > 1. I have one file in which one column contains a list of Charge Codes
> > ("codes"). For each of these codes, I need to locate that code within a
> > different file.
> > 2. Each code has its own row of information. Once the code is located, there
> > are three conditions that I need to test for. First, I need to ensure that a
> > certain field in that row contains a certain letter (ie. A, not I). Second, I
> > need to verify that another certain field within that row contains a date
> > that is not in the past (ie. is after today). Lastly, I need to verify that
> > another field within that row contains one of three possible entries (ie.
> > says 123, 234, or 345 only).
> > 3. I need some way of knowing which codes fail the above tests, possibly by
> > changing the cell/row's color, etc.
> >
> > It seems that having two seperate files may complicate this process, in
> > which case I can easily copy the column of codes to be test from the first
> > file into the second. A second possible complication is that for each code
> > that I test (which comes from the first file), there will be a couple of rows
> > within the second file that have the same code, but differ in other fields,
> > and need to be tested. To put it another way, I need to run the test on every
> > instance of the code within the second file, not just the first instance.
> >
> > Thank you in advance for your help!
> >
> >
> >
> > "Jacob Skaria" wrote:
> >
> > > Definitely YES. Reply with more details so that someone here would help you.
> > > If you have a code which you have already written post that too....
> > >
> > > If this post helps click Yes
> > > ---------------
> > > Jacob Skaria
> > >
> > >
> > > "asmith87" wrote:
> > >
> > > > I've been given an assignment that I think an Excel Macro can help me with,
> > > > but I'm not sure. Here's what I need to do:
> > > >
> > > > I need to search within the file for a certain code (to be located in a
> > > > certain column in the file), and once I've found that code I need to verify
> > > > that the information in the code's corresponding row meets a few certain
> > > > requirements (ie. a specific cell reads "A" not "I"). I need to do this for
> > > > 1000+ codes, and be able to tell which codes do and which codes do not meet
> > > > the specified requirements.
> > > >
> > > > Appreciate any input, anything from feasibility to suggested methods. Thank
> > > > you!

Try the below and feedback. Worksheet names 'Codes' and 'Data'. For each code
in 'Codes' Col D starting from Row1 to end the macro checks for a cell match
in ColD of 'Data'. If Col E of that row is 'A' and the date in Col 'F' is
greater than todays date and COL G contains a valid entry the code will be
highlighted in yellow.

Sub Macro()
Dim lngRow As Long, lngLastRow As Long
Dim varData As Variant
lngLastRow = Sheets("Codes").Cells(Rows.Count, 4).End(xlUp).Row
For lngRow = 1 To lngLastRow
varData = Trim(Sheets("Codes").Range("D" & lngRow))
If varData <> "" Then
If ValidCode(varData) = True Then
Sheets("Codes").Range("D" & lngRow).Interior.ColorIndex = 6
End If
End If
Next
End Sub


Function ValidCode(varTemp As Variant) As Boolean

Dim myRange As Range, rngTemp As Range
Dim strAddress As String
Dim lngLastRow As Long

With Worksheets("Data")
lngLastRow = .Cells(Rows.Count, 4).End(xlUp).Row
Set myRange = .Range("D1" & lngLastRow)

Set rngTemp = myRange.Find(What:=varTemp, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not rngTemp Is Nothing Then
strAddress = rngTemp.Address
Do
If .Cells(rngTemp.Row, "E") = "A" Then
If .Cells(rngTemp.Row, "F") > Date Then
If .Cells(rngTemp.Row, "G") = 123 Or _
.Cells(rngTemp.Row, "G") = 234 Or _
.Cells(rngTemp.Row, "G") = 345 Then
ValidCode = True: Exit Do
End If
End If
End If
Set rngTemp = myRange.FindNext(rngTemp)
Loop While Not rngTemp Is Nothing And rngTemp.Address <> strAddress
End If
End With

End Function

--
If this post helps click Yes
---------------
Jacob Skaria


"asmith87" wrote:

> 1. In "codes", column D
> 2. In "data", column D
> 3. In "data", column E
> 4. In "data", column F
> 5. It would be fine in either way, but doing it in "codes", if easier, is
> ideal.
>
>
>
>
> "Jacob Skaria" wrote:
>
> > Assuming the worksheet with codes is named as 'codes' and the worksheet with
> > data is named 'data' few queries
> >
> > 1. Any specific data range to be searched (like say A1:J1000) in the 'data'
> > sheet or is that unknown.
> >
> > 2."First, I need to ensure that a certain field in that row contains a
> > certain letter (ie. A, not I)". Do you know which column or if you are not
> > sure what is the range to be searched in the row.
> >
> > 3. "Second, I need to verify that another certain field within that row
> > contains a date that is not in the past (ie. is after today)." Again the same
> > question do you know the column Or ...
> >
> > 4. "Lastly, I need to verify that another field within that row contains one
> > of three possible entries (ie.
> > says 123, 234, or 345 only)." Again the same question do you know the column
> > Or ...
> >
> > 5. "I need some way of knowing which codes fail the above tests, possibly by
> > changing the cell/row's color, etc. " This is to be done in 'codes' or 'data'
> > worksheet . I hope it is in 'codes'
> >
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "asmith87" wrote:
> >
> > > Excellent. To be more specific, what I need to do is thus:
> > >
> > > 1. I have one file in which one column contains a list of Charge Codes
> > > ("codes"). For each of these codes, I need to locate that code within a
> > > different file.
> > > 2. Each code has its own row of information. Once the code is located, there
> > > are three conditions that I need to test for. First, I need to ensure that a
> > > certain field in that row contains a certain letter (ie. A, not I). Second, I
> > > need to verify that another certain field within that row contains a date
> > > that is not in the past (ie. is after today). Lastly, I need to verify that
> > > another field within that row contains one of three possible entries (ie.
> > > says 123, 234, or 345 only).
> > > 3. I need some way of knowing which codes fail the above tests, possibly by
> > > changing the cell/row's color, etc.
> > >
> > > It seems that having two seperate files may complicate this process, in
> > > which case I can easily copy the column of codes to be test from the first
> > > file into the second. A second possible complication is that for each code
> > > that I test (which comes from the first file), there will be a couple of rows
> > > within the second file that have the same code, but differ in other fields,
> > > and need to be tested. To put it another way, I need to run the test on every
> > > instance of the code within the second file, not just the first instance.
> > >
> > > Thank you in advance for your help!
> > >
> > >
> > >
> > > "Jacob Skaria" wrote:
> > >
> > > > Definitely YES. Reply with more details so that someone here would help you.
> > > > If you have a code which you have already written post that too....
> > > >
> > > > If this post helps click Yes
> > > > ---------------
> > > > Jacob Skaria
> > > >
> > > >
> > > > "asmith87" wrote:
> > > >
> > > > > I've been given an assignment that I think an Excel Macro can help me with,
> > > > > but I'm not sure. Here's what I need to do:
> > > > >
> > > > > I need to search within the file for a certain code (to be located in a
> > > > > certain column in the file), and once I've found that code I need to verify
> > > > > that the information in the code's corresponding row meets a few certain
> > > > > requirements (ie. a specific cell reads "A" not "I"). I need to do this for
> > > > > 1000+ codes, and be able to tell which codes do and which codes do not meet
> > > > > the specified requirements.
> > > > >
> > > > > Appreciate any input, anything from feasibility to suggested methods. Thank
> > > > > you!

So far I haven't been able to get this to work, but I may know why. The code
that is in column D of "codes" is not found in column D of "data", it is in
column A of "data". In column D of "data" I am testing for an "A" entry.
Perhaps this is why it isn't working?

Please advise on next step. I'll be experimenting with it in the meantime.

"Jacob Skaria" wrote:

> Try the below and feedback. Worksheet names 'Codes' and 'Data'. For each code
> in 'Codes' Col D starting from Row1 to end the macro checks for a cell match
> in ColD of 'Data'. If Col E of that row is 'A' and the date in Col 'F' is
> greater than todays date and COL G contains a valid entry the code will be
> highlighted in yellow.
>
> Sub Macro()
> Dim lngRow As Long, lngLastRow As Long
> Dim varData As Variant
> lngLastRow = Sheets("Codes").Cells(Rows.Count, 4).End(xlUp).Row
> For lngRow = 1 To lngLastRow
> varData = Trim(Sheets("Codes").Range("D" & lngRow))
> If varData <> "" Then
> If ValidCode(varData) = True Then
> Sheets("Codes").Range("D" & lngRow).Interior.ColorIndex = 6
> End If
> End If
> Next
> End Sub
>
>
> Function ValidCode(varTemp As Variant) As Boolean
>
> Dim myRange As Range, rngTemp As Range
> Dim strAddress As String
> Dim lngLastRow As Long
>
> With Worksheets("Data")
> lngLastRow = .Cells(Rows.Count, 4).End(xlUp).Row
> Set myRange = .Range("D1" & lngLastRow)
>
> Set rngTemp = myRange.Find(What:=varTemp, LookIn:=xlValues, _
> LookAt:=xlWhole, SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, MatchCase:=False)
> If Not rngTemp Is Nothing Then
> strAddress = rngTemp.Address
> Do
> If .Cells(rngTemp.Row, "E") = "A" Then
> If .Cells(rngTemp.Row, "F") > Date Then
> If .Cells(rngTemp.Row, "G") = 123 Or _
> .Cells(rngTemp.Row, "G") = 234 Or _
> .Cells(rngTemp.Row, "G") = 345 Then
> ValidCode = True: Exit Do
> End If
> End If
> End If
> Set rngTemp = myRange.FindNext(rngTemp)
> Loop While Not rngTemp Is Nothing And rngTemp.Address <> strAddress
> End If
> End With
>
> End Function
>
> --
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "asmith87" wrote:
>
> > 1. In "codes", column D
> > 2. In "data", column D
> > 3. In "data", column E
> > 4. In "data", column F
> > 5. It would be fine in either way, but doing it in "codes", if easier, is
> > ideal.
> >
> >
> >
> >
> > "Jacob Skaria" wrote:
> >
> > > Assuming the worksheet with codes is named as 'codes' and the worksheet with
> > > data is named 'data' few queries
> > >
> > > 1. Any specific data range to be searched (like say A1:J1000) in the 'data'
> > > sheet or is that unknown.
> > >
> > > 2."First, I need to ensure that a certain field in that row contains a
> > > certain letter (ie. A, not I)". Do you know which column or if you are not
> > > sure what is the range to be searched in the row.
> > >
> > > 3. "Second, I need to verify that another certain field within that row
> > > contains a date that is not in the past (ie. is after today)." Again the same
> > > question do you know the column Or ...
> > >
> > > 4. "Lastly, I need to verify that another field within that row contains one
> > > of three possible entries (ie.
> > > says 123, 234, or 345 only)." Again the same question do you know the column
> > > Or ...
> > >
> > > 5. "I need some way of knowing which codes fail the above tests, possibly by
> > > changing the cell/row's color, etc. " This is to be done in 'codes' or 'data'
> > > worksheet . I hope it is in 'codes'
> > >
> > >
> > > If this post helps click Yes
> > > ---------------
> > > Jacob Skaria
> > >
> > >
> > > "asmith87" wrote:
> > >
> > > > Excellent. To be more specific, what I need to do is thus:
> > > >
> > > > 1. I have one file in which one column contains a list of Charge Codes
> > > > ("codes"). For each of these codes, I need to locate that code within a
> > > > different file.
> > > > 2. Each code has its own row of information. Once the code is located, there
> > > > are three conditions that I need to test for. First, I need to ensure that a
> > > > certain field in that row contains a certain letter (ie. A, not I). Second, I
> > > > need to verify that another certain field within that row contains a date
> > > > that is not in the past (ie. is after today). Lastly, I need to verify that
> > > > another field within that row contains one of three possible entries (ie.
> > > > says 123, 234, or 345 only).
> > > > 3. I need some way of knowing which codes fail the above tests, possibly by
> > > > changing the cell/row's color, etc.
> > > >
> > > > It seems that having two seperate files may complicate this process, in
> > > > which case I can easily copy the column of codes to be test from the first
> > > > file into the second. A second possible complication is that for each code
> > > > that I test (which comes from the first file), there will be a couple of rows
> > > > within the second file that have the same code, but differ in other fields,
> > > > and need to be tested. To put it another way, I need to run the test on every
> > > > instance of the code within the second file, not just the first instance.
> > > >
> > > > Thank you in advance for your help!
> > > >
> > > >
> > > >
> > > > "Jacob Skaria" wrote:
> > > >
> > > > > Definitely YES. Reply with more details so that someone here would help you.
> > > > > If you have a code which you have already written post that too....
> > > > >
> > > > > If this post helps click Yes
> > > > > ---------------
> > > > > Jacob Skaria
> > > > >
> > > > >
> > > > > "asmith87" wrote:
> > > > >
> > > > > > I've been given an assignment that I think an Excel Macro can help me with,
> > > > > > but I'm not sure. Here's what I need to do:
> > > > > >
> > > > > > I need to search within the file for a certain code (to be located in a
> > > > > > certain column in the file), and once I've found that code I need to verify
> > > > > > that the information in the code's corresponding row meets a few certain
> > > > > > requirements (ie. a specific cell reads "A" not "I"). I need to do this for
> > > > > > 1000+ codes, and be able to tell which codes do and which codes do not meet
> > > > > > the specified requirements.
> > > > > >
> > > > > > Appreciate any input, anything from feasibility to suggested methods. Thank
> > > > > > you!

And that was the reason why I have asked few queries at the beginning and
your answers doesnt seem to be genuine . If you go through the queries you
will understand..

1. In "codes", column D (the query was range in 'data' sheet)
2. In "data", column D (i have placed the code here)
3. In "data", column E (A here)
4. In "data", column F (date here and G contains 123,234)
5. It would be fine in either way, but doing it in "codes", if easier, is
ideal.



--
If this post helps click Yes
---------------
Jacob Skaria


"asmith87" wrote:

> So far I haven't been able to get this to work, but I may know why. The code
> that is in column D of "codes" is not found in column D of "data", it is in
> column A of "data". In column D of "data" I am testing for an "A" entry.
> Perhaps this is why it isn't working?
>
> Please advise on next step. I'll be experimenting with it in the meantime.
>
> "Jacob Skaria" wrote:
>
> > Try the below and feedback. Worksheet names 'Codes' and 'Data'. For each code
> > in 'Codes' Col D starting from Row1 to end the macro checks for a cell match
> > in ColD of 'Data'. If Col E of that row is 'A' and the date in Col 'F' is
> > greater than todays date and COL G contains a valid entry the code will be
> > highlighted in yellow.
> >
> > Sub Macro()
> > Dim lngRow As Long, lngLastRow As Long
> > Dim varData As Variant
> > lngLastRow = Sheets("Codes").Cells(Rows.Count, 4).End(xlUp).Row
> > For lngRow = 1 To lngLastRow
> > varData = Trim(Sheets("Codes").Range("D" & lngRow))
> > If varData <> "" Then
> > If ValidCode(varData) = True Then
> > Sheets("Codes").Range("D" & lngRow).Interior.ColorIndex = 6
> > End If
> > End If
> > Next
> > End Sub
> >
> >
> > Function ValidCode(varTemp As Variant) As Boolean
> >
> > Dim myRange As Range, rngTemp As Range
> > Dim strAddress As String
> > Dim lngLastRow As Long
> >
> > With Worksheets("Data")
> > lngLastRow = .Cells(Rows.Count, 4).End(xlUp).Row
> > Set myRange = .Range("D1" & lngLastRow)
> >
> > Set rngTemp = myRange.Find(What:=varTemp, LookIn:=xlValues, _
> > LookAt:=xlWhole, SearchOrder:=xlByRows, _
> > SearchDirection:=xlNext, MatchCase:=False)
> > If Not rngTemp Is Nothing Then
> > strAddress = rngTemp.Address
> > Do
> > If .Cells(rngTemp.Row, "E") = "A" Then
> > If .Cells(rngTemp.Row, "F") > Date Then
> > If .Cells(rngTemp.Row, "G") = 123 Or _
> > .Cells(rngTemp.Row, "G") = 234 Or _
> > .Cells(rngTemp.Row, "G") = 345 Then
> > ValidCode = True: Exit Do
> > End If
> > End If
> > End If
> > Set rngTemp = myRange.FindNext(rngTemp)
> > Loop While Not rngTemp Is Nothing And rngTemp.Address <> strAddress
> > End If
> > End With
> >
> > End Function
> >
> > --
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "asmith87" wrote:
> >
> > > 1. In "codes", column D
> > > 2. In "data", column D
> > > 3. In "data", column E
> > > 4. In "data", column F
> > > 5. It would be fine in either way, but doing it in "codes", if easier, is
> > > ideal.
> > >
> > >
> > >
> > >
> > > "Jacob Skaria" wrote:
> > >
> > > > Assuming the worksheet with codes is named as 'codes' and the worksheet with
> > > > data is named 'data' few queries
> > > >
> > > > 1. Any specific data range to be searched (like say A1:J1000) in the 'data'
> > > > sheet or is that unknown.
> > > >
> > > > 2."First, I need to ensure that a certain field in that row contains a
> > > > certain letter (ie. A, not I)". Do you know which column or if you are not
> > > > sure what is the range to be searched in the row.
> > > >
> > > > 3. "Second, I need to verify that another certain field within that row
> > > > contains a date that is not in the past (ie. is after today)." Again the same
> > > > question do you know the column Or ...
> > > >
> > > > 4. "Lastly, I need to verify that another field within that row contains one
> > > > of three possible entries (ie.
> > > > says 123, 234, or 345 only)." Again the same question do you know the column
> > > > Or ...
> > > >
> > > > 5. "I need some way of knowing which codes fail the above tests, possibly by
> > > > changing the cell/row's color, etc. " This is to be done in 'codes' or 'data'
> > > > worksheet . I hope it is in 'codes'
> > > >
> > > >
> > > > If this post helps click Yes
> > > > ---------------
> > > > Jacob Skaria
> > > >
> > > >
> > > > "asmith87" wrote:
> > > >
> > > > > Excellent. To be more specific, what I need to do is thus:
> > > > >
> > > > > 1. I have one file in which one column contains a list of Charge Codes
> > > > > ("codes"). For each of these codes, I need to locate that code within a
> > > > > different file.
> > > > > 2. Each code has its own row of information. Once the code is located, there
> > > > > are three conditions that I need to test for. First, I need to ensure that a
> > > > > certain field in that row contains a certain letter (ie. A, not I). Second, I
> > > > > need to verify that another certain field within that row contains a date
> > > > > that is not in the past (ie. is after today). Lastly, I need to verify that
> > > > > another field within that row contains one of three possible entries (ie.
> > > > > says 123, 234, or 345 only).
> > > > > 3. I need some way of knowing which codes fail the above tests, possibly by
> > > > > changing the cell/row's color, etc.
> > > > >
> > > > > It seems that having two seperate files may complicate this process, in
> > > > > which case I can easily copy the column of codes to be test from the first
> > > > > file into the second. A second possible complication is that for each code
> > > > > that I test (which comes from the first file), there will be a couple of rows
> > > > > within the second file that have the same code, but differ in other fields,
> > > > > and need to be tested. To put it another way, I need to run the test on every
> > > > > instance of the code within the second file, not just the first instance.
> > > > >
> > > > > Thank you in advance for your help!
> > > > >
> > > > >
> > > > >
> > > > > "Jacob Skaria" wrote:
> > > > >
> > > > > > Definitely YES. Reply with more details so that someone here would help you.
> > > > > > If you have a code which you have already written post that too....
> > > > > >
> > > > > > If this post helps click Yes
> > > > > > ---------------
> > > > > > Jacob Skaria
> > > > > >
> > > > > >
> > > > > > "asmith87" wrote:
> > > > > >
> > > > > > > I've been given an assignment that I think an Excel Macro can help me with,
> > > > > > > but I'm not sure. Here's what I need to do:
> > > > > > >
> > > > > > > I need to search within the file for a certain code (to be located in a
> > > > > > > certain column in the file), and once I've found that code I need to verify
> > > > > > > that the information in the code's corresponding row meets a few certain
> > > > > > > requirements (ie. a specific cell reads "A" not "I"). I need to do this for
> > > > > > > 1000+ codes, and be able to tell which codes do and which codes do not meet
> > > > > > > the specified requirements.
> > > > > > >
> > > > > > > Appreciate any input, anything from feasibility to suggested methods. Thank
> > > > > > > you!

I apologize if I have been unclear. I'm trying very hard to understand this.

To set things straight, I am taking the code found in column D of "Codes"
and searching for it in column A of "Data". Once it is located in A of
"Data", then for that corresponding row I am testing columns D,E, and F as
previously discussed.

I have adjusted the code you sent to remedy the problems. Below, I'm adding
*** by the changes I've made, so they are obvious. As I have changed the
code, it still does not work.

Sub Macro()
Dim lngRow As Long, lngLastRow As Long
Dim varData As Variant
lngLastRow = Sheets("Codes").Cells(Rows.Count, 4).End(xlUp).Row
For lngRow = 1 To lngLastRow
varData = Trim(Sheets("Codes").Range("D" & lngRow))
If varData <> "" Then
If ValidCode(varData) = True Then
Sheets("Codes").Range("D" & lngRow).Interior.ColorIndex = 6
End If
End If
Next
End Sub


Function ValidCode(varTemp As Variant) As Boolean

Dim myRange As Range, rngTemp As Range
Dim strAddress As String
Dim lngLastRow As Long

With Worksheets("Data")
lngLastRow = .Cells(Rows.Count, 4).End(xlUp).Row
Set myRange = .Range("***A1:***A" & lngLastRow)

Set rngTemp = myRange.Find(What:=varTemp, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not rngTemp Is Nothing Then
strAddress = rngTemp.Address
Do
If .Cells(rngTemp.Row, "***D") = "A" Then
If .Cells(rngTemp.Row, "***E") > Date Then
If .Cells(rngTemp.Row, "***F") = MP Or _
.Cells(rngTemp.Row, "***F") = ALL Or _
.Cells(rngTemp.Row, "***F") = ODC Then
ValidCode = True: Exit Do
End If
End If
End If
Set rngTemp = myRange.FindNext(rngTemp)
Loop While Not rngTemp Is Nothing And rngTemp.Address <> strAddress
End If
End With



Thank you for your patience



"Jacob Skaria" wrote:

> And that was the reason why I have asked few queries at the beginning and
> your answers doesnt seem to be genuine . If you go through the queries you
> will understand..
>
> 1. In "codes", column D (the query was range in 'data' sheet)
> 2. In "data", column D (i have placed the code here)
> 3. In "data", column E (A here)
> 4. In "data", column F (date here and G contains 123,234)
> 5. It would be fine in either way, but doing it in "codes", if easier, is
> ideal.
>
>
>
> --
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "asmith87" wrote:
>
> > So far I haven't been able to get this to work, but I may know why. The code
> > that is in column D of "codes" is not found in column D of "data", it is in
> > column A of "data". In column D of "data" I am testing for an "A" entry.
> > Perhaps this is why it isn't working?
> >
> > Please advise on next step. I'll be experimenting with it in the meantime.
> >
> > "Jacob Skaria" wrote:
> >
> > > Try the below and feedback. Worksheet names 'Codes' and 'Data'. For each code
> > > in 'Codes' Col D starting from Row1 to end the macro checks for a cell match
> > > in ColD of 'Data'. If Col E of that row is 'A' and the date in Col 'F' is
> > > greater than todays date and COL G contains a valid entry the code will be
> > > highlighted in yellow.
> > >
> > > Sub Macro()
> > > Dim lngRow As Long, lngLastRow As Long
> > > Dim varData As Variant
> > > lngLastRow = Sheets("Codes").Cells(Rows.Count, 4).End(xlUp).Row
> > > For lngRow = 1 To lngLastRow
> > > varData = Trim(Sheets("Codes").Range("D" & lngRow))
> > > If varData <> "" Then
> > > If ValidCode(varData) = True Then
> > > Sheets("Codes").Range("D" & lngRow).Interior.ColorIndex = 6
> > > End If
> > > End If
> > > Next
> > > End Sub
> > >
> > >
> > > Function ValidCode(varTemp As Variant) As Boolean
> > >
> > > Dim myRange As Range, rngTemp As Range
> > > Dim strAddress As String
> > > Dim lngLastRow As Long
> > >
> > > With Worksheets("Data")
> > > lngLastRow = .Cells(Rows.Count, 4).End(xlUp).Row
> > > Set myRange = .Range("D1" & lngLastRow)
> > >
> > > Set rngTemp = myRange.Find(What:=varTemp, LookIn:=xlValues, _
> > > LookAt:=xlWhole, SearchOrder:=xlByRows, _
> > > SearchDirection:=xlNext, MatchCase:=False)
> > > If Not rngTemp Is Nothing Then
> > > strAddress = rngTemp.Address
> > > Do
> > > If .Cells(rngTemp.Row, "E") = "A" Then
> > > If .Cells(rngTemp.Row, "F") > Date Then
> > > If .Cells(rngTemp.Row, "G") = 123 Or _
> > > .Cells(rngTemp.Row, "G") = 234 Or _
> > > .Cells(rngTemp.Row, "G") = 345 Then
> > > ValidCode = True: Exit Do
> > > End If
> > > End If
> > > End If
> > > Set rngTemp = myRange.FindNext(rngTemp)
> > > Loop While Not rngTemp Is Nothing And rngTemp.Address <> strAddress
> > > End If
> > > End With
> > >
> > > End Function
> > >
> > > --
> > > If this post helps click Yes
> > > ---------------
> > > Jacob Skaria
> > >
> > >
> > > "asmith87" wrote:
> > >
> > > > 1. In "codes", column D
> > > > 2. In "data", column D
> > > > 3. In "data", column E
> > > > 4. In "data", column F
> > > > 5. It would be fine in either way, but doing it in "codes", if easier, is
> > > > ideal.
> > > >
> > > >
> > > >
> > > >
> > > > "Jacob Skaria" wrote:
> > > >
> > > > > Assuming the worksheet with codes is named as 'codes' and the worksheet with
> > > > > data is named 'data' few queries
> > > > >
> > > > > 1. Any specific data range to be searched (like say A1:J1000) in the 'data'
> > > > > sheet or is that unknown.
> > > > >
> > > > > 2."First, I need to ensure that a certain field in that row contains a
> > > > > certain letter (ie. A, not I)". Do you know which column or if you are not
> > > > > sure what is the range to be searched in the row.
> > > > >
> > > > > 3. "Second, I need to verify that another certain field within that row
> > > > > contains a date that is not in the past (ie. is after today)." Again the same
> > > > > question do you know the column Or ...
> > > > >
> > > > > 4. "Lastly, I need to verify that another field within that row contains one
> > > > > of three possible entries (ie.
> > > > > says 123, 234, or 345 only)." Again the same question do you know the column
> > > > > Or ...
> > > > >
> > > > > 5. "I need some way of knowing which codes fail the above tests, possibly by
> > > > > changing the cell/row's color, etc. " This is to be done in 'codes' or 'data'
> > > > > worksheet . I hope it is in 'codes'
> > > > >
> > > > >
> > > > > If this post helps click Yes
> > > > > ---------------
> > > > > Jacob Skaria
> > > > >
> > > > >
> > > > > "asmith87" wrote:
> > > > >
> > > > > > Excellent. To be more specific, what I need to do is thus:
> > > > > >
> > > > > > 1. I have one file in which one column contains a list of Charge Codes
> > > > > > ("codes"). For each of these codes, I need to locate that code within a
> > > > > > different file.
> > > > > > 2. Each code has its own row of information. Once the code is located, there
> > > > > > are three conditions that I need to test for. First, I need to ensure that a
> > > > > > certain field in that row contains a certain letter (ie. A, not I). Second, I
> > > > > > need to verify that another certain field within that row contains a date
> > > > > > that is not in the past (ie. is after today). Lastly, I need to verify that
> > > > > > another field within that row contains one of three possible entries (ie.
> > > > > > says 123, 234, or 345 only).
> > > > > > 3. I need some way of knowing which codes fail the above tests, possibly by
> > > > > > changing the cell/row's color, etc.
> > > > > >
> > > > > > It seems that having two seperate files may complicate this process, in
> > > > > > which case I can easily copy the column of codes to be test from the first
> > > > > > file into the second. A second possible complication is that for each code
> > > > > > that I test (which comes from the first file), there will be a couple of rows
> > > > > > within the second file that have the same code, but differ in other fields,
> > > > > > and need to be tested. To put it another way, I need to run the test on every
> > > > > > instance of the code within the second file, not just the first instance.
> > > > > >
> > > > > > Thank you in advance for your help!
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Jacob Skaria" wrote:
> > > > > >
> > > > > > > Definitely YES. Reply with more details so that someone here would help you.
> > > > > > > If you have a code which you have already written post that too....
> > > > > > >
> > > > > > > If this post helps click Yes
> > > > > > > ---------------
> > > > > > > Jacob Skaria
> > > > > > >
> > > > > > >
> > > > > > > "asmith87" wrote:
> > > > > > >
> > > > > > > > I've been given an assignment that I think an Excel Macro can help me with,
> > > > > > > > but I'm not sure. Here's what I need to do:
> > > > > > > >
> > > > > > > > I need to search within the file for a certain code (to be located in a
> > > > > > > > certain column in the file), and once I've found that code I need to verify
> > > > > > > > that the information in the code's corresponding row meets a few certain
> > > > > > > > requirements (ie. a specific cell reads "A" not "I"). I need to do this for
> > > > > > > > 1000+ codes, and be able to tell which codes do and which codes do not meet
> > > > > > > > the specified requirements.
> > > > > > > >
> > > > > > > > Appreciate any input, anything from feasibility to suggested methods. Thank
> > > > > > > > you!

In Function Valid Code do the following changes and try

1. Replace
lngLastRow = .Cells(Rows.Count, 4).End(xlUp).Row
with
lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
to get the last row from Column A

2. I hope Colum E is formatted to date OR a genuine date entry is there.

3. What is MP , ALL , ODC ?

Are these constants? I dont see the declarations in the code.
Are these text values? If so should have double quotes like "MP", "ALL", "ODC"


If this post helps click Yes
---------------
Jacob Skaria


"asmith87" wrote:

> I apologize if I have been unclear. I'm trying very hard to understand this.
>
> To set things straight, I am taking the code found in column D of "Codes"
> and searching for it in column A of "Data". Once it is located in A of
> "Data", then for that corresponding row I am testing columns D,E, and F as
> previously discussed.
>
> I have adjusted the code you sent to remedy the problems. Below, I'm adding
> *** by the changes I've made, so they are obvious. As I have changed the
> code, it still does not work.
>
> Sub Macro()
> Dim lngRow As Long, lngLastRow As Long
> Dim varData As Variant
> lngLastRow = Sheets("Codes").Cells(Rows.Count, 4).End(xlUp).Row
> For lngRow = 1 To lngLastRow
> varData = Trim(Sheets("Codes").Range("D" & lngRow))
> If varData <> "" Then
> If ValidCode(varData) = True Then
> Sheets("Codes").Range("D" & lngRow).Interior.ColorIndex = 6
> End If
> End If
> Next
> End Sub
>
>
> Function ValidCode(varTemp As Variant) As Boolean
>
> Dim myRange As Range, rngTemp As Range
> Dim strAddress As String
> Dim lngLastRow As Long
>
> With Worksheets("Data")
> lngLastRow = .Cells(Rows.Count, 4).End(xlUp).Row
> Set myRange = .Range("***A1:***A" & lngLastRow)
>
> Set rngTemp = myRange.Find(What:=varTemp, LookIn:=xlValues, _
> LookAt:=xlWhole, SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, MatchCase:=False)
> If Not rngTemp Is Nothing Then
> strAddress = rngTemp.Address
> Do
> If .Cells(rngTemp.Row, "***D") = "A" Then
> If .Cells(rngTemp.Row, "***E") > Date Then
> If .Cells(rngTemp.Row, "***F") = MP Or _
> .Cells(rngTemp.Row, "***F") = ALL Or _
> .Cells(rngTemp.Row, "***F") = ODC Then
> ValidCode = True: Exit Do
> End If
> End If
> End If
> Set rngTemp = myRange.FindNext(rngTemp)
> Loop While Not rngTemp Is Nothing And rngTemp.Address <> strAddress
> End If
> End With
>
>
>
> Thank you for your patience
>
>
>
> "Jacob Skaria" wrote:
>
> > And that was the reason why I have asked few queries at the beginning and
> > your answers doesnt seem to be genuine . If you go through the queries you
> > will understand..
> >
> > 1. In "codes", column D (the query was range in 'data' sheet)
> > 2. In "data", column D (i have placed the code here)
> > 3. In "data", column E (A here)
> > 4. In "data", column F (date here and G contains 123,234)
> > 5. It would be fine in either way, but doing it in "codes", if easier, is
> > ideal.
> >
> >
> >
> > --
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "asmith87" wrote:
> >
> > > So far I haven't been able to get this to work, but I may know why. The code
> > > that is in column D of "codes" is not found in column D of "data", it is in
> > > column A of "data". In column D of "data" I am testing for an "A" entry.
> > > Perhaps this is why it isn't working?
> > >
> > > Please advise on next step. I'll be experimenting with it in the meantime.
> > >
> > > "Jacob Skaria" wrote:
> > >
> > > > Try the below and feedback. Worksheet names 'Codes' and 'Data'. For each code
> > > > in 'Codes' Col D starting from Row1 to end the macro checks for a cell match
> > > > in ColD of 'Data'. If Col E of that row is 'A' and the date in Col 'F' is
> > > > greater than todays date and COL G contains a valid entry the code will be
> > > > highlighted in yellow.
> > > >
> > > > Sub Macro()
> > > > Dim lngRow As Long, lngLastRow As Long
> > > > Dim varData As Variant
> > > > lngLastRow = Sheets("Codes").Cells(Rows.Count, 4).End(xlUp).Row
> > > > For lngRow = 1 To lngLastRow
> > > > varData = Trim(Sheets("Codes").Range("D" & lngRow))
> > > > If varData <> "" Then
> > > > If ValidCode(varData) = True Then
> > > > Sheets("Codes").Range("D" & lngRow).Interior.ColorIndex = 6
> > > > End If
> > > > End If
> > > > Next
> > > > End Sub
> > > >
> > > >
> > > > Function ValidCode(varTemp As Variant) As Boolean
> > > >
> > > > Dim myRange As Range, rngTemp As Range
> > > > Dim strAddress As String
> > > > Dim lngLastRow As Long
> > > >
> > > > With Worksheets("Data")
> > > > lngLastRow = .Cells(Rows.Count, 4).End(xlUp).Row
> > > > Set myRange = .Range("D1" & lngLastRow)
> > > >
> > > > Set rngTemp = myRange.Find(What:=varTemp, LookIn:=xlValues, _
> > > > LookAt:=xlWhole, SearchOrder:=xlByRows, _
> > > > SearchDirection:=xlNext, MatchCase:=False)
> > > > If Not rngTemp Is Nothing Then
> > > > strAddress = rngTemp.Address
> > > > Do
> > > > If .Cells(rngTemp.Row, "E") = "A" Then
> > > > If .Cells(rngTemp.Row, "F") > Date Then
> > > > If .Cells(rngTemp.Row, "G") = 123 Or _
> > > > .Cells(rngTemp.Row, "G") = 234 Or _
> > > > .Cells(rngTemp.Row, "G") = 345 Then
> > > > ValidCode = True: Exit Do
> > > > End If
> > > > End If
> > > > End If
> > > > Set rngTemp = myRange.FindNext(rngTemp)
> > > > Loop While Not rngTemp Is Nothing And rngTemp.Address <> strAddress
> > > > End If
> > > > End With
> > > >
> > > > End Function
> > > >
> > > > --
> > > > If this post helps click Yes
> > > > ---------------
> > > > Jacob Skaria
> > > >
> > > >
> > > > "asmith87" wrote:
> > > >
> > > > > 1. In "codes", column D
> > > > > 2. In "data", column D
> > > > > 3. In "data", column E
> > > > > 4. In "data", column F
> > > > > 5. It would be fine in either way, but doing it in "codes", if easier, is
> > > > > ideal.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Jacob Skaria" wrote:
> > > > >
> > > > > > Assuming the worksheet with codes is named as 'codes' and the worksheet with
> > > > > > data is named 'data' few queries
> > > > > >
> > > > > > 1. Any specific data range to be searched (like say A1:J1000) in the 'data'
> > > > > > sheet or is that unknown.
> > > > > >
> > > > > > 2."First, I need to ensure that a certain field in that row contains a
> > > > > > certain letter (ie. A, not I)". Do you know which column or if you are not
> > > > > > sure what is the range to be searched in the row.
> > > > > >
> > > > > > 3. "Second, I need to verify that another certain field within that row
> > > > > > contains a date that is not in the past (ie. is after today)." Again the same
> > > > > > question do you know the column Or ...
> > > > > >
> > > > > > 4. "Lastly, I need to verify that another field within that row contains one
> > > > > > of three possible entries (ie.
> > > > > > says 123, 234, or 345 only)." Again the same question do you know the column
> > > > > > Or ...
> > > > > >
> > > > > > 5. "I need some way of knowing which codes fail the above tests, possibly by
> > > > > > changing the cell/row's color, etc. " This is to be done in 'codes' or 'data'
> > > > > > worksheet . I hope it is in 'codes'
> > > > > >
> > > > > >
> > > > > > If this post helps click Yes
> > > > > > ---------------
> > > > > > Jacob Skaria
> > > > > >
> > > > > >
> > > > > > "asmith87" wrote:
> > > > > >
> > > > > > > Excellent. To be more specific, what I need to do is thus:
> > > > > > >
> > > > > > > 1. I have one file in which one column contains a list of Charge Codes
> > > > > > > ("codes"). For each of these codes, I need to locate that code within a
> > > > > > > different file.
> > > > > > > 2. Each code has its own row of information. Once the code is located, there
> > > > > > > are three conditions that I need to test for. First, I need to ensure that a
> > > > > > > certain field in that row contains a certain letter (ie. A, not I). Second, I
> > > > > > > need to verify that another certain field within that row contains a date
> > > > > > > that is not in the past (ie. is after today). Lastly, I need to verify that
> > > > > > > another field within that row contains one of three possible entries (ie.
> > > > > > > says 123, 234, or 345 only).
> > > > > > > 3. I need some way of knowing which codes fail the above tests, possibly by
> > > > > > > changing the cell/row's color, etc.
> > > > > > >
> > > > > > > It seems that having two seperate files may complicate this process, in
> > > > > > > which case I can easily copy the column of codes to be test from the first
> > > > > > > file into the second. A second possible complication is that for each code
> > > > > > > that I test (which comes from the first file), there will be a couple of rows
> > > > > > > within the second file that have the same code, but differ in other fields,
> > > > > > > and need to be tested. To put it another way, I need to run the test on every
> > > > > > > instance of the code within the second file, not just the first instance.
> > > > > > >
> > > > > > > Thank you in advance for your help!
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Jacob Skaria" wrote:
> > > > > > >
> > > > > > > > Definitely YES. Reply with more details so that someone here would help you.
> > > > > > > > If you have a code which you have already written post that too....
> > > > > > > >
> > > > > > > > If this post helps click Yes
> > > > > > > > ---------------
> > > > > > > > Jacob Skaria
> > > > > > > >
> > > > > > > >
> > > > > > > > "asmith87" wrote:
> > > > > > > >
> > > > > > > > > I've been given an assignment that I think an Excel Macro can help me with,
> > > > > > > > > but I'm not sure. Here's what I need to do:
> > > > > > > > >
> > > > > > > > > I need to search within the file for a certain code (to be located in a
> > > > > > > > > certain column in the file), and once I've found that code I need to verify
> > > > > > > > > that the information in the code's corresponding row meets a few certain
> > > > > > > > > requirements (ie. a specific cell reads "A" not "I"). I need to do this for
> > > > > > > > > 1000+ codes, and be able to tell which codes do and which codes do not meet
> > > > > > > > > the specified requirements.
> > > > > > > > >
> > > > > > > > > Appreciate any input, anything from feasibility to suggested methods. Thank
> > > > > > > > > you!

I made the changes you advised, and added quotation marks around ALL, ODC,
and MP. Next, I ran the code, and it said there was an Error, "Compile Error:
Expected End Function", and it highlighted the first row of the ValidCode
part (Function ValidCode(varTemp As Variant) As Boolean).


"Jacob Skaria" wrote:

> In Function Valid Code do the following changes and try
>
> 1. Replace
> lngLastRow = .Cells(Rows.Count, 4).End(xlUp).Row
> with
> lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
> to get the last row from Column A
>
> 2. I hope Colum E is formatted to date OR a genuine date entry is there.
>
> 3. What is MP , ALL , ODC ?
>
> Are these constants? I dont see the declarations in the code.
> Are these text values? If so should have double quotes like "MP", "ALL", "ODC"
>
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "asmith87" wrote:
>
> > I apologize if I have been unclear. I'm trying very hard to understand this.
> >
> > To set things straight, I am taking the code found in column D of "Codes"
> > and searching for it in column A of "Data". Once it is located in A of
> > "Data", then for that corresponding row I am testing columns D,E, and F as
> > previously discussed.
> >
> > I have adjusted the code you sent to remedy the problems. Below, I'm adding
> > *** by the changes I've made, so they are obvious. As I have changed the
> > code, it still does not work.
> >
> > Sub Macro()
> > Dim lngRow As Long, lngLastRow As Long
> > Dim varData As Variant
> > lngLastRow = Sheets("Codes").Cells(Rows.Count, 4).End(xlUp).Row
> > For lngRow = 1 To lngLastRow
> > varData = Trim(Sheets("Codes").Range("D" & lngRow))
> > If varData <> "" Then
> > If ValidCode(varData) = True Then
> > Sheets("Codes").Range("D" & lngRow).Interior.ColorIndex = 6
> > End If
> > End If
> > Next
> > End Sub
> >
> >
> > Function ValidCode(varTemp As Variant) As Boolean
> >
> > Dim myRange As Range, rngTemp As Range
> > Dim strAddress As String
> > Dim lngLastRow As Long
> >
> > With Worksheets("Data")
> > lngLastRow = .Cells(Rows.Count, 4).End(xlUp).Row
> > Set myRange = .Range("***A1:***A" & lngLastRow)
> >
> > Set rngTemp = myRange.Find(What:=varTemp, LookIn:=xlValues, _
> > LookAt:=xlWhole, SearchOrder:=xlByRows, _
> > SearchDirection:=xlNext, MatchCase:=False)
> > If Not rngTemp Is Nothing Then
> > strAddress = rngTemp.Address
> > Do
> > If .Cells(rngTemp.Row, "***D") = "A" Then
> > If .Cells(rngTemp.Row, "***E") > Date Then
> > If .Cells(rngTemp.Row, "***F") = MP Or _
> > .Cells(rngTemp.Row, "***F") = ALL Or _
> > .Cells(rngTemp.Row, "***F") = ODC Then
> > ValidCode = True: Exit Do
> > End If
> > End If
> > End If
> > Set rngTemp = myRange.FindNext(rngTemp)
> > Loop While Not rngTemp Is Nothing And rngTemp.Address <> strAddress
> > End If
> > End With
> >
> >
> >
> > Thank you for your patience
> >
> >
> >
> > "Jacob Skaria" wrote:
> >
> > > And that was the reason why I have asked few queries at the beginning and
> > > your answers doesnt seem to be genuine . If you go through the queries you
> > > will understand..
> > >
> > > 1. In "codes", column D (the query was range in 'data' sheet)
> > > 2. In "data", column D (i have placed the code here)
> > > 3. In "data", column E (A here)
> > > 4. In "data", column F (date here and G contains 123,234)
> > > 5. It would be fine in either way, but doing it in "codes", if easier, is
> > > ideal.
> > >
> > >
> > >
> > > --
> > > If this post helps click Yes
> > > ---------------
> > > Jacob Skaria
> > >
> > >
> > > "asmith87" wrote:
> > >
> > > > So far I haven't been able to get this to work, but I may know why. The code
> > > > that is in column D of "codes" is not found in column D of "data", it is in
> > > > column A of "data". In column D of "data" I am testing for an "A" entry.
> > > > Perhaps this is why it isn't working?
> > > >
> > > > Please advise on next step. I'll be experimenting with it in the meantime.
> > > >
> > > > "Jacob Skaria" wrote:
> > > >
> > > > > Try the below and feedback. Worksheet names 'Codes' and 'Data'. For each code
> > > > > in 'Codes' Col D starting from Row1 to end the macro checks for a cell match
> > > > > in ColD of 'Data'. If Col E of that row is 'A' and the date in Col 'F' is
> > > > > greater than todays date and COL G contains a valid entry the code will be
> > > > > highlighted in yellow.
> > > > >
> > > > > Sub Macro()
> > > > > Dim lngRow As Long, lngLastRow As Long
> > > > > Dim varData As Variant
> > > > > lngLastRow = Sheets("Codes").Cells(Rows.Count, 4).End(xlUp).Row
> > > > > For lngRow = 1 To lngLastRow
> > > > > varData = Trim(Sheets("Codes").Range("D" & lngRow))
> > > > > If varData <> "" Then
> > > > > If ValidCode(varData) = True Then
> > > > > Sheets("Codes").Range("D" & lngRow).Interior.ColorIndex = 6
> > > > > End If
> > > > > End If
> > > > > Next
> > > > > End Sub
> > > > >
> > > > >
> > > > > Function ValidCode(varTemp As Variant) As Boolean
> > > > >
> > > > > Dim myRange As Range, rngTemp As Range
> > > > > Dim strAddress As String
> > > > > Dim lngLastRow As Long
> > > > >
> > > > > With Worksheets("Data")
> > > > > lngLastRow = .Cells(Rows.Count, 4).End(xlUp).Row
> > > > > Set myRange = .Range("D1" & lngLastRow)
> > > > >
> > > > > Set rngTemp = myRange.Find(What:=varTemp, LookIn:=xlValues, _
> > > > > LookAt:=xlWhole, SearchOrder:=xlByRows, _
> > > > > SearchDirection:=xlNext, MatchCase:=False)
> > > > > If Not rngTemp Is Nothing Then
> > > > > strAddress = rngTemp.Address
> > > > > Do
> > > > > If .Cells(rngTemp.Row, "E") = "A" Then
> > > > > If .Cells(rngTemp.Row, "F") > Date Then
> > > > > If .Cells(rngTemp.Row, "G") = 123 Or _
> > > > > .Cells(rngTemp.Row, "G") = 234 Or _
> > > > > .Cells(rngTemp.Row, "G") = 345 Then
> > > > > ValidCode = True: Exit Do
> > > > > End If
> > > > > End If
> > > > > End If
> > > > > Set rngTemp = myRange.FindNext(rngTemp)
> > > > > Loop While Not rngTemp Is Nothing And rngTemp.Address <> strAddress
> > > > > End If
> > > > > End With
> > > > >
> > > > > End Function
> > > > >
> > > > > --
> > > > > If this post helps click Yes
> > > > > ---------------
> > > > > Jacob Skaria
> > > > >
> > > > >
> > > > > "asmith87" wrote:
> > > > >
> > > > > > 1. In "codes", column D
> > > > > > 2. In "data", column D
> > > > > > 3. In "data", column E
> > > > > > 4. In "data", column F
> > > > > > 5. It would be fine in either way, but doing it in "codes", if easier, is
> > > > > > ideal.
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Jacob Skaria" wrote:
> > > > > >
> > > > > > > Assuming the worksheet with codes is named as 'codes' and the worksheet with
> > > > > > > data is named 'data' few queries
> > > > > > >
> > > > > > > 1. Any specific data range to be searched (like say A1:J1000) in the 'data'
> > > > > > > sheet or is that unknown.
> > > > > > >
> > > > > > > 2."First, I need to ensure that a certain field in that row contains a
> > > > > > > certain letter (ie. A, not I)". Do you know which column or if you are not
> > > > > > > sure what is the range to be searched in the row.
> > > > > > >
> > > > > > > 3. "Second, I need to verify that another certain field within that row
> > > > > > > contains a date that is not in the past (ie. is after today)." Again the same
> > > > > > > question do you know the column Or ...
> > > > > > >
> > > > > > > 4. "Lastly, I need to verify that another field within that row contains one
> > > > > > > of three possible entries (ie.
> > > > > > > says 123, 234, or 345 only)." Again the same question do you know the column
> > > > > > > Or ...
> > > > > > >
> > > > > > > 5. "I need some way of knowing which codes fail the above tests, possibly by
> > > > > > > changing the cell/row's color, etc. " This is to be done in 'codes' or 'data'
> > > > > > > worksheet . I hope it is in 'codes'
> > > > > > >
> > > > > > >
> > > > > > > If this post helps click Yes
> > > > > > > ---------------
> > > > > > > Jacob Skaria
> > > > > > >
> > > > > > >
> > > > > > > "asmith87" wrote:
> > > > > > >
> > > > > > > > Excellent. To be more specific, what I need to do is thus:
> > > > > > > >
> > > > > > > > 1. I have one file in which one column contains a list of Charge Codes
> > > > > > > > ("codes"). For each of these codes, I need to locate that code within a
> > > > > > > > different file.
> > > > > > > > 2. Each code has its own row of information. Once the code is located, there
> > > > > > > > are three conditions that I need to test for. First, I need to ensure that a
> > > > > > > > certain field in that row contains a certain letter (ie. A, not I). Second, I
> > > > > > > > need to verify that another certain field within that row contains a date
> > > > > > > > that is not in the past (ie. is after today). Lastly, I need to verify that
> > > > > > > > another field within that row contains one of three possible entries (ie.
> > > > > > > > says 123, 234, or 345 only).
> > > > > > > > 3. I need some way of knowing which codes fail the above tests, possibly by
> > > > > > > > changing the cell/row's color, etc.
> > > > > > > >
> > > > > > > > It seems that having two seperate files may complicate this process, in
> > > > > > > > which case I can easily copy the column of codes to be test from the first
> > > > > > > > file into the second. A second possible complication is that for each code
> > > > > > > > that I test (which comes from the first file), there will be a couple of rows
> > > > > > > > within the second file that have the same code, but differ in other fields,
> > > > > > > > and need to be tested. To put it another way, I need to run the test on every
> > > > > > > > instance of the code within the second file, not just the first instance.
> > > > > > > >
> > > > > > > > Thank you in advance for your help!
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > "Jacob Skaria" wrote:
> > > > > > > >
> > > > > > > > > Definitely YES. Reply with more details so that someone here would help you.
> > > > > > > > > If you have a code which you have already written post that too....
> > > > > > > > >
> > > > > > > > > If this post helps click Yes
> > > > > > > > > ---------------
> > > > > > > > > Jacob Skaria
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "asmith87" wrote:
> > > > > > > > >
> > > > > > > > > > I've been given an assignment that I think an Excel Macro can help me with,
> > > > > > > > > > but I'm not sure. Here's what I need to do:
> > > > > > > > > >
> > > > > > > > > > I need to search within the file for a certain code (to be located in a
> > > > > > > > > > certain column in the file), and once I've found that code I need to verify
> > > > > > > > > > that the information in the code's corresponding row meets a few certain
> > > > > > > > > > requirements (ie. a specific cell reads "A" not "I"). I need to do this for
> > > > > > > > > > 1000+ codes, and be able to tell which codes do and which codes do not meet
> > > > > > > > > > the specified requirements.
> > > > > > > > > >
> > > > > > > > > > Appreciate any input, anything from feasibility to suggested methods. Thank
> > > > > > > > > > you!