Excel 2010 search and copy code

[gelpena]

Hi all,

I need help with a code to search Sheet1 and copy to Sheet 2. I have attached a Workbook to simulate what I want to accomplish. I want to press a button on Sheet2 which would run the macro a search dates on Sheet1 and copy the name associated with that name to the corresponding datelabel on Sheet 2. I hope you guys can help. :confused:

[Reverend Jim]

You didn't attach anything that I can see. Can you please explain the problem a little more clearly?

search dates on Sheet1 and copy the name associated with that name

Are the dates in specific rows and/or columns? What do you mean by "name associated with that name? If you can describe the problem step by step as if you were doing it by hand then the solution (the code) often becomes much clearer.

[gelpena]

Thank you for taking the time to respond Reverend.

I'm trying to put together a scheduler:
- Sheet1 holds the raw data where column A (A1:A500) is populated with names (A1=Mary, A2=Joe, etc). Area B:AC in Sheet1 is populated with random dates.
- Sheet2 thru 13(Jan-Dec)are calendar months.
- The data in Sheet1 B:AC will be sorted and copied to its corresponding month(Sheet), But instead of passing the date, the macro will pass the name in column A.

EX: Cell A2=Joe. if Cell B2 in Sheet1 has a date in it (13-Mar) then in Sheet4 (March) the name Joe will be copied to an empty cell under a column labeled 13-Mar. If there are any other 13-Mar in Sheet1(B:AC) then those names would also appear along with Joe.

My Userform has buttons labeled with months that when pressed would bring the user to the corresponding calendar month and display who's scheduled to work.

Thanks Reverend for your help. I've tried other sites but no one has responded yet.

http://www.excelforum.com/excel-programming/811834-sample-code-for-a-userform.html
http://www.mrexcel.com/forum/showthread.php?t=610954
http://www.vbaexpress.com/forum/showthread.php?t=40784

[Reverend Jim]

Is this the algorithm?

Sheet2 = month 1
Sheet3 = month 2
etc

On Sheet1

    start at row 1
    
    while first cell in this row is not blank
    
    	get name from first cell
        start at column 2
        
        while this cell is not blank
            get month (m) and day (d) number from date in this cell
            copy name to first blank cell in column d of sheet m+1
            get next cell
            
        get next row

I'll point out that this is the VB.net forum and what you asked for is a VBA solution. If you code it as VBA then the code would appear in every workbook (I presume you would have one for each year). That means one copy of the code for every year for everyone who uses their own copy of the workbook. You CAN code it as a VB app in which case the app would load the Excel workbook, crunch the numbers, then save it back out. I can show you how to do that if you prefer.

[gelpena]

"You CAN code it as a VB app in which case the app would load the Excel workbook, crunch the numbers, then save it back out. I can show you how to do that if you prefer."

I'm totally willing to try this! how does it work?

Reverend,

This looks like something that mihgt work. But how, and please pardon my ignorance, can I implement this to my Excel Workbook? see attachments for an idea of what I'm looking for.

[Reverend Jim]

First a little code, then a few comments. To get at the workbook you have to create the Excel object.

Dim xls As New Excel.Application
Dim sheet1 As Excel.Worksheet

xls.Workbooks.Open("d:\temp\test.xls")
sheet1 = xls.Workbooks(1).Sheets(1)

This creates the Excel object and opens an existing Excel workbook. The assignment to the variable, sheet1, is not really necessary but it saves a lot of typing. Now you can access the cells by row and column number using the "Cells" collection. The top, left cell is

sheet1.Cells(1,1)

and you can get/set the value by the "Value" property as in

dim name as String = sheet1.Cells(1,1).Value

To access cells on another sheet you use

xls.Workbooks(1).Sheets(sheetnum).Cells(row,col)

or

With xls.Workbooks(1).Sheets(sheetnum)
    .Cells(row,col).Value...
End With

Having said that, you may have a problem. On sheet1, you have blank cells mixed with non-blank cells so there is no obvious way (because rows are indefinitely long) of determining when you have processed all the dates in a given row. I don't see any obvious reason for having embedded blank fields.

Once you have finished crunching the numbers you have to clean up. That consists of saving the workbook, closing the Excel application and releasing all objects. If you don't release the objects then you may end up with multiple copies of Excel running in the background chewing up resources. To check this just run Task Manager and look for Excel.exe. This didn't used to be necessary (at least not from vbScript). I don't know why it is now.

xls.WorkBooks(1).Save
xls.Workbooks.Close()
xls.Quit()

releaseObject(sheet)
releaseObject(xls)

