Solved

Find Name, Insert Value & Format

Posted on 2011-09-19
2
256 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
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

743 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

8 Experts available now in Live!

Get 1:1 Help Now