I am looking into VBA and I have a Question involving some code I have written.
ID: 3909435 • Letter: I
Question
I am looking into VBA and I have a Question involving some code I have written.
I have date in an excel worksheet such as a persons name in A1..A2..A3 ect. with following information following them such as their date of Birth, height, weight in B1...C1...D1..ect.
My code so far asked for an input to search for a name in the list. My question is how can I have the code search for the persons name and then take their information and move it to another sheet such as sheet2 and then have a pop up displaying all the information.
This is my Code so Far.
Sub Uniform()
Application.ScreenUpdating = False
Dim i As Long
Dim name As String
Dim isfound As Boolean
Dim count As Long
Dim n As Double
Dim WksNew As Excel.Worksheet
Dim SrcRng As Range
Dim myrange As Range
name = InputBox("Who are you looking for?")
isfound = False
count = Range("A1").EntireColumn("A").Rows.count
i = 0
Do Until i = count
i = i + 1
If Range("A1").Offset(i - 1, 0) = name Then
Set myrange = ActiveCell.Offset(0, 1).End(xlToRight)
With ThisWorkbook
If .Worksheets(.Sheets.count).Range(myrange) = "" Then
.Worksheets(.Sheets.count).Range(myrange).Resize(SrcRng.Rows.count, SrcRng.Columns.count).Value = SrcRng.Value
Else
Set WksNew = Worksheets.Add(after:=.Worksheets(.Sheets.count))
n = .Sheets.count
.Worksheets(n).Range("A1").Resize(SrcRng.Rows.count, SrcRng.Columns.count).Value = SrcRng.Value
End If
End With
isfound = True
End If
Loop
If isfound = False Then MsgBox "Person Not Found"
End Sub
Explanation / Answer
Code has been rewritten fully to meet your requirements with comments. Please provide feedback if there are any queries.
Sub Uniform()
Application.ScreenUpdating = False
Dim i As Long
Dim name As String
Dim isfound As Boolean
Dim count As Long
Dim WksNew As Excel.Worksheet
Dim myrange As Range
Dim input
Dim i,j
'The code is generalised for any number of columns since we haven't hardcoded it. Even if you add more details code will work
name = InputBox("Who are you looking for?") 'Input from user
isfound = False
count = Range("A1").EntireColumn("A").Rows.count 'Number of rows
i = 0
input = 1 'Start from row 1
Do Until i = count
i = i + 1
If Range(Cell(i,1)).Value = name Then 'If name is found in ith row
isfound = True 'Name is found
lastcol = ActiveSheet.Range(Cells(i,1)).End(xlToRight).Column 'Finding the last column value in the ith row
Set myrange = ActiveSheet.Range(Cells(i,2),Cells(i,lastcol)) 'range will contain values from secons column till lastcol
'Keeping another variable in sheet2 to keep a tap of where the values are to be copied named input
'Copying values
j = 2
Do Until j = lastcol
Msgbox ActiveSheet.Range(Cells(i,j)).Value 'Refers to different details in all columns like B,C, etcfor name in ith row
Loop
With ThisWorkbook
ActiveSheet.Range(myrange).copy 'Copying the range values in myrange of activesheet
.Worksheets("Sheet2").Range(Cell(input,1)).PasteSpecial 'Paste the values in sheet2 at input row
End With
End If
Loop
If isfound = False Then
MsgBox "Person Not Found"
End Sub
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.