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

Hi Choice,

Try:

Range("B1").Value = Range("A21").End(xlUp).Value


---
Regards,
Norman



"choice" wrote in message
...
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

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"choice" wrote in message
...
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



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.760 / Virus Database: 509 - Release Date: 10/09/2004

One way would be:-

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

"choice" wrote in message
...
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

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))

In article ,
choice wrote:

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

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

"JE McGimpsey" wrote:

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))

In article ,
choice wrote:

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