Solved Help with speeding my vba macro code up!

October 31, 2016 at 15:59:42
Specs: Windows 7
Hello! Can anyone help me speed my code up? I need to reference One workbook to another huge workbook which consists of 1000's and 1000's of columns/rows. My macro does what it needs to do but yet is taking hours to load. Any ideas?

Private Sub CommandButton1_Click()

Dim counter As Integer

Dim openedWorkbook As Workbook

Dim sourceSheet As Worksheet

Dim strFileToOpen As String

Dim continue As Boolean

Dim result1 As String

Dim result2 As String

Dim result3 As String

Dim x As String

Const sourceStartRow = 5

Const activeSheetStartRow = 2

strFileToOpen = Application.GetOpenFilename

continue = False

If strFileToOpen <> "" Then

Set sourceSheet = Application.ActiveSheet

Set openedWorkbook = Workbooks.Open(strFileToOpen)

continue = True

End If

If continue Then

counter = 0

With openedWorkbook.Sheets(1)

For i = sourceStartRow To .UsedRange.Rows.Count

result1 = getReturnValue(openedWorkbook.Sheets(1), .Cells(i, 2), 2, 15, sourceStartRow - 1)

result2 = getReturnValue(openedWorkbook.Sheets(1), .Cells(i, 2), 2, 14, sourceStartRow - 1)

result3 = getReturnValue(openedWorkbook.Sheets(1), .Cells(i, 2), 2, 3, sourceStartRow - 1)

If sourceSheet.Cells(activeSheetStartRow + counter, 1) = result1 Then

sourceSheet.Cells(activeSheetStartRow + counter, 1) = result1

sourceSheet.Cells(activeSheetStartRow + counter, 2) = result2

sourceSheet.Cells(activeSheetStartRow + counter, 3) = result3

End If

counter = counter + 1

Next i

End With

End If

openedWorkbook.Close

Set openedWorkbook = Nothing

End Sub

Private Function getReturnValue(ByRef sourceSheet As Worksheet, ByVal lookupValue As String, ByVal lookupCol As Integer, ByVal returnCol As Integer, ByVal headerRows As Integer) As String

Dim startRow As Integer

Dim result As String

Dim found As Boolean

With sourceSheet

startRow = headerRows + 1

found = False

result = ""

For i = startRow To .UsedRange.Rows.Count

If .Cells(i, lookupCol) = lookupValue Then

result = .Cells(i, returnCol)

found = True

Exit For

End If

Next i

If Not found Then

getReturnValue = "**NOT FOUND**"

Else

getReturnValue = result

End If

End With

End Function

message edited by kaygee


See More: Help with speeding my vba macro code up!

Report •

#1
November 1, 2016 at 07:51:41
✔ Best Answer
I don't have access to Excel at the moment, but it looks like you are looping through your values one at time looking for a match. Have you considered using the .Find method to go directly to your matching values? That should be much quicker.

https://msdn.microsoft.com/en-us/li...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 2, 2016 at 11:45:19
DerbyDad03 Thanks for the response! Do you have any idea where I would place the .find method in my code?

message edited by kaygee


Report •

#3
November 3, 2016 at 23:42:11
The .Find method replaces the For-Next. As an example, these 2 pieces of code do the same thing.

They both display the address of any cell in A1:A10 that contains the words Test Word. LoopIt checks each cell in A1:A10 individually, FindIt uses the .Find method to Find each occurrence of the string.

Sub LoopIt()
'Define search criteria
  myStr = "Test Word"
'Loop through A1:A10
   For rw = 1 To 10
'Display Address when criteria found
    If Cells(rw, 1) = myStr Then
     MsgBox Cells(rw, 1).Address
    End If
   Next
End Sub

Sub FindIt()
'Define search criteria
  myStr = "Test Word"
'Set search range to A1:A10
   With Range("A1:A10")
'Find criteria
    Set c = .Find(myStr, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
'Display Address when criteria found
            MsgBox c.Address
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
   End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question