Help from Bernie

[Dan Tschippert]

Hi Bernie,

I pasted your previous help below since it was a while ago and didn't know
if you would see it if I put it into the same thread.

How would I get the number to continue from one column to another. For
example, have the number start in c20 and continue through c30 then go to
e20 and continue through e30.

Thanks in advance for your help, you've been great so far.

Dan

--------------------------------------

Dan,

If your list of numbers is in column A, and your header is in row 1 (with no
blank cells belwo it) then simply:

=INDEX(A:A,COUNTA(A:A),1)

Otherwise, you can use

=INDEX(A:A,COUNTA(A:A) + "Number of blank cells above your list",1)

So, if there is one blank cell above your list:
=INDEX(A:A,COUNTA(A:A) + 1,1)

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Dan,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub DanRandomWrapResults()
Dim myCol As Integer

Dim myCell As Range
For myCol = 3 To 255 Step 2
Set myCell = Cells(65536, myCol).End(xlUp)(2)
If myCell.Row < 20 Then
Set myCell = Cells(20, myCol)
GoTo FoundBlank
End If
If myCell.Row < 31 Then
GoTo FoundBlank
End If
Next myCol

FoundBlank:

myCell.Value = "'" & Format(1000 * Rnd(), "000")
With myCell(1, 2)
.Value = Now()
.NumberFormat = "mmm dd, yyyy hh:mm:ss"
.EntireColumn.AutoFit
End With

End Sub