Previous Topic Next Topic

VBA Display In Selected Cell

Closed
azmiismail Posts 17 Registration date Thursday March 3, 2011 Status Member Last seen July 20, 2011 - Mar 17, 2011 at 07:00 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 17, 2011 at 02:24 PM
Hello,

Can somebody help me out with this objective.I would like to have the Staff Number which I'd search and found base on the code below to have the person name display in say cell "H2"(of the same worksheet).I'got over 100 names with the staff numbers stated immediately under the respective names in my list under the same column.I'm still very much new with macro and VBA code.Please excuse me.
Sample,
James
13456

run code to search for 13456 and James display in cell "H2".

Sub Find_Val()
Dim FindString As String
Dim Rng As Range
FindString = InputBox("Name or Staff Number")
If Trim(FindString) <> "" Then
With Sheets("MAC 11").Range("A:CZ")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then

Application.Goto Rng, True
Else
MsgBox "Not listed"
End If
End With
End If

End Sub

Thanks
Related:

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 17, 2011 at 08:42 AM
could you please re-explain your issue with some exaple

Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc
AND post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too.


Note: your data need not be to be real data but a good representative of how data looks like
azmiismail Posts 17 Registration date Thursday March 3, 2011 Status Member Last seen July 20, 2011
Mar 17, 2011 at 10:24 AM
http://wikisend.com/download/390140/STAFF%20INFO..xlsm
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 17, 2011 at 10:53 AM
Since you say you are new to macro, here is your macro with comments to allow you to better understand what the code is doing (in case you have some confusion). I have mofied the code when a match is found to display the value in C19 ( as per your sample book) 


Sub Find_Info()

   
   Dim FindString          As String 'declaring variable FindString of type string
   Dim Rng                 As Range  'declaring variable Rng of type range
   
   ' display the input box to ask the staff number
   FindString = InputBox("Name or Staff Number")
   
   'check to see if non-white space character were entered
   If Trim(FindString) <> "" _
   Then
      ' on sheet MAC 11, within range defined by column A through CZ,
      With Sheets("MAC 11").Range("A:CZ")
      
         'locate the cell that constains the sting that is being search
         Set Rng = .Find(What:=FindString, _
                           After:=.Cells(.Cells.Count), _
                           LookIn:=xlValues, _
                           LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, _
                           MatchCase:=False)
                           
         
         If Not Rng Is Nothing _
         Then
            ' if a cell was found, then goto the cell that was located
            'Application.Goto Rng, True
            
            ' on MAC 11, at cell B19, put the value that is found in same coulmn but one row above
            ' OFFSET(offset by how many rows, offset by how many colums) is format
            Sheets("MAC 11").Cells(19, "B") = Rng.Offset(-1, 0)
            
         Else
            'if no cell was found
            MsgBox "Not listed"
         End If
      End With
   End If

End Sub
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Mar 17, 2011 at 01:33 PM
Nice Work....... Awesome..........
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Mar 17, 2011 at 01:35 PM
But In This Case If We Type The Name In Input Box Then It Shows The -1 Row Means It Will Show The Number Of Above Employee..... But Still Gr8 Work Sir.........
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 17, 2011 at 02:24 PM
ah in that case, Provided that name always appear on even row and id always on odd rows (or other way round), then you do the search as per code. When the match is found, then you check the row is even or odd

Rng.Row will give you the row number where the match occured. Now you have to see if it is an odd number or a even number and then act according.

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
If cell A1 has text then cell B2 has today's Date
Sasha -
ac3mark -

1 response
VBA Macro Matrix Multiplication with a scalar
VBAConfused -
MANMANI -

6 responses
How to add Rows automatically based on Cell Value In Excel?
Raj_2823 -
TrowaD -

7 responses