Excel formula into all cell array

Question

Im trying to fill in a formula in a lot of cells using a VBA. The workbook I'm working with looks like this:

I'm trying to fill in a formula starting in B3. I first tried to define a range and insert formula, but my problem is that the range is never the same. Some data sets I have more columns and others i have more rows.

Is there a way to make a VBA that defines the range as all columns with content in Row1 and all rows with content in A?

The formula that I'm trying to inset is like this: =INDEX(Sheet1!$N:$N;MATCH(Sheet3!$A:$A&Sheet3!B$1;Sheet1!$R:$R;0))

I hope someone can help me with my problem.

You should look for the concepts of lastrow and last column. There are plenty of posts about that on this site that will give you an answer. — Luuklag, Sep 05 '17 at 14:31
Possible duplicate of [Error in finding last used cell in VBA](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) — Luuklag, Sep 05 '17 at 14:32
Siddharth Rout's find mothod is very good. It is better than usedrange method. — Dy.Lee, Sep 05 '17 at 14:42
You might want to invest some time in understanding Tables (listobjects) and then write your code based on that. It simplifies matters a lot. Here's a nice introduction: https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables — Egalth, Sep 05 '17 at 15:27
@Egalth. Yes,I have done a link to Siddarth's method. The method is perfect. — Dy.Lee, Sep 05 '17 at 22:47

score 0 · Answer 1 · answered Sep 05 '17 at 15:29

you could create a dynamic named range that can be used in VBA

Use the below to define the range

=OFFSET(Sheet1!$B$3,0,0,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$1:$1)-1)

Changing Sheet1 to that of your sheetname

Assuming that there areno blank columns or rowsin your data set etc.

Excel formula into all cell array

1 Answers1