excel coulmn to lookup table in access

[frankee_gee]

Please help,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have a spreadsheet with 5400 records/rows. This will be imported into an Access 2007 DB. This DB has multiple tables including lookup tables. <o></o>
<o></o>
In Excel the contents of column "D" titled UnitID, must be converted to numbers, which will very from 1 thru 96 depending on the content.<o></o>
<o></o>
All 96 lookup values are in a second sheet in this excel book. <o></o>
<o></o>
Say in sheet "Tag_Log" cell D6 = Boiler<o></o>
<o></o>
In sheet "Units" B29 = Boiler and A29 = 28<o></o>
<o></o>
Want "Tag_Log" cell D6 = 28<o></o>
<o></o>
How can this be accomplished?<o></o>
<o> </o>
<o> </o>
Want the Current UnitID column in Sheet: “Tag_Log” (Data).<o></o>
<o> </o>
To be replaced by UnitID column in Sheet: “Units”(Lookup Table).<o></o>
<o> </o>
Must match UnitID column in sheet: Tag_Log to UnitNo in sheet: “Units”. Then take the number in column A UnitID in that same row and replace the contents of UnitID in sheet: Tag_Log.<o></o>

How can this be done?


Thanks for your help.

Frank.

 

[J Ericson]

Hey Frankee,

Looking at your posting history it looks like your about as new to this as I am, but have a pair of solutions.

Typically I would use VLOOKUP to return the values from the "Units" sheet into the "Tag_Log" sheet. However to do this involves some very minor tweaking.

On the "Units" sheet, switch the order of columns A and B so that A contains the text (such as boiler) and column B contains UnitID
On the "Tag_Log" sheet, insert a column just after the 'D' column. In your newly inserted 'E' column in cell E2 insert the following formula; =VLOOKUP(D2,Units!A:B,2,0)



The second solution is probably more dangerous. I only say that because I am only starting to learn VB scripting. This may not be the most efficient code but it worked smoothly on my machine. The code does make a few assumptions;
1) The sheets names are always as you described
2) The data is always in the columns you described
3) D2 is where the data is to start being replaced

The script takes about 6-8 seconds to run through all 5400 lines on my machine.

Sub UnitID()

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Dim CurVal As String
    Dim Rng As Range

    On Error Resume Next
    
    Range("D2").Select
    
    Do While Not IsEmpty(ActiveCell)
        
        CurVal = ActiveCell.Value
       
        ActiveCell = "Home"
        
        With Worksheets("Units").Range("B:B").Select
            Selection.Find(What:=CurVal, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False, SearchFormat:=False).Select
        End With

        Selection.Offset(0, -1).Select
        
        NewVal = ActiveCell.Value
      
        With Sheets("Tag_Log").Range("D:D").Select
            Selection.Find(What:="Home", After:=Cells(1, 4), LookIn:=xlFormulas, LookAt _
                :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False, SearchFormat:=False).Select
        End With
        ,
        Selection.Replace What:="Home", Replacement:=NewVal
        ActiveCell.Offset(1, 0).Select
        
    Loop

End Sub

Hope one of these helps you,
J Ericson

 

[frankee_gee]

J,

Thanks for your reply.

Your first solution:

Will not convert the cells where the number is stored as TEXT. Any idea why?

Thanks,

Frankee