Complex Macro to fill in employees wages

[tonywatsonhelp]

Hi Everyone,

I'll try make this as clear as possible I realise its quite complex but hopefully someone can help.

Ok so I have a sheet called "Employees Details"
Column F are the Names
Column G are salary's

Now I have a sheet called Staff

In this sheet i work out their wages each month,
what i need is a macro that can look up each employees rate in "Employees Details" column G by matching their name (All names are unique)

The thing is the layout is not even so we are going to need to search for "Hourly Rate" as a header down column "C"

So if was doing this in english heres what i'd put

For each "Hourly Rate" in Column "C" look at name in cell above,

then goto "Employees Details" and find that name in column F, take the value in same row column G and put it next to the "Hourly Rate" in column D

I hope that makes sense?

please help if you can
thanks
Tony

 

[Tupe77]

Could you put the structure of the Sheets for us, especially the structure of the Staff Sheet left a little unclear to me.

XL2BB or even a screenshot would help.

 

[tonywatsonhelp]

Staff Sheet
A	B	C	D	E
		Headers	This is the row i want filled
		Tony
		Hourly Rate	Find This
		Age
		other data posssably blank cells
		Jim
		Hourly Rate	Find this etc!
			So Column C has lots of headers, there is no sequence,	I want to fill in everyones Hourly Rate in column D	So i need a macro to look down column C for the words "Hourly Rate"	Goto "Employees Details" sheet,	Find the name above Hourly Rate, in column F	Take the rate in G and put it into D

Employee Details
D	E	F	G	H
		Name	Hourly Rate
		Jim	11
		Sue	21
		Harry	11
		Tony	25
		mick	15
		Jim	21

 

[Fluff]

How about

Sub tonywatson()
   Dim Cl As Range
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Employees Details")
      For Each Cl In .Range("F2", .Range("F" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Cl.Offset(, 1).Value
      Next Cl
   End With
   With Sheets("Staff")
      With .Range("C2", .Range("C" & Rows.Count).End(xlUp))
         .Replace "Hourly Rate", "=xxxHourly Rate", xlWhole, , False, , False, False
         For Each Cl In .SpecialCells(xlFormulas, xlErrors)
            Cl.Offset(, 1).Value = Dic(Cl.Offset(-1).Value)
         Next Cl
         .Replace "=xxxHourly Rate", "Hourly Rate", xlWhole, , False, , False, False
      End With
   End With
End Sub

 

[tonywatsonhelp]

Wow, thank you Fluff,
Saved me again
Tony

 

[Fluff]

You're welcome & thanks for the feedback.