Is it Possible to have a dynamic function in an array written in visual basic?

[Carrie_Loos via OfficeKB.com]

Is there a way to write the vlookup formula in visual basic while using an
array to be relative as if you were copying the formula from cell to cell on
a worksheet?

Sub Fill_In_Data()
Dim TestArrayData As Range

Application.ScreenUpdating = False

Set TestArrayData = Range("E3:AU400")

Sheets("Dates").Select

MyFormula = "=IF(ISNA(VLOOKUP(RC[-4]&"" ""&R[-2]C,Classes!C[-4]:C[24],29,
FALSE)),0,(VLOOKUP(RC[-4]&"" ""&R[-2]C,Classes!C[-4]:C[24],29,FALSE)))"
TestArrayData.FormulaArray = MyFormula

Application.ScreenUpdating = True


End Sub

 

[Dave Peterson]

I don't see the purpose of the array formula.

I'd try:
TestArrayData.FormulaR1C1 = MyFormula

Are you sure you meant e3:au400?

Is there a way to write the vlookup formula in visual basic while using an
array to be relative as if you were copying the formula from cell to cell on
a worksheet?

Sub Fill_In_Data()
Dim TestArrayData As Range

Application.ScreenUpdating = False

Set TestArrayData = Range("E3:AU400")

Sheets("Dates").Select

MyFormula = "=IF(ISNA(VLOOKUP(RC[-4]&"" ""&R[-2]C,Classes!C[-4]:C[24],29,
FALSE)),0,(VLOOKUP(RC[-4]&"" ""&R[-2]C,Classes!C[-4]:C[24],29,FALSE)))"
TestArrayData.FormulaArray = MyFormula

Application.ScreenUpdating = True

End Sub

 

[Carrie_Loos via OfficeKB.com]

Dave -

Long story but in a nutshell, I am stuck doing this calendar project in Excel
whereas a much better choice would have been a database. So, I have this data
sheet that reviews a list of classes and fills in start and end dates (via
the vlookup formula) and then converts it to a landscape timeline.
Bottom line this vlookup formula for a very large area of the worksheet is
monsterously slow. I was following some advice from another to try it in an
array formula to improve the time. I had never written an array formula in vb
before so I wasn't sure if it was possible.
And yes row 3 - 400 is correct and it is only a fraction of what I need to do.
I am hoping to get the formula to work, then change the array to values and
then loop to a new array from 400 - 800 ect...

I don't see the purpose of the array formula.

I'd try:
TestArrayData.FormulaR1C1 = MyFormula

Are you sure you meant e3:au400?

Is there a way to write the vlookup formula in visual basic while using an
array to be relative as if you were copying the formula from cell to cell on

[quoted text clipped - 19 lines]

 

[gimme_this_gimme_that]

Try Application.Match.

Store "abc" in Cell A3.

Then try:

Sub f()
Dim book As Workbook
Dim range As range
Dim sheet As Worksheet
Dim PSKeyRng As Variant
Set book = ThisWorkbook
Set sheet = book.Sheets("Sheet1")
Set range = sheet.range(sheet.Cells(1, 1), sheet.Cells(10, 1))
Set PSKeyRng = sheet.range("A1:A7")
res = Application.Match("abc", PSKeyRng)
MsgBox res
End Sub

This displays three.

 

[JMB]

If possible, you might consider sorting your lookup table in ascending order
and using approximate match lookups nested in an IF statement to get an exact
match.

=IF(VLOOKUP(criteria, table, 1, TRUE)=criteria, VLOOKUP(criteria, table,
col_num,TRUE), 0)

you could get #N/A if your criteria is less than the first item in the
table, so you could add a test for this if it is a concern
=IF(criteria<first_item_in_lookup_table, 0, IF(VLOOKUP(criteria, table, 1,
TRUE)=criteria, VLOOKUP(criteria, table, col_num,TRUE), 0))

this approach is much faster than exact match lookups, but it depends on the
lookup table being sorted.

Dave -

