get data from another worksheet - speed issue 1

[jammic25]

Hi everyone,

I have a "main" worksheet that has a set of values. Based on this set of values I have another worksheet that has the list of the possible values(basically a look-up table) and corresponding codes that need to be retrieved and placed into another cell in the "main" worksheet. I have the code to do this(see below). My problem is the speed at which the whole process takes. For each record I have to switch back and forth between the worksheets. Is there a better method to accomplish this task at a faster speed?

CODE:
Sub Test()
Dim c As Range
Dim sFirstHit As String
Dim i
Dim j
Dim k
ThisWorkbook.Worksheets("MEDEP LabData EDD template&quot.Activate
ro = ActiveSheet.UsedRange.Rows.Count 'Total number of rows that contain data
ro1 = ThisWorkbook.Worksheets("Look up table&quot.UsedRange.Rows.Count 'Total number of rows that contain data
co = ActiveSheet.UsedRange.Columns.Count 'Total number of columns that contain data

For r = 4 To ro 'Start at row 4 and go to the end of data
i = Cells(r, 29)
Cells(r, 29).NumberFormat = "general"
ThisWorkbook.Worksheets("Look up table&quot.Activate 'goto look-up worksheet
With Columns(1)
Set c = .Find(i, LookIn:=xlValues) 'find value that matches data in main worksheet
sFirstHit = c.Address
k = c.Offset(0, 1) 'Get value in next cell that corresponds to original value
End With
ThisWorkbook.Worksheets("Main&quot.Activate
Cells(r, 13) = k 'Set cell to new data
Next
End Sub

Thanks
Jim

 

[SkipVought]

Hi,

You don't have to Active and Select methods -- in fact, Activate and Select slows the process down. So try this mod...

    Dim c As Range, wsTemp As Worksheet, wsData As Worksheet
    Dim sFirstHit As String
    Dim i
    Dim j
    Dim k
    Set wsTemp = ThisWorkbook.Worksheets("MEDEP LabData EDD template")
    ro = wsTemp.UsedRange.Rows.Count       'Total number of rows that contain data
    
    Set wsData = ThisWorkbook.Worksheets("Look up table")
    With wsData.UsedRange
        ro1 = .Rows.Count
        co = .Columns.Count    'Total number of columns that contain data
    End With
    
    For r = 4 To ro 'Start at row 4 and go to the end of data
        With wsTemp.Cells(r, 29)
            i = .Value
            .NumberFormat = "general"
        End With
        
        With wsData.Columns(1)
            Set c = .Find(i, LookIn:=xlValues) 'find value that matches data in main worksheet
            sFirstHit = c.Address
            k = c.Offset(0, 1) 'Get value in next cell that corresponds to original value
        End With
        
        wsTemp.Cells(r, 13) = k    'Set cell to new data
    Next

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[jammic25]

Thanks Skip,

That worked great!

Jim

 

[hannal]

Hi!

I have a suggestion that I think will speed up things a little. this is a method using arry calculations instead of excelfunctions like find, vlookup, match or similar.

Copy this sub and run it as a macro on a sheet to see what happens.
Hope it'll be useful, maybe you already now this.

Sub findValue()

'Creates column with codes------------------
    Range("A1").Value = "a"
    Range("A2").Value = "b"
    Range("A3").Value = "c"
    Range("A4").Value = "d"
    Range("A5").Value = "e"
'Create column with values------------------
    Range("B1").Value = 1
    Range("B2").Value = 2
    Range("B3").Value = 3
    Range("B4").Value = 4
    Range("B5").Value = 5
'Creates a cell with the code you want to find------------
    Range("C1").Value = "d"
'Selects the cell where you want to put the found value---
    Range("D1").Select
'Find the value
    Selection.FormulaArray = "=SUM((C1=A1:A5)*B1:B5)"

End Sub

It does the following:

1. Checks if the value i cell C1 is equal to any 'of the values in column A1:A5
This results in a array with values 1 if true or 0 if false

2. That array is then multiplied by the values in column B1:B5 and the sum is computed

What happens is this:
if the value in C1 is d Then

a*0 + b*0 + c*0 + d*1 + e*0

Got to rush!
Bye, HL