VBA lookups within my code

[ValuationMan]

I have a macro that loops through Column A to remove outliers in the range. Using the offset function, the loop is used for Column B, Column C, etc. The basic structure of the code is below.

dim i as integer
i=1

dim ColumnOffset as integer

'the value of ColumnOffset changes based on the sheet
If ActiveSheet.name="Sheet1" then ColumnOffset=3
If ActiveSheet.name="Sheet2" then ColumnOffset=4
If ActiveSheet.name="Sheet3" then ColumnOffset=2

Do While i <= ColumnOffset[INDENT]'additional code that uses ColumnOffset to work on Column A then B then C then ...[/INDENT]
i=i+1

loop

The loops work as intended, but I'm wondering if the If ActiveSheet.name code could be more efficient. In a workbook, I would use Index Match or Vlookup to look up a reference table. I'm curious if anything like exists for use in the backend instead of using all of those If statements.

 

[offthelip]

You haven't given much detail about what you are doing but it sounds as though you are looping through the worksheet which is a very inefficient way of using VBA. It is usually much better to use variant arrays and load the entire worksheet into memory in one go. You mention that you would do use index match or vlookup, if that is what you are doing then variant arrays in VBa is usually about 500 to 1000 times faster, see this thread for an idea of how to do it.
https://www.mrexcel.com/forum/excel-questions/1043185-vlookup-vba-alternative.html

 

[ValuationMan]

Thanks. That definitely speeds up my code. Basically Column A has financial data. The macro removes all data above an upper control limit (two standard deviations above the mean). Column B, C, D, etc has similar data. The number of columns with data is dependent on the sheet. The macro add a comment with the value of the outlier cell and then replaces the value in the cell with "NM". The code works as is, but I am working on improving my VBA knowledge by making it more efficient.

Dim lastrow, r, c As Long
Dim inarr, searchfor As Variant
lastrow = 513
inarr = Range(Cells(1, 1), Cells(lastrow, 6))

For c = 1 To 6

'maxvalue is a named range with the current max in the range
'UCL (upper control limit) is the highest value allowed in the data
Do While Range("maxvalue").Offset(0, c - 1).Value > Range("UCL").Offset(0, c - 1).Value
searchfor = Cells(521, c)


For r = 1 To lastrow
    If inarr(r, c) = searchfor Then
        Cells(r, c).AddComment Cells(r, c).Text
        Cells(r, c) = "NM"
        Exit For
    End If
    
Next r

'refreshes variant (duplicate numbers were causing issues)
inarr = Range(Cells(1, 1), Cells(lastrow, 6))
Loop

Next c