Simple arrays with If statements

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

In many ways an alternative to my prior post about ranges with spaces, I'm
hoping this is easier.

I want to take a number of If statements, all of which return a text string,
"Sample" for example, if true or nothing if not and have them generate a
list.

I then want to put that list in order into cells on the spreadsheet. So in
layman's language (I know it's not VBA...)

If A1 = 1 Then
Add "Sample" to list

If A2 = 5 Then
Add "Example" to list

Place the list in Cells B1:B2 in the order they appear above.

It's so simple when written like that but the adding to list and placing in
spreadsheet components defeat me.

Advice welcome.

Andy
 
One way:

Manually:

In an empty column (say D), leave D1 blank and enter

D2: =ISTEXT(A2)

Choose Data/Filter/Advanced Filter. Select the Copy to new location
radio button. Check the Unique checkbox. Enter $A:$A in the source
range, $D$1:$D$2 in the criteria range, and $B$1 in the destination
range. Click OK.

To do it in a macro:

Public Sub CopyText()
With Range("D2")
.FormulaR1C1 = "=ISTEXT(rc1)"
Range("A:A").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=.offset(-1,0).resize(2,1), _
CopyToRange:=Range("B1"), _
Unique:=True
.ClearContents
End With
End Sub

Change D2 to suit.
 
Oops - I must have confused two different problems (one I'm working
on privately), since you didn't specify unique values and you said
the formula returns nothing, which I assume means a null string.

In the manual solution below, change the formula to

=AND(ISTEXT(A2), LEN(A2)>0)

and don't check the unique checkbox.

In the macro, use:

Public Sub CopyText()
Application.ScreenUpdating = False
With Range("D2")
.FormulaR1C1 = "=AND(ISTEXT(rc1),LEN(rc1)>0)"
Range("A:A").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=.Offset(-1, 0).Resize(2, 1), _
CopyToRange:=Range("B1"), _
Unique:=False
.ClearContents
End With
End Sub
 
Thanks, I have to copy the data in Columns A, B, C etc to a single column to
make the array work and then use If statements to put in "" instead of 0 to
make the noblanks work, but once I've done that it works great. It's a
little messy but I'm a lot further ahead than I was!

I'm still looking for an elegant VB solution though that allows me to do the
calculations in VB and requires no cells at all except those for the final
data. I know it's hiding out there somewhere...

Cheers,

Andy
 
Back
Top