Solved

Find Name, Insert Value & Format

Posted on 2011-09-19
2
281 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

840 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