Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 25th, 2005, 01:52 PM
Authorized User
 
Join Date: Dec 2003
Location: , , USA.
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to AyatKh
Default Read a column and Search Row by Row in another col

Hi All,
There is a monthly report that I have to generate and it takes a lot of time (2 days) to do it manually since it consists of 400 users to check row by row. I know VBA and Excel but it’s been a long time that I haven’t use it. So I will appreciate it if someone can help me.

  One of the excel files includes 3 columns. I need to read the fist column which are Names and find the names in the second excel file . For each of the findings I need to do some calculation based on other columns.

So what I need to know is how I can read a column row by row, search the names in other column and when I find it, I need to do some calculation. and If I don't find the names, I want to insert them at the buttom of the sheet maybe in Blue Color.

For example: I have excel file1 and file2.
From file2. I need to read column A from row 1 to row 300 or more. Then based on each row I need to find the names in file1.column A .
I hope I have explained it well

Thanks,
Dorsa


 
Old January 25th, 2005, 07:22 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Bournemouth, Dorset, United Kingdom.
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi, try this for size,

Sub mdl_FindWhizzyDo()
Dim myLines As Integer
Dim myName As String
Dim myLoop As Integer
Dim myFirstNameAddress As String
Dim myCurrAdd As String
    Workbooks("Book1").Activate
    Sheets("Sheet1").Select
    myLines = Range("A65535").End(xlUp).Row
    Workbooks("Book2").Activate
    Sheets("Sheet1").Select
    For myLoop = 1 To myLines
        myName = Workbooks("Book1").Sheets("Sheet1").Range("A" & myLoop).Value
        Cells.Find(What:=myName, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
            .Activate
        myFirstNameAddress = ActiveCell.Address
        Do Until myFirstNameAddress = myCurrAdd


            'do your processing here


            Cells.Find(What:=myName, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
                xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
                .Activate
            myCurrAdd = ActiveCell.Address
        Loop
        myFirstNameAddress = ""
        myCurrAdd = ""
    Next
End Sub


cheers

Matthew

 
Old January 26th, 2005, 12:02 PM
Authorized User
 
Join Date: Dec 2003
Location: , , USA.
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to AyatKh
Default

Hi, Thanks very much for your answer, Could you pls tell me how I can specify if the name is not found in the sheet?

Thanks,
Dorsa





Similar Threads
Thread Thread Starter Forum Replies Last Post
find the FIRST USED row/column? crmpicco Excel VBA 3 July 23rd, 2013 12:52 PM
[Word] 2003 VBA - Absolute Row/Col in Tables with geoboater Word VBA 0 August 16th, 2007 07:22 PM
Column to Row virall23@yahoo.com Excel VBA 2 March 23rd, 2007 04:16 AM
Column to Row virall23@yahoo.com SQL Server DTS 0 March 1st, 2007 10:57 AM
How to get the last row and last column value ramk_1978 SQL Server 2000 1 April 4th, 2005 06:34 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.