Find a number in a selected range using VBA

rerolfes

New Member
Joined
Oct 18, 2011
Messages
7
Bear with me...I'm new to this thread thing. I think this is a really simple question but it is driving me crazy that I can't figure it out. I have written a macro that switches between 2 worksheets. In the first worksheet I pick up a number and give it a variable name (prjnum). Then I flip to the second worksheet, select a range of numbers, then I want to find the first number (using the variable name) within that selection and insert information in that row.

I cannot come up with the proper syntax for the Find command:

Range(Selection, Selection.End(xlDown)).Select
Selection.Find(What:=prjnum, LookIn:=xlValues).Activate

I've tried the variable name with quote marks around it but it still doesn't work.

Can anyone help? (Just let me know if I'm not posting this thread properly!)
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Welcome to MrExcel!

Please post the entire code, using [code]...Your code here...[/code] to surround the code.
 
Upvote 0
OK, sorry about that! Hope I do this correctly. I've only included a portion of my code here to show how I'm picking up the number, naming it, then looking for it on the other sheet.

Code:
Range("C1").Select
    Do Until ActiveCell.Value = "*"
        If ActiveCell.Value <> "" Then
            prjname = ActiveCell.Value
            Selection.Offset(2, -1).Select
            prjnum = ActiveCell.Value
            Selection.Offset(-1, 4).Select
            prjmgr = ActiveCell.Value
            Selection.Offset(1, 1).Select
            pobal = ActiveCell.Value
            Selection.End(xlToLeft).Select
            Selection.End(xlDown).Select
        End If
'Flip to Workload sheet and look for project #
    Sheets("Work Under Contract").Select
    Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Find(What:=prjnum, LookIn:=xlValues).Activate
'If the project number is found, insert the PO Balance
    If ActiveCell.Value = prjnum Then
        Selection.Offset(0, 4).Select
        ActiveCell.Value = pobal
        Selection.Offset(0, -4).Select
 
Upvote 0

Forum statistics

Threads
1,207,109
Messages
6,076,599
Members
446,215
Latest member
userds5593

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