Private Sub releaseObject(ByVal obj As Object)

    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try

End Sub

[gelpena]

On sheet1, you have blank cells mixed with non-blank cells so there is no obvious way (because rows are indefinitely long) of determining when you have processed all the dates in a given row. I don't see any obvious reason for having embedded blank fields.

Reverend,
Thanks for all the time you're putting into helping me. The search boundaries are from B2:B500 to AC2:AC500. The reason why there would be blank fields is that nobody is scheduled to work on that day therefore the scheduler would be empty.

[Reverend Jim]

I don't follow. On Sheet1, what is the correlation between the column and the day? For example, for Jane you have 12-Jan in column B and 14-Jan in column H. There is only one day between 12 and 13 but five blank columns.

[gelpena]

I don't follow. On Sheet1, what is the correlation between the column and the day? For example, for Jane you have 12-Jan in column B and 14-Jan in column H. There is only one day between 12 and 13 but five blank columns.

The columns in Sheet1, besides A1:A500, do not play any roll other than being part of the area to be searched. This is exactly the purpose of the macro I'm trying to get so that regardless of the order the dates were entered in any row, the names associated with those rows(A1:A500) would be arranged into a calendar form in Sheet2. blank cells would be ignored.

[Reverend Jim]

If blank cells are to be ignored then why iniclude them? In any case, you can scan the row from column 2 to column 500 and just process those that are non-blank.

[gelpena]

If blank cells are to be ignored then why iniclude them? In any case, you can scan the row from column 2 to column 500 and just process those that are non-blank.

This Workbook will be utilized by my uncle who has no idea what a Workbook is. I need to account for every possible scenario. And believe me, entering a date where they may fall will be common.

[gelpena]

The columns in Sheet1, besides A1:A500, do not play any roll other than being part of the area to be searched. This is exactly the purpose of the macro I'm trying to get so that regardless of the order the dates were entered in any row, the names associated with those rows(A1:A500) would be arranged into a calendar form in Sheet2. blank cells would be ignored.

Reverend,

Take a look at this code in the other forum I posted?

http://www.vbaexpress.com/forum/showthread.php?p=259418#post259418

It works for column B only at this stage.

[Reverend Jim]

I can't comment on that code because I don't speak VBA. What is your level of programming expertise? Is it sufficient so that you can turn

On Sheet1
 
start at row 1
 
while first cell in this row is not blank
 
    get name from first cell
 
    for each column from 2 to 500
        get month (m) and day (d) number from date in this cell
        copy name to first blank cell in column d of sheet m+1
 
    get next row

into actual code?

[gelpena]

I can't comment on that code because I don't speak VBA. What is your level of programming expertise? Is it sufficient so that you can turn

On Sheet1
 
start at row 1
 
while first cell in this row is not blank
 
    get name from first cell
 
    for each column from 2 to 500
        get month (m) and day (d) number from date in this cell
        copy name to first blank cell in column d of sheet m+1
 
    get next row

into actual code?

Reverend,
I programmed Java before, but many years ago. My career path has taken me away from coding in Java and much less other languages.

[Reverend Jim]

If you are unable to write it yourself are you going to be able to support it?

[gelpena]

If you are unable to write it yourself are you going to be able to support it?

I think I can. I used to be pretty good at debugging.

[Reverend Jim]

I'll try to post something for you in the morning. With luck, you can tweek it to what you need.

[Reverend Jim]

I've attached a project and an Excel spreadsheet. You'll have to modify the code to point to wherever you put the spreadsheet. I hope this helps.

[gelpena]

I've attached a project and an Excel spreadsheet. You'll have to modify the code to point to wherever you put the spreadsheet. I hope this helps.

Reverend,

I don't think I have the enrironment to run your program install in my computer. How/What do I need to run it?

[Reverend Jim]

Because this is the VB.Net forum I assumed you had Visual Studio (for creating VB.Net applications).

[gelpena]

Because this is the VB.Net forum I assumed you had Visual Studio (for creating VB.Net applications).

I feel really bad as it appears I have wasted your time. No, I do not have Visual Studio. I only have MS Excel installed.

[Reverend Jim]

Not a problem. If you know anything about VBA you can still use the logic I laid out to put the code directly into the spreadsheet. Good luck with it.

[gelpena]

Not a problem. If you know anything about VBA you can still use the logic I laid out to put the code directly into the spreadsheet. Good luck with it.

Thanks Reverend! I'll give it a shot. You have taken me as far as you could and I appreciate it. It's been my pleasure.

[Reverend Jim]

Mine too.