Can A Macro Help Me?

  • Thread starter Thread starter asmith87
  • Start date Start date
A

asmith87

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
 
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
 
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.
 
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:D" & 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
 
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.
 
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.
 
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
 
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
 
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).
 
Compare with the initial code....and identify whats has gone wrong..(which
lines have gone removed...)

If this post helps click Yes
 
Back
Top