Clever Geek Handbook

Using array instead of ranges in VBA

I've only been using VBA for a couple of weeks, so carry with me.

I am trying to modify a macro to read from an array instead of a range. One of the sections I need to change is using .formulaR1C1 to run the vlookup, but it's hard for them to diligently try to figure out how this can be done with an array.

An example of this is the following line:

.Range("M2:L" & lastrow).FormulaR1C1 = "=VLOOKUP(RC[-1], Sheet2!R1C1:R4C10, 3, 0)"

I am not sure if I can set the value of the array to a formula as I have already done, or if I need to store the value as a String and then edit the cell value later when printing the column in the worksheet.

What I have so far is below:

For i = 2 To lastrow
arr(i, 13).FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R1C1:R4C10,3,0)"
Next

Thanks in advance!

+3


source to share


3 answers


Storing the value as a string is definitely a valid option. Just write the formula in your array, assuming it is of type Variant / String, and when you put the data back in the worksheet, you can use the .FormulaR1C1

Cell (Range) object to apply it as a formula.

arr(i, 13) = "=VLOOKUP(RC[-1],WMSPAM!R1C1:R4C10,3,0)"
...
Range("M2").FormulaR1C1 = Arr(1,13)

I believe this approach is most likely the most effective and most easily supported for you. Since you are learning and seem to be curious about what is possible, here are some more examples of how you might approach this, with some additional explanation.

.FormulaR1C1

is a method of the Range object, so the only way it could be called on an Array element would be if that element was a Range object.

Dim arr(0 To 10) As Range
Set arr(0) = Range("A1")
arr(0).FormulaR1C1 = "=2+2"


Note that since ranges are an object (of a reference type), this operation will directly affect the range specified in the array. In the example above, the formula "= 2 + 2" will be placed in the cell A1

. You can read more about the difference between reference and value types here .

If your array only contains values, another way to achieve what you want is to use an object WorksheetFunction

. With this object, you can access the formula functions that you will use in the worksheet in VBA.

WorksheetFunction.VLookup(Arg1, Arg2, Arg3, Arg4)

As with the written code, the methods WorksheetFunction

take some trial and error to get them to work as you would expect, but in my experience this can be tricky to implement, but there are some cases where they can be very useful !

More about the method VLookup

here .

+2


source


You can try using .Offset()

:



Sub Test()

    lastrow = 11
    With Sheets(1)
        Set Rng = .Range("M2:L" & lastrow)
    End With

    For i = 0 To Rng.Rows.Count - 1
        Rng.Offset(i, 12).FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R1C1:R4C10,3,0)"
    Next

End Sub
+1


source


I'm not sure what you are asking. Your first line of code writes the formula to a two column range in the most efficient way that already exists, your second snippet shows a less efficient way to do it one cell at a time. If the goal is to use vlookup to populate cells and then get rid of the formula, one effective way is to:

.Range("M2:L" & lastrow).FormulaR1C1 = "=VLOOKUP(RC[-1], Sheet2!R1C1:R4C10, 3, 0)"
.Range("M2:L" & lastrow).Value2 = .Range("M2:L" & lastrow).Value2
0


source






More articles:


All Articles