VBA-Right Direction?

[Chemical Engineering Coop]

Hi,

Would like to convert values in cells within a certain column. The column must be selected by it's header name "wyarctick_contactID", due to the fact it's not stationary (likes to move around). I thought maybe defining a range(all row 1 cells) and then stating "if value="wyarctick_contract" select entire column. I don't know if this is the best way or not. Maybe being able to make this column an array based by it's header name? If it would have to stay within the same column of course I can make that happen as well.

Now for the cross reference.
Below is a copy for example of the columns and cells. Like I said the column with desired cells will move around. The length of the data will also vary based upon the range of dates. I think I can use the loop until "" (nothing) due to rows being random in number (user chooses the amount of dates)

<table border="0" cellpadding="0" cellspacing="0" width="469"><colgroup><col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> <col style="mso-width-source:userset;mso-width-alt:4205;width:86pt" width="115"> <col style="mso-width-source:userset;mso-width-alt:4534;width:93pt" width="124"> <col style="mso-width-source:userset;mso-width-alt:4644;width:95pt" width="127"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;width:77pt" height="17" width="103">wyarctick_date</td> <td class="xl24" style="width:86pt" width="115">wyarctick_timein</td> <td class="xl24" style="width:93pt" width="124">wyarctick_timeout</td> <td class="xl24" style="width:95pt" width="127">wyarctick_contract</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" align="right" height="17">5/3/2011 0:00</td> <td align="right">0.29</td> <td align="right">0.47</td> <td align="right">2381</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" align="right" height="17">5/3/2011 0:00</td> <td align="right">0.53</td> <td align="right">1.08</td> <td align="right">2381</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" align="right" height="17">5/3/2011 0:00</td> <td align="right">1.2</td> <td align="right">1.37</td> <td align="right">230821</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" align="right" height="17">5/3/2011 0:00</td> <td align="right">1.25</td> <td align="right">1.48</td> <td align="right">23051</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" align="right" height="17">5/3/2011 0:00</td> <td align="right">3.25</td> <td align="right">3.49</td> <td align="right">23370</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" align="right" height="17">5/3/2011 0:00</td> <td align="right">3.27</td> <td align="right">3.51</td> <td align="right">23370</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" align="right" height="17">5/3/2011 0:00</td> <td align="right">4.24</td> <td align="right">4.41</td> <td align="right">233300</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" align="right" height="17">5/3/2011 0:00</td> <td align="right">4.28</td> <td align="right">4.5</td> <td align="right">230542</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" align="right" height="17">5/3/2011 0:00</td> <td align="right">4.38</td> <td align="right">5.06</td> <td align="right">2381</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" align="right" height="17">5/3/2011 0:00</td> <td align="right">6.21</td> <td align="right">6.35</td> <td align="right">2311</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" align="right" height="17">5/3/2011 0:00</td> <td align="right">6.22</td> <td align="right">6.46</td> <td align="right">2318</td> </tr> </tbody></table>
The contractor ID number correspond with the second sheet (cross reference sheet) Here is an example piece of the second sheet.
<table border="0" cellpadding="0" cellspacing="0" width="516"><colgroup><col style="mso-width-source:userset;mso-width-alt:10861;width:223pt" width="297"> <col style="mso-width-source:userset;mso-width-alt:4534;width:93pt" width="124"> <col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;width:223pt" height="17" width="297">
</td> <td class="xl27" style="width:93pt" width="124">
</td> <td class="xl26" style="width:71pt" width="95">Sawdust</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">PRODUCT CODE</td> <td class="xl27">
</td> <td class="xl26">20</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" height="17">
</td> <td class="xl27">
</td> <td class="xl26">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl26" style="height:12.75pt" height="17">Supplier & Mill Name</td> <td class="xl24">Supplier </td> <td class="xl26">Contract No's</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" height="17">
</td> <td class="xl26">Number</td> <td class="xl26">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" height="17">Pleasant River Lumber S/F Planer Fines</td> <td class="xl24">X3080</td> <td class="xl24">23801</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" height="17">Prime Timber</td> <td class="xl24">X3549</td> <td class="xl24">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" height="17">Pleasant River - Fuel</td> <td class="xl24">X3080</td> <td class="xl24">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" height="17">Pleasant River Lumber S/F Saw.</td> <td class="xl24">X3080</td> <td class="xl24">2381</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" height="17">Prentiss & Carlisle</td> <td class="xl24">X3520</td> <td class="xl24">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" height="17">Pleasant River Lumber S/F Shv.</td> <td class="xl24">X3080</td> <td class="xl24">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" height="17">Pleasant River Lumber S/F Mx'd Saw/Shv</td> <td class="xl24">X3080</td> <td class="xl24">2382</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" height="17">Pleasant River-Enfield S/F Shv</td> <td class="xl24">X3080</td> <td class="xl24">
</td> </tr> </tbody></table>
So as you can see if the 2381 appears in the contractor column from the first page then "Pleasent River Lumber S/F Saw" would need to be what the cell would change to. There are over 500 contractors so going through and defining what each term should be with "if" statements seems a little time consuming if there were a possibility that a macro could be made that...
1.) found the number in sheet2
2.) found the supplier name in the same row within column a,
3.) apply that name to the cell with that contractor ID number.

