Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
If I search for a name in Excel, how do I get the Range of the cell once the name is found?
For example, I search for for John Doe and it is located in Range("A22"), how do I assign A22 to a variable? How will I later increment A22 to A23 and so forth? Thanks for any help.
I'm not sure if I understand fully what you want. The following will (I think) assign the cells address to a variable (I'm assuming it is the active cell AND that you are working in VBA)
VariableName = ActiveCell.Address
If you want the cell's value assigned ot the variable:
VariableName = ActiveCell.Value
Incrementing depends on what you are trying to do. The OFFSET command will let you reference cells that C columns and R rows different than a reference cell. If you are saying the name moves to another cell, then you may find it easiest to find the name again and reassign the address or value to the variable.
Hi Peak,
If you are looking for all the instances of "John Doe" in Col A something like this should work. First it counts the number of "John Doe" in col A then loops through the find statement that many times and assigns the cell address to a variable
Sub Macro2()
Dim fngrng As Range
Dim i As Integer
For i = 1 To WorksheetFunction.CountIf(Columns(1), "John Doe")
Cells.Find(What:="George Custer", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
fndrng = ActiveCell.Address
MsgBox ("Found at: " & fndrng)
Next i
End SubHope this helps
.....Ralph
Thanks for the replies guys. Let me try to explain what I'm trying to do a little better. I think I made a few mistakes before.
I have a spread sheet with Column A having names and Columns B-P with values. I want to be able to search for a name, find that cell's address (ex. "A22") and from there use "A22" as a reference cell to jump to "B22", "D22" and so forth. Basically, I will assign the data in "B22" - "P22" a variable name so I can later extract that information and send it over to MS Word in a chart. So for example, someone searches for John Doe, all the information for him will be put in a chart and sent to MS Word.
The reason why I didn't prepare a chart in excel with this info and just paste the chart onto MS Word is because I'm not the person entering the data onto the spread sheet. Someone else handles that from time to time. And yes, I'm working from VBA. I've figured out the search part but still need to assign the address to a variable and be able to reference the cell and jump to another column within the same row. Again, thanks for your help guys.
I think the following macro (contained in the attachment) gives you the leads you need. It copies the data from columns B: P of whatever row the activecell in in to a varaible and then pastes this variable to columns B: P or row one. Select a cell and click the text box to try it.
Sub Macro1()
'
RowNum = ActiveCell.Row
Data = Range("B" & RowNum & ": P" & RowNum).Value
Range("B1: P1") = Data
End Sub
Thanks for all your help guys. I did the following and I think it works.
strName = InputBox("Enter an Name:")
Range("A1:A20").Select
Cells.Find(What:=strName, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
strValue = ActiveCell.Value
Don’t have an account yet? Register yourself now and be a part of our community!