Most efficient way to loop thru a column searching for a specific values

Question

I have some code that I want to search thru a row of column names (row 7) and identify whether or not the column name is part of a list I have. If it detects that it is part of the list, then it will go down that column and convert the formulas to values by doing Sheet.Range.Value=Sheet.Range.Value.

For example, if it detects the word Apple or Banana, it will loop thru the column and convert the formulas into values.

However, I have found that this probably isn't the most efficient way to achieve this. I have the code listed below. Does anyone know how to make this more efficient?

Dim lastcol, lastrow As Long

    lastcol = Sheets("Sheet1").Cells(7, Columns.Count).End(xlToLeft).Column
    lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    Dim z
    For i = 5 To lastcol
        If Sheets("Sheet1").Cells(7, i).value = "Banana" Then
            For z = 9 To lastrow
                Sheets("Sheet1").Cells(z, i).value = Sheets("Sheet1").Cells(z, i).value
            Next z
        End If
        If Sheets("Sheet1").Cells(7, i).value = "Apple" Then
            For z = 9 To lastrow
                Sheets("Sheet1").Cells(z, i).value = Sheets("Sheet1").Cells(z, i).value
            Next z
        End If
        If Sheets("Sheet1").Cells(7, i).value = "Coconut" Then
            For z = 9 To lastrow
                Sheets("Sheet1").Cells(z, i).value = Sheets("Sheet1").Cells(z, i).value
            Next z
        End If
        If Sheets("Sheet1").Cells(7, i).value = "Kiwi" Then
            For z = 9 To lastrow
                Sheets("Sheet1").Cells(z, i).value = Sheets("Sheet1").Cells(z, i).value
            Next z
        End If
        If Sheets("Sheet1").Cells(7, i).value = "Watermelon" Then
            For z = 9 To lastrow
                Sheets("Sheet1").Cells(z, i).value = Sheets("Sheet1").Cells(z, i).value
            Next z
        End If
        If Sheets("Sheet1").Cells(7, i).value = "Orange" Then
            For z = 9 To lastrow
                Sheets("Sheet1").Cells(z, i).value = Sheets("Sheet1").Cells(z, i).value
            Next z
        End If
    Next i

In your nested `For z = 9 to lastRow`, I'm pretty sure you can remove that and just do `Range(Cells(9,i),cells(lastRow,i)).Value = Range(Cells(9,i),cells(lastRow,i)).Value` and set those all in one go. Also, you can use `Else If`, instead of `End If`, since with `If / ... / End If / If / ... / End If`will check every `If` condition. Assuming a cell is one value, you could do `If [cell].Value = "Banana" Then / [that range formula above] / ElseIf [cell].Value = "Apple" / ...` — BruceWayne, Jul 31 '19 at 14:16

Justyna MK · Accepted Answer · 2019-07-31T14:55:59.210

3

You can use an array of search words (arrWords) as an alternative solution, and then check if a given header (Cells(7,i).Value) belongs to this array (Application.Match):

Sub foo()
    Dim lastcol As Long, lastrow As Long, z As Long
    Dim arrWords As Variant

    arrWords = Array("Banana", "Apple", "Coconut", "Kiwi", "Watermelon", "Orange")
    lastcol = Sheets("Sheet1").Cells(7, Columns.Count).End(xlToLeft).Column
    lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 5 To lastcol
        If Not IsError(Application.Match(Sheets("Sheet1").Cells(7, i).Value, arrWords, 0)) Then
            For z = 9 To lastrow
                Sheets("Sheet1").Cells(z, i).Value = Sheets("Sheet1").Cells(z, i).Value
            Next z
        End If
    Next i
End Sub

Edit:

As per Bruce's suggestion, you can also replace your whole For z = 9 to lastrow ... Next z loop with one line:

Sheets("Sheet1").Range(Cells(9, i), Cells(lastrow, i)).Value = Sheets("Sheet1").Range(Cells(9, i), Cells(lastrow, i)).Value

