Solved

Find Name, Insert Value & Format

Posted on 2011-09-19
2
267 Views
Last Modified: 2012-06-21
Hi Experts,

I received help with the attached file from other experts and just ran across an issue.  The Sub StreetTeam should find all names in the "Macro Completed" sheet Column G that are in the "Names" sheet Column A, replace the value in Column E with the ShortName from the "Names" sheet and do some simple formatting.  It works for some rows, then errors out saying apllication-defined or object-defined error.  Seems like it starts referencing the wrong lookup values?  I cant really tell.  Any help is appreciated.

Thanks,
Cody-
Sample.xls
0
Comment
Question by:codyvance1
  • 2
2 Comments
 
LVL 9

Accepted Solution

by:
chwong67 earned 500 total points
ID: 36564119
Please change the findnext coomand as below:

Public Sub StreetTeam()

Dim wkb As Workbook
Dim dataSht As Worksheet
Dim namesSht As Worksheet
Dim rng As Range
Dim fRange As Range
Dim myName As Range
Dim s As String

Set wkb = ThisWorkbook
Set namesSht = wkb.Sheets("Names")
Set dataSht = wkb.Sheets("Macro Completed")

For Each myName In namesSht.Range("A2:A100")
    If myName <> "" Then
        Set fRange = dataSht.Range("G1:G1500").Find(What:=myName.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
        If Not fRange Is Nothing Then
            s = fRange.Address
            Do
                fRange.Offset(0, -6).Font.Bold = True
                fRange.Offset(0, -6).Interior.ColorIndex = 15
                fRange.Offset(0, -2).Value = myName.Offset(0, 1).Value
                fRange.Offset(0, -2).Interior.ColorIndex = 15
                fRange.Offset(0, -1).Value = "Please Send for check in."
                fRange.Offset(0, -1).Font.Bold = True
                Set fRange = dataSht.Range("G1:G1500").FindNext(fRange)            Loop While fRange.Address <> s
        End If
    End If
    s = ""
Next myName

End Sub
0
 
LVL 9

Expert Comment

by:chwong67
ID: 36564205
This is due to vba find cell E27 for 'Craig Surdy'.
By restrict the search range G, the4 next find cell is G27 instead of E27.
Sample-1-.xls
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now