Search a column for the last five cells?

[Daniel]

Hi all,

i have a small problem and it follows:

I need to search 1 column ( say A ) and find the last cell. From there
i want to step five cells back, copy every value for each on and paste
them into a specific textbox i have in a userform.

My code so far look like this:

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
Dim nr1 As Integer, nr2 As Integer, nr3 As Integer, n4 As Integer, nr5
As Integer
''''This is the values i want to put in my textboxes!!!
nr1 = ActiveCell.Offset(-1, 0).Select And ActiveCell.Copy
nr2 = ActiveCell.Offset(-1, 0).Select And ActiveCell.Copy
nr3 = ActiveCell.Offset(-1, 0).Select And ActiveCell.Copy
nr4 = ActiveCell.Offset(-1, 0).Select And ActiveCell.Copy
nr5 = ActiveCell.Offset(-1, 0).Select And ActiveCell.Copy
End Sub

PS Im a beginner, started last week so please try to keep it simple

 

[Bob Phillips]

iCol = Activecell.Column
iLastRow = Cells(Rows.Count, iCol).End(xlUp).Row
Dim nr1 As Integer, nr2 As Integer, nr3 As Integer, n4 As Integer, nr5
As Integer
nr1 = Cells(iLastRow,iCol).Value
nr2 = Cells(iLastRow-1,iCol).Value
nr3 = Cells(iLastRow-2,iCol).Value
nr4 = Cells(iLastRow-3,iCol).Value
nr5 = Cells(iLastRow-4,iCol).Value

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Melanie Breden]

Hi Daniel,

I need to search 1 column ( say A ) and find the last cell. From there
i want to step five cells back, copy every value for each on and paste
them into a specific textbox i have in a userform.

if I understand you correctly, are the 5 last values of the column A to be
transferred into *one* textbox?
Place the MultiSelect-characteristic of the textbox on True,
in order to arrange the values one below the other:

Private Sub CommandButton1_Click()
Dim lngRow As Long
Dim rngArea As Range
Dim intI As Integer

lngRow = Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row - 4
Set rngArea = Worksheets(1).Cells(lngRow, "A").Resize(5, 1)

For intI = 1 To rngArea.Cells.Count
TextBox1.Text = TextBox1.Text & IIf(TextBox1.Text = "", "", vbCr) _
& rngArea.Cells(intI)
Next intI
End Sub

--
Mit freundlichen Grüssen

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[Daniel]

Hi Daniel,


if I understand you correctly, are the 5 last values of the column A to be
transferred into *one* textbox?
Place the MultiSelect-characteristic of the textbox on True,
in order to arrange the values one below the other:

Private Sub CommandButton1_Click()
Dim lngRow As Long
Dim rngArea As Range
Dim intI As Integer

lngRow = Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row - 4
Set rngArea = Worksheets(1).Cells(lngRow, "A").Resize(5, 1)

For intI = 1 To rngArea.Cells.Count
TextBox1.Text = TextBox1.Text & IIf(TextBox1.Text = "", "", vbCr) _
& rngArea.Cells(intI)
Next intI
End Sub

Thanks for all yor help, finally i can continue!