Solved

VBA - Finad Names, replace Value & Format

Posted on 2011-09-19
3
296 Views
Last Modified: 2012-08-13
Hi Experts,

I need some help with the attached Excel file.  You can see the code I have for FindNames.  This should look at each name in Column G in "Macro Completed" sheet, and if that name is listed in "Names" sheet, it should replace value in Column E of "Macro Completed" with value next to name on Names Sheet (ST Name) as well as perform some formatting, etc.  Right now it only does it for the first instance of each name found.  I need it to work for entire sheet.

THanks!
Cody-
Macro-Sample.xls
0
Comment
Question by:codyvance1
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 500 total points
Comment Utility
Cody - see if this works:
Public Sub FindNames()

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", namesSht.Range("A" & namesSht.Rows.Count).End(xlUp)) 'First name to search for in Column A
    If myName <> "" Then
        Set fRange = dataSht.Range("G:G").Find(What:=myName.Value, LookIn:=xlFormulas, LookAt:=xlWhole) 'Search in Column G for "Name" from column A
        If Not fRange Is Nothing Then 'Name found!
            'myName.Font.Bold = True 'Make Column A name Bold as in example code
            'myName.Interior.ColorIndex = 15 'Color Code Column A
            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 'set Column E same row = column B from myName match
                fRange.Offset(0, -2).Interior.ColorIndex = 15 'color code Column E
                fRange.Offset(0, -1).Value = "Please Send to Adam Farer for check in." 'alert message Column F
                fRange.Offset(0, -1).Font.Bold = True 'Bold Column F as in example code
                Set fRange = dataSht.Cells.FindNext(fRange)
            Loop While fRange.Address <> s
        End If
    End If
    s = ""
Next myName

End Sub

Open in new window

0
 

Author Comment

by:codyvance1
Comment Utility
Awesome!  Works perfectly.   Thanks for Speedy Response!

Cody-
0
 

Author Comment

by:codyvance1
Comment Utility
Guess I jumped the gun, getting Application object defined error on

fRange.Offset(0, -6).Font.Bold = True
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 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

10 Experts available now in Live!

Get 1:1 Help Now