Find Feature with Macro

[Alexander]

Hi

I have struggling with one part of my macro.

Basically, I have a row of cells with names. Alex, John, Martha, Jim, etc.
Each person has a list of data under them. I have a master column of data.
Depending who is on duty today, I want the macro to find the person and then
copy paste special the master column over the person column.

Thus, in the macro code where it says

Find(What:="John",

I
want it to refer to a specific cell. I.e. what:=$E$4.

Then it must go to the cell it found and copy paste the info in.

Cells.Find(What:="John", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate


Any ideas on how to do this?

Regards

Alex

 

[excel.instructor]

Hi

I have struggling with one part of my macro.

Basically, I have a row of cells with names. Alex, John, Martha, Jim, etc..
Each person has a list of data under them. I have a master column of data..
Depending who is on duty today, I want the macro to find the person and then
copy paste special the master column over the person column.

Thus, in the macro code where it says I
want it to refer to a specific cell. I.e. what:=$E$4.

Then it must go to the cell it found and copy paste the info in.

Cells.Find(What:="John", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

Any ideas on how to do this?

Regards

Alex

Alex-
The following code is based on a few assumptions. You'll have to
modify these named ranges to meet your needs. Here is how I set up my
worksheet to test:

The row of cells with names I put in row 1, starting in cell D1. The
master list started in cell I1. All their associated values are in
the cells below each name. The cell where you'll input the person's
name on duty is cell A2.

A B C
D E F
G H I
1 On Duty: Alex
John Martha Jim
Master
2 <input name> 1
2 3
4 2
3
Temp Full Full
Temp Full



You'll need to establish some named ranges. For the values associated
with each name, create a named range for their list of values. For
example, create a named range called "Alex" that refers to cells
D23. Don't include the field header in the named range. Do this
for all names and also create a named range for the Master list
labeled, "Master". It too should NOT contain the field header (e.g.,
I2:I3 only).

In the module folder of the workbook (Alt + F11) place the following
code:

Sub OnDuty()
Dim rng As Range
Set rng = Range("A2")

Range((rng.Value)).ClearContents
Range("Master").Copy Range((rng.Value))
End Sub

How this works: It sets the "rng" variable equal to the value in cell
A2. This is the name of the person on duty, which also corresponds to
one of the named ranges you want to replace with the values in the
Master named range. Now that you've established the appropriate named
range to replace, the next line clears the contents of that named
range, then copies the master named range into the named range that
matches what's in cell A2.

Hope this works.

Excel.Instructor (Ed2go.com/Advanced Excel)

 

[excel.instructor]

Alex-
The following code is based on a few assumptions.   You'll have to
modify these named ranges to meet your needs.  Here is how I set up my
worksheet to test:

The row of cells with names I put in row 1, starting in cell D1.  The
master list started in cell I1.  All their associated values are in
the cells below each name.  The cell where you'll input the person's
name on duty is cell A2.

          A               B               C
D               E                 F
G                 H                I
1  On Duty:                                           Alex
John            Martha             Jim
Master
2  <input name>                                      1
2                  3
4                                   2
3
Temp           Full              Full
Temp                             Full

You'll need to establish some named ranges.  For the values associated
with each name, create a named range for their list of values.  For
example, create a named range called "Alex" that refers to cells
D23.  Don't include the field header in the named range.  Do this
for all names and also create a named range for the Master list
labeled, "Master".  It too should NOT contain the field header (e.g.,
I2:I3 only).

In the module folder of the workbook (Alt + F11) place the following
code:

Sub OnDuty()
    Dim rng As Range
    Set rng = Range("A2")

    Range((rng.Value)).ClearContents
    Range("Master").Copy Range((rng.Value))
End Sub

How this works:  It sets the "rng" variable equal to the value in cell
A2.  This is the name of the person on duty, which also corresponds to
one of the named ranges you want to replace with the values in the
Master named range.  Now that you've established the appropriate named
range to replace, the next line clears the contents of that named
range, then copies the master named range into the named range that
matches what's in cell A2.

Hope this works.

Excel.Instructor (Ed2go.com/Advanced Excel)- Hide quoted text -

- Show quoted text -

Alex-
I failed to include additional code in case you input an on-duty
person's name in the input cell (example used cell A2) and that
person's name doesn't appear in the list or have a named range
associated with it. As the code sits now, it can't handle that
elequently. Use this if there's a chance of this happening:

Sub OnDuty()
Dim rng As Range
Set rng = Range("A2")

On Error GoTo ErrHandler:
Range((rng.Value)).ClearContents
Range("Master").Copy Range((rng.Value))
Exit Sub

ErrHandler:
MsgBox "Name does not Exist", 0, "Name Error"

End Sub

 

[Alexander]

Hi

This is the code I have used for my macro at the moment. I want to try and
get my specific macro to work. As mentioned I am learning about macro code. I
can record, but need to start learning how to edit macros.

It needss to look at the date in E4 and find the date in the row E4:T4. It
must then move one cell down.

Then it must paste special the range E5:G49 into the new place.

This is the code I have but it is giving an error with set source range

Any ideas as to why the error is coming?

regards

Alex
Sub copyPastemaster()

Dim SourceRng As Range
Dim PasteRng As Range
Dim NamesRng As Range, Ccell As Range
Dim Ws As Worksheet
Dim SearchName As String

Set Ws = Worksheets("DWOR")
Set SourceRng = Range(Ws.Range("E5:G49"), Cells(Ws.Rows.Count, "E").End(xlUp))
Set NamesRng = Range(Ws.Range("H4:Y4"), Cells(1,
Ws.Columns.Count).End(xlToLeft).Offset(0, -1)) 'A2 till pre-last cell in Row 1
SearchName = Range("E4").Value
For Each Ccell In NamesRng
If Ccell.Value = SearchName Then Set PasteRng = Ccell.Offset(1, 0)
Next

If Not PasteRng Is Nothing Then
SourceRng.Copy
Ws.Paste Destination:=PasteRng
End If

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False


End Sub