edited Jul 31 '19 at 14:55

answered Jul 31 '19 at 14:28

Justyna MK

3,523
3
11
25

1

Clever using `Match()`. You could also change that `For` loop and just have a one line `Range(Cells(7, i), Cells(lastRow, i)).Value = Range(Cells(7, i), Cells(lastRow, i)).Value` – BruceWayne Jul 31 '19 at 14:42
@BruceWayne Nice one! It would be even more efficient then. – Justyna MK Jul 31 '19 at 14:52
Per Bruce's suggestion, I added it in yet am having an Object not defined error. To test out where the error would be, I replaced `Range(Cells(9, i), Cells(lastrow, i))` with `Range("F1:F48")` and it ran without an error. So it seems like defining range with the cells seems to be throwing an issue. Any idea as to why? @BruceWayne @JustynaMK – tokyo_north Jul 31 '19 at 15:32
3

@tokyo_north You'll get that error if `Sheet1` is NOT your currently active sheet. This is because you don't fully qualify your `Cells` references inside the `Range` argument, which makes the `Cells` default to the ActiveSheet. Because `Range` is qualified to `Sheet1` and `Cells` are defaulting to ActiveSheet, if you have a different sheet selected you'll end up with that error. – tigeravatar Jul 31 '19 at 15:35
1

@tigeravatar +1. I didn't have the sheet activated either, which was an issue. But thanks to all who helped. This proves to be very efficient. – tokyo_north Jul 31 '19 at 15:54

score 1 · Answer 2 · edited Jul 31 '19 at 15:12

You could use an array, and check the value of the array.

This is a little longer than @JustynaMK's answer, but I was working on it before they posted their, so figure I'll go ahead and post.

Option Explicit

Sub replace_formulas()
Dim keys() As Variant
Dim keyWords As String

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

keys = Array("Banana", "Apple", "Coconut", "Kiwi", "Watermelon", "Orange")
keyWords = joinArray(keys, "#")

Dim headerRow As Long, startCol As Long
headerRow = 7
startCol = 5

Dim lastCol As Long, lastRow As Long
lastCol = ws.Cells(headerRow, Columns.Count).End(xlToLeft).Column
' I changed this to Column 5 to get the lastRow, but change as needed
lastRow = ws.Cells(Rows.Count, startCol).End(xlUp).Row

Dim headers As Range
Set headers = ws.Range(ws.Cells(headerRow, startCol), ws.Cells(headerRow, lastCol))

Dim cel As Range
With ws
    For Each cel In headers
        If InStr(1, keyWords, "#" & cel.Value & "#", vbTextCompare) Then
            .Range(.Cells(headerRow, cel.Column), .Cells(lastRow, cel.Column)).Value = _
            .Range(.Cells(headerRow, cel.Column), .Cells(lastRow, cel.Column)).Value
        End If
    Next cel
End With

End Sub

Function joinArray(arr As Variant, delim As String) As String
'https://stackoverflow.com/a/11112615/4650297
Dim strg As String
strg = Join(arr, delim)
joinArray = delim & strg
End Function

score 0 · Answer 3 · answered Jul 31 '19 at 15:46

If you store your list as an array on a sheet, then there are a variety of ways of testing whether something is in the list.

A1 = apple A2 = banana A3 = missing B1 = TRUE B2 = TRUE B3 = FALSE

=ISNUMBER(MATCH("apple",A1:A2,0)) returns TRUE

=ISNUMBER(MATCH("orange",A1:A2,0)) returns FALSE

=vlookup("apple",A1:A3,1)="apple" returns TRUE.

=vlookup("orange",A1:A3,1)="orange" returns FALSE.

=not(vlookup("apple",A1:A3,1)="missing") returns TRUE.

=not(vlookup("orange",A1:A3,1)="missing") returns FALSE.

=vlookup("apple",A1:B3,2) returns TRUE.

=vlookup("orange",A1:B3,2) returns FALSE.

Most efficient way to loop thru a column searching for a specific values

3 Answers3