Any Excel gurus up for helping me out?

[TechnoPro]

I am trying to automate something, and this final step is proving to be very challenging.

Assume that within the range A1:A20, only one cell will have a value. All of the rest will be blank. As for which cell will contain the data, that is variable. I should note that this data will NOT be numbers, but text strings.

I am looking for a formula to put into cell B1 that will automatically copy that one value from range A1:A20.

Any ideas?

Update 1...

Ghetto workaround:

=CONCATENATE(A1,A2,A3...A20)

It works adeqautely well. I was hoping for an elegant answer, but this works.

 

[Kelemvor]

Could do it with a macro... Could even record your own.

Start Recording
Select Cell A1
Press Ctrl End
Copy
Select B1
Paste
Stop Recording

Then just figur eout how you want it to kick off. Could set it to go on WOrksheet.Open or a button or Ctrl combo key or whatever.

 

[dquan97]

or hire a monkey to do it for you

 

[nebula]

Here you go, a macro for you:

Sub Macro1()

Dim i As Integer
Dim TestCell As String

For i = 1 To 20
TestCell = Cells(i, 1)
If TestCell = "" Then
Else
Cells(1, 2) = TestCell
Exit For
End If
Next i
End Sub

If you haven't worked with macros before, record a new macro and stop right away, go to the VB editor and open up the modules. You'll see the one you just recorded, the only thing it will have is the Sub Macro and End Sub headings, replace with my code.

 

[Ilmater]

Originally posted by: nebula
Here you go, a macro for you:

Sub Macro1()

Dim i As Integer
Dim TestCell As String

For i = 1 To 20
TestCell = Cells(i, 1)
If TestCell = "" Then
Else
Cells(1, 2) = TestCell
Exit For
End If
Next i
End Sub

If you haven't worked with macros before, record a new macro and stop right away, go to the VB editor and open up the modules. You'll see the one you just recorded, the only thing it will have is the Macro and Sub headings, replace with my code.

Nice! Much better than concatenate!

 

[nebula]

Thanks, took me five minutes! *pats self on back* I do alot of data manipulation here at work. Acquiring data from a force gauge and sending it to Excel and manipulating.

 

[Kelemvor]

And a wasted loop. The quickie one I did just goes right to it and doesn't check all the empty cells....

You'll save a few milliseconds if you do it my way. ha ha.

Sub Macro()
Range("A1").Select
Selection.End(xlDown).Select
Range("B1").value = ActiveCell.Value
End Sub

 

[nebula]

Originally posted by: FrankyJunior
And a wasted loop. The quickie one I did just goes right to it and doesn't check all the empty cells....

You'll save a few milliseconds if you do it my way. ha ha.

Sub Macro()
Range("A1").Select
Selection.End(xlDown).Select
Range("B1").value = ActiveCell.Value
End Sub

Poo on you!! But your solution came 2 hours later! Besides I love wasting CPU power! kidding I'll remember this trick though.

 

[TechnoPro]

You guys are good, thank you!