Variable find macro

Tdup

New Member
Joined
Jan 10, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I have two sheets, one with a company name where my team enters into B3 whatever company they are working on that day and another sheet with a pivot table and a list of the companies and information relating to each company. I have tried to create a macro whereby the team can enter the company they are working on, and it returns the information from the pivot table. However, I can only get it to return the information from the original macro I recorded and not update when I change the company in cell B3.

The pivot table does not change, it is what I am searching for in the pivot table that changes ie. cell B3. The "find" function doesn't update when I change cell B3. It still returns "Company X" even though I've changed B3 to "Company Y" for example. I am new to this so any help would be greatly appreciated! Thanks in advance!

Sub PEPs()
'
' PEPs Macro
'

'
Range("B3").Select
Selection.Copy
Sheets("Current and Past PEP").Select
Cells.Find(What:="Company X", After:=ActiveCell, LookIn:= _
xlFormulas2, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Range("B5:E33").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PEP's and Committees").Select
Range("B10").Select
ActiveSheet.Paste
End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi,
You have hard coded CompanyX in the search criteria so regardless of what you enter in your cell, this is the only value your code will search for.

This is untested but see if this update to your code does what you want

VBA Code:
Sub PEPs()
    Dim Search      As String
    Dim rng         As Range
    
    With Sheets("Current And Past PEP")
    
        Search = .Range("B3").Value
        
        Set rng = .Cells.Find(What:=Search, LookIn:=xlFormulas2, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not rng Is Nothing Then
            
            .Range("B5:E33").Copy Sheets("PEP's and Committees").Range("B10")
        Else
            MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
        End If
    End With
End Sub

Dave
 
Upvote 0
Hi,
You have hard coded CompanyX in the search criteria so regardless of what you enter in your cell, this is the only value your code will search for.

This is untested but see if this update to your code does what you want

VBA Code:
Sub PEPs()
    Dim Search      As String
    Dim rng         As Range
   
    With Sheets("Current And Past PEP")
   
        Search = .Range("B3").Value
       
        Set rng = .Cells.Find(What:=Search, LookIn:=xlFormulas2, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not rng Is Nothing Then
           
            .Range("B5:E33").Copy Sheets("PEP's and Committees").Range("B10")
        Else
            MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
        End If
    End With
End Sub

Dave
Thanks for the help Dave, but this still for some reason only returns the original company that was in B3 and hasn't updated when I change it even though it's not hard coded anymore.
 
Upvote 0
Did you use my code as published or just update your own code?

If still having issues - share if possible, copy of your workbook with dummy data on file sharing site like dropbox.

Dave
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top