Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search a column for the last five cells?
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 :-) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search a column for the last five cells?
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) "Daniel" wrote in message om... 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 :-) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search a column for the last five cells?
Hi Daniel,
Daniel wrote: 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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search a column for the last five cells?
"Melanie Breden" wrote in message ...
Hi Daniel, Daniel wrote: 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 Thanks for all yor help, finally i can continue! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search adjacent cells in Column for sequence | Excel Worksheet Functions | |||
LookUp Function with Two Column Search Returning One Column Value | Excel Worksheet Functions | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
search column of text cellto identify those cells with specific w | Excel Worksheet Functions | |||
Fast way to search many cells by column for text strings | Excel Discussion (Misc queries) |