Solved

VBA - Find Names, Format, and Change Value

Posted on 2011-09-18
8
205 Views
Last Modified: 2013-11-05
Hi Experts,

I am writing a Macro and need a little help.  Right now I used the attached code and just re-add for new names.  It finds the "Name" in Column G then for that row where the name is found makes Column A Bold, Replaces Value in Column E with "ST FirstName" (FirstName being the First Name of the person in Column G), and changes Column F to "Please send for Check in.". If the Name being searched was John Smith, it would replace the value in Column E with ST John.  I would like to have a sheet named "Names" with Column A being the full name to search for, and Column B being the value to replace in Column E.  How can I accomplish this with the code below?

Thank You!
Cody-
For Each Row In ActiveSheet.UsedRange.EntireRow.Rows
        If Row.Cells(1, 7).Value = "Name" Then
            With Row.Range("A1")
                .Font.Bold = True
                .Interior.ColorIndex = 15
            End With
            With Row.Range("E1")
                .Value = "ST FirstName"
                .Interior.ColorIndex = 15
            End With
            With Row.Range("F1")
                .Value = "Please Send for check in."
                .Font.Bold = True
            End With
        End If
    Next Row

Open in new window

0
Comment
Question by:codyvance1
  • 5
  • 3
8 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 36557490
App searches worksheet in this workbook called "Names", and goes through last names in Column A, uses Find method in Column G, when a match is found, the first name from Column B is pasted in Column E, same row, with alert message for check in Column F.  Highlighting as desired...

Here's the code, for a public module:
Sub findAndAlert()
Dim wkb As Workbook
Dim sht As Worksheet
Dim rng As Range
Dim fRange As Range
Dim myName As Range


    Set wkb = ThisWorkbook
    Set sht = wkb.Sheets("Names")
    
    For Each myName In sht.Range("A2", sht.Range("A" & sht.Rows.Count).End(xlUp)) 'Gull name to search for in Column A
        If myName <> "" Then
            Set fRange = sht.Range("G:G").Find(what:=myName.Value, LookIn:=xlFormulas, lookat:=xlWhole) 'Search in Column G for "Name" in 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
                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 for check in." 'alert message Column F
                fRange.Offset(0, -1).Font.Bold = True 'Bold Column F as in example code
            End If
        End If
    Next myName
    
End Sub

Open in new window


See attached demo workbook.  Just click the macro button to see it work.

Enjoy!

Dave
findNames-r1.xlsm
0
 

Author Comment

by:codyvance1
ID: 36557531
Hi Dave,

So this is somewhat what I was looking for, but a bit off.  I realize now my explanation was a bit off also.  It needs to look for names in the "Data" sheet Column G and then make the change in that sheet if it finds names form the "Names" sheet Column A.  It should make the changes to the "Data" sheet (i.e. bold, gray fill) and replace Column E value with the value in Column B of "Names" sheet next to the name it found.

Hope this makes sense...

Cody-
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36557537
Could you provide a sample workbook to work with so we can do this in one more go?

Dave
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 36557545
Ok - if this is not it, I will wait for you to upload a sample.  The brain is doing too many assumption "leaps" otherwise :)  You may recall from last month's E-E newsletter providing pictures and/or sample workbooks assist us in helping you!

Here's the code:
Sub findAndAlert()
Dim wkb As Workbook
Dim dataSht As Worksheet
Dim namesSht As Worksheet
Dim rng As Range
Dim fRange As Range
Dim myName As Range


    Set wkb = ThisWorkbook
    Set namesSht = wkb.Sheets("Names")
    Set dataSht = wkb.Sheets("Data")
    
    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
                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 for check in." 'alert message Column F
                fRange.Offset(0, -1).Font.Bold = True 'Bold Column F as in example code
            End If
        End If
    Next myName
    
End Sub

Open in new window


See attached demo workbook.

Dave
findNames-r2.xlsm
0
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.

 

Author Comment

by:codyvance1
ID: 36557572
Worked perfect!  Sorry for not sending with sample...

Cody-
0
 

Author Comment

by:codyvance1
ID: 36560429
Hope youre still around.. I just noticed that it only formats the first name found in the Data sheet, then does nothing for the rest.  I am attaching sample data this time.  It should format each row in "Macro Completed" sheet where name found in the "Names" sheet in Column G.  You can see some of your code I edited as it does not need to bold it on the "Names" sheet.
Macro-Sample.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36561368
Try renaming your "Paste Here" tab as "Data", as you specified in your requirements.

Cheers,

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36561373
See attached, where I just copied in your sample data.

Cheers,

Dave
findNames-r2.xlsm
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

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,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
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…

914 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

18 Experts available now in Live!

Get 1:1 Help Now