Excel Match Offset formula in VB for dynamic range

[BMD4]

Hi,

I have a Match offset function written in Excel VB.

The maximum number of cells that the formula can go to is 2000.

But, I am looking to have the formula dynamic.

Eg: if I submit one value in C8, I see the result in B8 as per the formula.

But, I see formula from B8 to B2000 cells. I want the formula to appear based on the source cell.

For eg. If I have values in C8,C9 and C10, the formula should be in B8, B9 and B10.

I do not need the formula to come till B2000. Is there a way I can make it dynamic. Below is the code for the range.


Range("B8").Select
Selection.AutoFill Destination:=Range("B8:B2000"), Type:=xlFillDefault
Range("B8:B2000").Select
Range("B2000").Select

Thanks in advance

[protonLeah]

...If [you] have values in C8,C9 and C10, ... Will the remaining cells, C11,... be empty?

[protonLeah]

Please Login or Register  to view this content.

[BMD4]

yes. If C11 is empty, B11 should be empty. If we have value in C11, then B11 should populate Match value based on C11. Column C is a drop down.

[BMD4]

Hi Leah,

Thanks for the code.

When I tried this I do not see formula for B8 as well.

Please let me know if I missed out anything.

[protonLeah]

Well, I tried to create my own test file. Please post one...

[BMD4]

Hi Leah,

Attached the sample excel.

[protonLeah]

Your vba is password protected

[Sintek]

I suggest doing away with formulas...Make use of VBA...Code in Template Sheet Module
Also having an entire column with data validation is just crazy...Populate this column once needed only...

Please Login or Register  to view this content.

[BMD4]

Hi,

The file was extremely helpful. Thanks.

Couple of things I noticed are

1. It is not working when I copy paste multiple values in Col J.
2. When I delete value from Col J, value in Col B does not get deleted.

Attached the file.

Can you please let me know if i am missing anything.

Thanks in advance.

[Sintek]

1. It is not working when I copy paste multiple values in Col J.

As theses were dropdowns, I assumed this would be the only method of entering a value

2. When I delete value from Col J, value in Col B does not get deleted.

This does delete the value in Col B

Please Login or Register  to view this content.

[BMD4]

Thanks. I tried the code. However, I do not see values populating in column B. Attached the excel.

Please let me know if I missed out anything.

[Sintek]

All works as it should...
If I select in Col J...B is populated...
If I paste into Col J...All B is populated...
If I clear Col J...B is cleared...

Not sure what process you are doing...