Long story but in a nutshell, I am stuck doing this calendar project in Excel
whereas a much better choice would have been a database. So, I have this data
sheet that reviews a list of classes and fills in start and end dates (via
the vlookup formula) and then converts it to a landscape timeline.
Bottom line this vlookup formula for a very large area of the worksheet is
monsterously slow. I was following some advice from another to try it in an
array formula to improve the time. I had never written an array formula in vb
before so I wasn't sure if it was possible.
And yes row 3 - 400 is correct and it is only a fraction of what I need to do.
I am hoping to get the formula to work, then change the array to values and
then loop to a new array from 400 - 800 ect...

I don't see the purpose of the array formula.

I'd try:
TestArrayData.FormulaR1C1 = MyFormula

Are you sure you meant e3:au400?

Is there a way to write the vlookup formula in visual basic while using an
array to be relative as if you were copying the formula from cell to cell on

[quoted text clipped - 19 lines]

 

[Dave Peterson]

It's possible to enter an array formula via code. I just don't see the purpose
for your formula. It doesn't look like an array formula to me.

In fact, I'd bet that the references aren't correct. It kind of looks like your
data is laid out in a table with headers across the top and down the left hand
side.

MyFormula = "=IF(ISNA(VLOOKUP(RC1&"" ""&R1C,Classes!C1:C29,29,FALSE)),0," _
& "(VLOOKUP(RC1&"" ""&R1C,Classes!C1:C29,29,FALSE)))"

(untested--watch out for typos!)

But with 43 columns * 400 (or more) rows, don't expect this kind of thing to
calculate quickly.

When I've had to do this kind of stuff, I'd cycle through fewer rows and build
up to all the rows I needed.

In fact, if you're going to convert the formulas to values, I'd drop the =if()
and just use a single =vlookup(). It may speed things up a bit.

Then convert to values and finally, convert #n/a's to 0's with a simple
edit|Replace command.

Dave -

Long story but in a nutshell, I am stuck doing this calendar project in Excel
whereas a much better choice would have been a database. So, I have this data
sheet that reviews a list of classes and fills in start and end dates (via
the vlookup formula) and then converts it to a landscape timeline.
Bottom line this vlookup formula for a very large area of the worksheet is
monsterously slow. I was following some advice from another to try it in an
array formula to improve the time. I had never written an array formula in vb
before so I wasn't sure if it was possible.
And yes row 3 - 400 is correct and it is only a fraction of what I need to do.
I am hoping to get the formula to work, then change the array to values and
then loop to a new array from 400 - 800 ect...

I don't see the purpose of the array formula.

I'd try:
TestArrayData.FormulaR1C1 = MyFormula

Are you sure you meant e3:au400?

Is there a way to write the vlookup formula in visual basic while using an
array to be relative as if you were copying the formula from cell to cell on

[quoted text clipped - 19 lines]

 

[Carrie_Loos via OfficeKB.com]

Thanks Dave- It's funny that you say these things because that is exactly how
I had it built (looping piece by piece) but was still disappointed with the
performance. And it really isn't an "array" formula it was just someones
suggestion to try and put it in an array to help speed things up.

So I have been asking advice and trying various things but looks like I have
the best approach with the original looping formula. Which means I may need
to completely rethink my approach and try something else just not sure what
yet.

It's possible to enter an array formula via code. I just don't see the purpose
for your formula. It doesn't look like an array formula to me.

In fact, I'd bet that the references aren't correct. It kind of looks like your
data is laid out in a table with headers across the top and down the left hand
side.

MyFormula = "=IF(ISNA(VLOOKUP(RC1&"" ""&R1C,Classes!C1:C29,29,FALSE)),0," _
& "(VLOOKUP(RC1&"" ""&R1C,Classes!C1:C29,29,FALSE)))"

(untested--watch out for typos!)

But with 43 columns * 400 (or more) rows, don't expect this kind of thing to
calculate quickly.

When I've had to do this kind of stuff, I'd cycle through fewer rows and build
up to all the rows I needed.

In fact, if you're going to convert the formulas to values, I'd drop the =if()
and just use a single =vlookup(). It may speed things up a bit.

Then convert to values and finally, convert #n/a's to 0's with a simple
edit|Replace command.

Dave -

[quoted text clipped - 25 lines]