VBA Running very slow when getting values from other wb

**scottmoss** · 11-07-2012, 03:32 PM

Hi,

This is a long one and thank you in advance for any tips/support.

I've got a few loops that basically lookup a customer number in another workbook and when its found it import x amount of cells. The workbook with the code in and the workbook it's importing the data from both have over 150 columns and around 350 rows (customers).

When I'm running it takes my bad boy work pc (8 Processors with 8gb Of Ram) over 35 minutes to run. i've put loads of application statue bar notes in so i can see where its hanging and finding the record to import is quick but its the:-

If Not Workbooks(NWB).Sheets(NWBS).Range("DU" & FREF).Value = "" Then Workbooks(TWB).Sheets("data").Range("P" & CREF) =_ CDec(Workbooks(NWB).Sheets(NWBS).Range("DU" & FREF).Value)

Workbooks(TWB).Sheets("data").Range("T" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("E" & FREF)

I have lots of these and this is where its being slow. It flies through the first 60 records in around 1 minute but after that it just gets slower and slower.

I've even tried to remove lines of data once its matched to reduce the lookup but that doesn't help, it can't be to do with the new workbook growing in size because it already has the data populated.

Any info or tips of how i can improve this would be great.

Thanks again,

Scott

**TMS** · 11-07-2012, 03:51 PM

Difficult to know based on the information provided. 350 records (rows) by 150 fields (columns) doesn't seem that big ... even times two.

Are both workbooks open all the time? How do you process the records?

Probably be useful to share all the code and, ideally, sample workbooks (obviously, with sensitive data removed)

Regards, TMS

**scottmoss** · 11-07-2012, 04:02 PM

Hi,

Thanks for the quick reply.

The lookup to match the records is happening very quick but its all this thats taking ages when it finds the record:

It looks very messy when pasting but its all needed as the columns are in completely different places in each workbook.

If Not Workbooks(NWB).Sheets(NWBS).Range("DT" & FREF).Value = "" Then Workbooks(TWB).Sheets("data").Range("M" & CREF) = CDec(Workbooks(NWB).Sheets(NWBS).Range("DT" & FREF).Value)
If Not Workbooks(NWB).Sheets(NWBS).Range("BA" & FREF).Value = "" Then Workbooks(TWB).Sheets("data").Range("N" & CREF) = CDec(Workbooks(NWB).Sheets(NWBS).Range("BA" & FREF).Value)
If Not Workbooks(NWB).Sheets(NWBS).Range("DU" & FREF).Value = "" Then Workbooks(TWB).Sheets("data").Range("P" & CREF) = CDec(Workbooks(NWB).Sheets(NWBS).Range("DU" & FREF).Value)

Workbooks(TWB).Sheets("data").Range("T" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("E" & FREF)
Workbooks(TWB).Sheets("data").Range("U" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("N" & FREF)
Workbooks(TWB).Sheets("data").Range("V" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("P" & FREF)
Workbooks(TWB).Sheets("data").Range("W" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("B" & FREF)
                       
            If Len(Workbooks(NWB).Sheets(NWBS).Range("Q" & FREF)) = 7 Then
            DD = Left(Workbooks(NWB).Sheets(NWBS).Range("Q" & FREF), 1)
            DD = "0" & DD
            MM = Mid(Workbooks(NWB).Sheets(NWBS).Range("Q" & FREF), 2, 2)
            YYYY = Right(Workbooks(NWB).Sheets(NWBS).Range("Q" & FREF), 4)
            Workbooks(TWB).Sheets("data").Range("X" & CREF) = Format(DD & "/" & MM & "/" & YYYY, "MM/DD/YYYY")
            Else
            DD = Left(Workbooks(NWB).Sheets(NWBS).Range("Q" & FREF), 2)
            MM = Mid(Workbooks(NWB).Sheets(NWBS).Range("Q" & FREF), 3, 2)
            YYYY = Right(Workbooks(NWB).Sheets(NWBS).Range("Q" & FREF), 4)
            Workbooks(TWB).Sheets("data").Range("X" & CREF) = Format(DD & "/" & MM & "/" & YYYY, "MM/DD/YYYY")
            End If
            If Workbooks(TWB).Sheets("data").Range("X" & CREF) = "//" Then Workbooks(TWB).Sheets("data").Range("X" & CREF) = ""
            
            If InStr(Workbooks(NWB).Sheets(NWBS).Range("R" & FREF), ",") > 1 Then _
            Workbooks(TWB).Sheets("data").Range("AA" & CREF) = Left(Workbooks(NWB).Sheets(NWBS).Range("R" & FREF), InStr(Workbooks(NWB).Sheets(NWBS).Range("R" & FREF), ",") - 1)
                
            DD = Right(Workbooks(NWB).Sheets(NWBS).Range("T" & FREF), 2)
            MM = Mid(Workbooks(NWB).Sheets(NWBS).Range("T" & FREF), 5, 2)
            YYYY = Left(Workbooks(NWB).Sheets(NWBS).Range("T" & FREF), 4)
            Workbooks(TWB).Sheets("data").Range("AB" & CREF) = Format(DD & "/" & MM & "/" & YYYY, "MM/DD/YYYY")
            If Workbooks(TWB).Sheets("data").Range("AB" & CREF) = "//" Then Workbooks(TWB).Sheets("data").Range("AB" & CREF) = ""
                        
            Workbooks(TWB).Sheets("data").Range("AC" & CREF) = "Financial Strength " & Workbooks(NWB).Sheets(NWBS).Range("DS" & FREF) & " " & Workbooks(NWB).Sheets(NWBS).Range("DU" & FREF) & ", based on tangible net worth of " & Format(CDec(Workbooks(NWB).Sheets(NWBS).Range("BA" & FREF)), "£#,##0")
            If Workbooks(TWB).Sheets("data").Range("AC" & CREF) = "Financial Strength  , based on tangible net worth of £0" Then Workbooks(TWB).Sheets("data").Range("AC" & CREF) = ""
            
            Workbooks(TWB).Sheets("data").Range("AF" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("DW" & FREF) & " " & Workbooks(NWB).Sheets(NWBS).Range("DY" & FREF) & "-" & Workbooks(NWB).Sheets(NWBS).Range("EA" & FREF) & " " & Workbooks(NWB).Sheets(NWBS).Range("EC" & FREF) & "-" & Workbooks(NWB).Sheets(NWBS).Range("EE" & FREF) & " " & Workbooks(NWB).Sheets(NWBS).Range("EG" & FREF) & "-" & Workbooks(NWB).Sheets(NWBS).Range("EI" & FREF) & " " & Workbooks(NWB).Sheets(NWBS).Range("EJ" & FREF) & "-" & Workbooks(NWB).Sheets(NWBS).Range("EM" & FREF) & " " & Workbooks(NWB).Sheets(NWBS).Range("EO" & FREF) & "-" & Workbooks(NWB).Sheets(NWBS).Range("EQ" & FREF) & " " & Workbooks(NWB).Sheets(NWBS).Range("ES" & FREF) & "-" & Workbooks(NWB).Sheets(NWBS).Range("EU" & FREF) & " " & Workbooks(NWB).Sheets(NWBS).Range("ew" & FREF) & "-" & Workbooks(NWB).Sheets(NWBS).Range("EY" & FREF) & " " & Workbooks(NWB).Sheets(NWBS).Range("fa" & FREF)
                        
            Workbooks(TWB).Sheets("data").Range("AZ" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("AD" & FREF)
            Workbooks(TWB).Sheets("data").Range("BA" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("AE" & FREF)
            Workbooks(TWB).Sheets("data").Range("BC" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("AF" & FREF)
            Workbooks(TWB).Sheets("data").Range("BD" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("AG" & FREF)
            Workbooks(TWB).Sheets("data").Range("BE" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("AH" & FREF)
            Workbooks(TWB).Sheets("data").Range("BF" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("AI" & FREF)
            Workbooks(TWB).Sheets("data").Range("BG" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("AV" & FREF)
            Workbooks(TWB).Sheets("data").Range("BH" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("AJ" & FREF)
            Workbooks(TWB).Sheets("data").Range("BI" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("AK" & FREF)
            Workbooks(TWB).Sheets("data").Range("BJ" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("AL" & FREF)
            Workbooks(TWB).Sheets("data").Range("BK" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("AM" & FREF)
            Workbooks(TWB).Sheets("data").Range("BL" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("AN" & FREF)
            Workbooks(TWB).Sheets("data").Range("BM" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("AW" & FREF)
            Workbooks(TWB).Sheets("data").Range("BN" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("AO" & FREF)
            Workbooks(TWB).Sheets("data").Range("BO" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("AP" & FREF)
            Workbooks(TWB).Sheets("data").Range("BP" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("AQ" & FREF)
            Workbooks(TWB).Sheets("data").Range("BQ" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("AR" & FREF)
            Workbooks(TWB).Sheets("data").Range("BR" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("AS" & FREF)
            Workbooks(TWB).Sheets("data").Range("BS" & CREF) = Workbooks(NWB).Sheets(NWBS).Range("AT" & FREF)
                        
            Workbooks(NWB).Sheets(NWBS).Range("GA" & FREF) = "Used"
            Workbooks(NWB).Activate
            ActiveSheet.Rows(FREF).EntireRow.Delete

**scottmoss** · 11-07-2012, 04:10 PM

Sorry forgot to mention that it opens the other workbook when you start to run it and when finished it will close the other workbook (as long as all records have been found).

Thanks again,

Scott

**TMS** · 11-07-2012, 04:48 PM

You're still only sharing what you think is the problem area, not the whole of the code.

That said, I'd be inclined to do something like:

Dim shTWB As Worksheet
Dim shNWB As Worksheet
Set shTWB = Workbooks(TWB).Sheets("data")
Set shNWB = Workbooks(NWB).Sheets(NWBS)
With shTWB
    .Range("AZ" & CREF) = shNWB.Range("AD" & FREF)
    .Range("BA" & CREF) = shNWB.Range("AE" & FREF)
    .Range("BC" & CREF) = shNWB.Range("AF" & FREF)
    .Range("BD" & CREF) = shNWB.Range("AG" & FREF)
    .Range("BE" & CREF) = shNWB.Range("AH" & FREF)
    .Range("BF" & CREF) = shNWB.Range("AI" & FREF)
    .Range("BG" & CREF) = shNWB.Range("AV" & FREF)
    .Range("BH" & CREF) = shNWB.Range("AJ" & FREF)
    .Range("BI" & CREF) = shNWB.Range("AK" & FREF)
    .Range("BJ" & CREF) = shNWB.Range("AL" & FREF)
    .Range("BK" & CREF) = shNWB.Range("AM" & FREF)
    .Range("BL" & CREF) = shNWB.Range("AN" & FREF)
    .Range("BM" & CREF) = shNWB.Range("AW" & FREF)
    .Range("BN" & CREF) = shNWB.Range("AO" & FREF)
    .Range("BO" & CREF) = shNWB.Range("AP" & FREF)
    .Range("BP" & CREF) = shNWB.Range("AQ" & FREF)
    .Range("BQ" & CREF) = shNWB.Range("AR" & FREF)
    .Range("BR" & CREF) = shNWB.Range("AS" & FREF)
    .Range("BS" & CREF) = shNWB.Range("AT" & FREF)
End With

In theory, it should make it a little quicker as it has references for the sheets ... and it, at least, makes the code easier to read.

Regards, TMS