Range, select, and printing arrays
 

I am just trying to learn VBA for Excel and was wondering if somebody might
help me. I am trying to understand how to select a cell (or range), how to
position the curser somewhere in that range (is that the same as
"collapsing"?), and how to print each value in an array.

I have created a small macro that factors all of the numbers in the first
row. Now I'd like to ask the user if they'd like to see all of the prime
numbers (see VBA code below). If so, I would like to display those in a
msgBox, preferably one per line. If the number that was factored was prime,
then "PRIME" is placed in row 5.

1) Do I need to decalre P(j) as an array to use it?
2) Is the Else: P(j) = "" required. I'm putting it in just to set initial
conditions just in case.
3) Is there a more efficient way of finding the value of the cell above the
active cell? I have done an ActiveCell.Offset to select it, but can I find
the value without first selecting the cell and making it active?
4) Can somebody explain the .Range("A1") when I am doing an offset. It
seems that no matter what the active cell is, "A1" is filled in.
5) How do I print all the values in an array? Do I need a For Each loop, or
does VBA have an easier way?

'Display the prime numbers if the user would like
Response = MsgBox(prompt:="Do you want to see the prime numbers?",
Style:=vbYesNo)
If Response = vbYes Then
Range("A5").Select
For j = 1 To 255
If ActiveCell.Value = "Prime" Then
ActiveCell.Offset(1, 0).Range("A1").Select
P(j) = ActiveCell.Value
Else: P(j) = ""
End If

For Each j In P(j)
' make a textbox that prints all the prime numbers, 1 per line


Thanks in advance!!
--
Jeff Ciaccio
Physics and AP Physics Teacher
Sprayberry High School; Marietta, GA
Blog: http://sprayberry.typepad.com/ciaccio

Range, select, and printing arrays
 

Hi Jeff

Thanks for your reply.


ReDim p(1)
Why do you only ReDim the array if the user wants to see the primes? Does
the (1) just tell the compiler that it's 1 dimensional?

The ReDim statement is needed to declare the size of the array before you
can assign any value to it, ie not needed if the user don't want to see the
primes. See the array as a worksheet with 1 row and one column. As the code
ReDim later on the array will have 2 rows and 1 column and so on..


If Cell.Value = "Prime" Then
How do you tell VBA not to be case specific?

If LCase(Cell.Value) = "prime" Then



ReDim Preserve p(UBound(p) + 1)
I'm still not too sure on this. It looks like you are dimensioning the
array and keeping the values currently in the array. Why do you need the
+1? If the index goes from 1 to UBound (since we set this option), wont
UBound(p) do the trick?

UBound(p) is the current size of the last dimension in the array, so +1 is
needed to expand the array.

Preserve is as you think used to keep the values currently in the array.


For c = 1 To UBound(p) - 1
Don't we lose the last value in the array with the - 1 ?

No because the array is expaded after the last number is assigned to the
array. If you remove the -1 you will have a zero value as last item.

Hopes this was explaination enough.

Best regards,
Per