Look Up Values with 7000+ data points

**dkang2007** · 06-26-2015, 11:37 AM

I pull a list of bonds daily and I have a formula that calculates a score. I then need to input those the scores into a database on another sheet that already has existing bonds names and scores. The daily data pull consists of new bonds and also existing bonds that are in my database. I am trying to have new bonds be added to the database with score and name while existing bonds just have the score copied into the correct row and column (by bond name and date).

I tried using a nested for loop where I take one bond name from the new list and compare it to each bond name on the old list but with 7000+ bond names daily, the VBA program takes too long.

Is there anyway to use an existing Excel function with VBA to make this process faster and more efficient?

**PCI** · 06-26-2015, 12:12 PM

It means using your example that
The last value on the right for Bond 2 : 5.67494476999126
will erase cell B4 (71.9135564307521) in sheet "NewData"
Is it

**dkang2007** · 06-26-2015, 12:15 PM

NewData is the sheet that contains the daily pulled information.

I want to take cell B4 in NewData and add it to cell G3 in the Database sheet. Column G would represent today's bond scores.

The Database sheet will keep a historic record of bond scores that are pulled daily.

**PCI** · 06-26-2015, 12:53 PM

Is that means that columns C, D, E ,F in sheet "NewData" have already data ?
It means the last column of sheet "Database" must be copid to the last column of sheet "Newdata" ?

**dkang2007** · 06-26-2015, 01:00 PM

Originally Posted by PCI

Is that means that columns C, D, E ,F in sheet "NewData" have already data ?
It means the last column of sheet "Database" must be copid to the last column of sheet "Newdata" ?

Columns C, D, E, F will never have data. The daily pull only generates the name of the bond of the score.

**PCI** · 06-26-2015, 01:15 PM

Is it what you need ...?

Option Explicit

Sub Treat()
Dim InRg As Range, OutRg As Range
Dim ObjDic   As Object
Set ObjDic = CreateObject("Scripting.Dictionary")
Dim InWS  As Worksheet, OutWS  As Worksheet
Dim LR  As Integer, LC As Integer, I As Integer
Dim F

    Set InWS = Sheets("Database")
    Set OutWS = Sheets("NewData")
    
    Set OutRg = Sheets("NewData").UsedRange
    Set InRg = Sheets("Database").UsedRange
    With InWS
        LR = .Cells(Rows.Count, 1).End(xlUp).Row
        LC = .Cells(1, Columns.Count).End(xlToLeft).Column
        For I = 2 To LR
            ObjDic.Item(.Cells(I, 1).Value) = .Cells(I, LC).Value
        Next
    End With

    With OutWS
        LR = .Cells(Rows.Count, 1).End(xlUp).Row
        LC = .UsedRange.Columns.Count
        For I = 3 To LR
            If (ObjDic.exists(.Cells(I, 1).Value)) Then
                .Cells(I, LC + 1).Value = ObjDic.Item(.Cells(I, 1).Value)
                ObjDic.Remove (.Cells(I, 1).Value)
            End If
        Next
        For Each F In ObjDic.keys
            LR = LR + 1
            .Cells(7, 1) = F
            .Cells(7, LC) = ObjDic.Item(F)
        Next
    End With
End Sub

**Alf** · 06-26-2015, 02:33 PM

A slightly different approach. Assuming that you wish to add new data to the next empty column the macro will find the first empty column in the "Database" sheet and copy the data from the "NewData" to that empty column.

To test run macr "Adder" from the "Database" sheet.

Alf

Look Up Values with 7000+ data points

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View