MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBA or array formula?

  Asked By: Meenachi    Date: Oct 19    Category: MS Office    Views: 3587

On Sheet 1 I have a list of expenditures in column C, some of which are paid out
of a grant. In column B there are index numbers which identify from which budget
the expenditure is coming.

I want to list all the expenditure from the grant, and only that expenditure, on
a separate sheet. I can do this by a simple if formula (which even I
understand!): =IF(B1="BH",C1," ")

On my new sheet however I then have big gaps between the entries. I assume it is
possible to write a VBA loop which would copy only the relevant items and make a
neat list. Is this my best bet?

I also wondered whether it would be possible to use an array formula to do this

Any thoughts on either option gratefully received.



4 Answers Found

Answer #1    Answered By: Helga Miller     Answered On: Oct 19

If you only need to do this occasionally I suggest selecting the
column with the big gaps, including the header and in the drop down menu :
Then in the dropdown list  which appears in the header cell choose
'(Non Blanks)'. This will hide all the rows with blanks in. Copy the
column to somewhere else in the sheet, or another sheet, in any event
avoid copying it on top of the hidden rows. Incidentally, it might be
safer to have no space between the quote marks at the end of your formula.

Answer #2    Answered By: Willard Washington     Answered On: Oct 19

I'm actually setting this up for someone else, who is very nervous about
using the computer at all. I'm therefore trying to make it as automated as
possible for her.

Thanks also for the comment about the blank spaces between the quotes: is
there a quick explanation as to why this is better?

Answer #3    Answered By: Emily Brown     Answered On: Oct 19

"is there a quick explanation as to why this is better?"

Note I did say 'might be safer' and that it was an incidental comment.

I just like things to be what they seem. As much as possible if a cell
looks empty I like it to be empty so that I don't have to start
considering 'What if there are invisible characters in there - how
shall I handle them? How many invisible characters are there in that cell?

The procedure I described using '(Non Blanks)' treats cells with just
a space in, in the same way as empty cells. So it doesn't matter there.

It is just a matter of preference.

If I get a moment I will address your chief problem.

Answer #4    Answered By: Jarrod Williams     Answered On: Oct 19

>>If I get a moment I will address your chief problem.

I've now gone with my original messy solution, as I had to get it done, but
thanks anyway for your interest and time.

Didn't find what you were looking for? Find more on VBA or array formula? Or get search suggestion and latest updates.