Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Find Name, Insert Value & Format

Posted on 2011-09-19
2
Medium Priority
?
308 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:Cody Vance
  • 2
2 Comments
 
LVL 9

Accepted Solution

by:
chwong67 earned 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

580 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