I have the following, which works but it continues all the way down the column and I need it to end at the last row of data in Column A and not keep running...

The next question I have is how would one really write the "index, match" formula in VBA?
I have looked at several examples and not really getting my head around the examples.


Dim rng As Range 'Store Number
Dim rng1 As Range 'Do Not Call LookUp
Dim rng2 As Range 'Do Not Call Insert

Set rng = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
Set rng1 = Range("U2:U" & Range("U" & Rows.Count).End(xlUp).Row)
Set rng2 = Range("D2:D" & Range("D" & Rows.Count).End(xlDown).Row)

With rng2
..Formula = "=index(V:V,MATCH(A2,U:U,0))"
..Value = .Value

End With


End Sub


Thanks Folks!

Hi,

The reason this runs on the entire column is because the macro is looking at every cell down column D. Assuming column D has no data, this would place the last row at the bottom of the sheet. Just change the rng2 argument to this:

Set rng2 = Range("D2:D" & Range("A" & Rows.Count).End(xlUp).Row)

The rest of the macro seemed to work fine on my machine.

Ben

On Tuesday, October 23, 2012 3:13:44 PM UTC-4, Ben McClave wrote:
Hi,



The reason this runs on the entire column is because the macro is looking at every cell down column D. Assuming column D has no data, this would place the last row at the bottom of the sheet. Just change the rng2 argument to this:



Set rng2 = Range("D2:D" & Range("A" & Rows.Count).End(xlUp).Row)



The rest of the macro seemed to work fine on my machine.



Ben

GOT IT!!! I did some of this 8+ yrs ago, but no longer have the workbooks, do not do this often and therefore cannot remember even 1/4 of it. So, "Range("A"...." is the column to find the last "filled" row. A little different than I remembered.
In the past I always worked the code then reverted to formulas, as in this example. So, another question, if I wanted to write the index, match in strict VBA code? I am not getting the Application---Match---Offset.
Ben, I appreciate all of your help!!!

If it's a performance improvement that you are after, then ActiveSheet.UsedRange.Rows.Count is quite handy to prevent a loop from running on to the end of the worksheet. I use Intersect( ActiveSheet.Selection, ActiveSheet.UsedRange ) a lot to limit the scope of my macros that process selected rows.

If you need the more strict check that there has to be something in Column A, then you already have that in Ben's answer.

Phil.