Any help would be great as I am attacking this on my own, and it's a little daunting. I need to make a macro due to the fact that I need to repeat this process many time based upon user selected dates.

Thank you all very much in advance for even taking a peak at this issue. If you need more information or would like to get more data from me then send me a message and I will get back to you as quick as possible.

Again, thank you.

Sid Lewis

 

[ColinKJ]

Hi Sid,

Based on the sheet 1 example data you've shown (before), can you post how you want to see it when the code has finished (after).

 

[jim may]

Here's a Quick Shot at things, with the following Layout (Note carefully)...

Excel Workbook
	A	B	C	D
1	wyarctick_date	wyarctick_timein	wyarctick_timeout	wyarctick_contract
2	5/3/2011	0.29	0.47	2381
3	5/3/2011	0.53	1.08	2381
4	5/3/2011	1.2	1.37	230821
5	5/3/2011	1.25	1.48	23051
6	5/3/2011	3.25	3.49	23370
7	5/3/2011	3.27	3.51	23370
8	5/3/2011	4.24	4.41	233300
9	5/3/2011	4.28	4.5	230542
10	5/3/2011	4.38	5.06	2381
11	5/3/2011	6.21	6.35	2311
12	5/3/2011	6.22	6.46	2318
Sheet1

Excel 2007

Excel Workbook
	A	B	C
1	PRODUCT CODE		20
2
3	Supplier & Mill Name	Supplier	Contract No's
4		Number
5	Pleasant River Lumber S/F Planer Fines	X3080	23801
6	Prime Timber	X3549
7	Pleasant River - Fuel	X3080
8	Pleasant River Lumber S/F Saw.	X3080	2381
9	Prentiss & Carlisle	X3520
10	Pleasant River Lumber S/F Shv.	X3080
11	Pleasant River Lumber S/F Mx'd Saw/Shv	X3080	2382
12	Pleasant River-Enfield S/F Shv	X3080
Sheet2

Excel 2007

In a backup copy of your file
Paste this code into a standard module and then Run it...

Sub Foo()
Dim ColumnAddress As Range
Sheets("Sheet1").Activate
Set Rng1 = Range("A1:D1")
With Rng1
    Set ColumnAddress = .Find(what:="wyarctick_contract", LookIn:=xlValues)
    MyColAddress = ColumnAddress.Address
End With
    ColumnLet = ColLetter(ColumnAddress)
    Range(ColumnLet & 1).Select 'Select the Column Header of "wyarctick_contract"
    ActiveCell.Offset(1).Select
    Sheets("Sheet2").Activate
        LR = Range("C" & Rows.Count).End(xlUp).Row
    Sheets("Sheet1").Activate
    Do Until ActiveCell = ""
    Set DestRng = Sheets("Sheet2").Range("C5:C" & LR)
        For Each c In DestRng
            If c.Value = ActiveCell Then
            ActiveCell.Value = c.Offset(, -2).Value
            End If
        Next c
    ActiveCell.Offset(1).Select
    Loop
Range("A1").Select
End Sub

Function ColLetter(Rng As Range) As String
ColLetter = Left(Rng.Address(True, False), _
InStr(1, Rng.Address(True, False), "$", 1) - 1)
End Function

 

[Chemical Engineering Coop]

I applied Jim's code and it worked great!!! Thanks a bunch!!!!