loop i think

C

choice

i have a list (a1:a20) where one cell in that row will have a value...all the
others will be blank. i need to have whatever cell has a value, to be
inputted into B1.
im thinking that i need a loop function, but im not too familiar with it

Thank you
 
K

Ken Wright

Do you really need code? If it is exactly as you say and numeric then in B1
simply put

=MAX(A1:A20)

or even

=LOOKUP(9.99999999999999E+307,A1:A20) for last numeric entry in a column


If it is text then

=LOOKUP(REPT("z",255),A1:A20) for last text entry in a column
 
B

bigwheel

One way would be:-

For c = 1 To 20
If Cells(c, 1) > "" Then
Cells(1, 2) = Cells(c, 1)
End If
Next c
 
J

JE McGimpsey

If the value is numeric, use

B1: =SUM(A1:A20)

If the value is text, array-enter (CTRL-SHIFT-ENTER or CMD-RETURN):


B1: =INDEX(A:A,MATCH(TRUE,(A1:A20<>""),FALSE))
 
C

choice

i cant use an equation, because at other times i want to type text into the
same cell...so i need VBA to enter in the value, then print then delete
 
Top