Excel Macro using Visual Basic - Newbie

[adieaw1983]

Hello all,

I'm a complete newbie to macro's and am hoping there is someone out there generous enough to offer some help, because I'm confused!

Basically I am trying to work through a list with several hundred entries, I need to find the corresponding value in another worksheet in the same workbook. I want to search the second worksheet "Total" using the values in cell "A2" from the original worksheet "Active" (going down the column as I progress). I would then like to copy the results from the same row as the result but in column E and paste that into the "Active" worksheet in cell "F2".

I've been trawling through books and sites and so far I've come up with the below, but this may be way off the mark:

Sub 1()

Worksheets("Active Vs Blank").Activate
Range("A2").Copy
Worksheets("Total Enrolments").Activate
Cells.Find(What:=Paste, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
'Need to move to the column E in the same row
ActiveCell.Copy
ActiveSheet.Paste Destination = Worksheets("Active Vs Blank").Range("F2")
End Sub

Any help would be greatly appreciated, even if it's just the title of books/sites that could help, and of course I will provide more info if needed.

Thanks in advance

Adie

 

[Norie]

Adie

If that is your current code then it is quite off the mark.

For a start it isn't going to compile - you can't name a sub 1.

 

[taleye]

Are you wanting to count the number of times that your entry in A2 in 'Active' appears in a column in the 'Total' worksheet? and then paste that amount into F2 in 'Active'???

 

[adieaw1983]

Sorry the name "1" was part of my attempt to cleanse any sensitive data, I have changed the title to "Search".

As for the result, there should only be 1 cell with the same value as cell "A2" in the "Active" worksheet within the "Total" worksheet.

To try and explain; I am trying to obtain the sales figures for several miscellaneous products which make up the "Active" worksheet from a spreadsheet that covers all of the product sales made, the "Total" worksheet.

After I have searched for and found the value from "A2" in the "Active" worksheet within the "Total" worksheet I want to copy the value from the status column "E" in the "Total" worksheet and align that to the original cell in the "Active" worksheet in cell "F2".