index match

[phillipus2005]

Hi

I have a table with multiple columns and a form that adds data to each of those columns, as a "case". I also have a separate function that adds data to some of those columns, based on the value in column A as a "case update". How do i get the remaining blank cells in that row to fill with data from the "case"?

As an example, a "case" will include Casename, dateopened, location, and Summary (and many others). A "case update" will add a row with Casename and summary but the dateopened and location will not change.

I would normally do this with an index/match function but need to build this in to the Case_update form code...

Any help very welcome

thanks in advance

Phillipus

 

[phillipus2005]

Thanks. My table has ~50 columns. Do I just replace 4 with the total number then add each blank column no (that I want to fill) to this line:

d.Add a(i, 1), a(i, 3) & Chr(164) & a(i, 4)

Where in my code should this sub go?

If I have multiple rows with the same “case name”, how do I ensure it pulls data from the row with the “new case” in it (rather than from “case update” row, with blanks in it)?

Thanks again. Sorry for the newbie questions (am learning by doing... and asking ?)

Phillipus

 

[phillipus2005]

Would really welcome any advice guys

 

[phillipus2005]

Seems to work with:

.Range(4).Formula = "=INDEX([Primary location],MATCH(1,INDEX(([@Case]=[Case])*(""NEW CASE""=[New case]),0,1),0))"

Thanks for the help guys