Hello Experts,
Could you help with this question. How do you write out the following
command within code? (This code will be embedded in the current
worksheet)

1) Open C:\Extract names.xls
2) Copy A1 (Name, ie. Joe), B1 (Beginning Date ie. Dec.1,2003), C1
(Ending Date, ie. Dec.5,2003), D1 (Number, ie. 105).
3) In the current worksheet, use the (Number) to match row A's header.
In this example, the (Number) of 105 is located in F1.
4) Once in column [F], go down to the corresponding beginning date and
paste {Name}s throughout the entire range including the ending date. In
this example, the beginning date of Dec.1,2003 is in cell [A10] and
ending date of Dec.5,2003 is in cell [A14]. So there should be Joe in
every cell from [A10] to [A14] inclusive.
5) When pasting the names, keep all of the current worksheet's existing
formats such as cell color backgrounds. Paste only the name.
6) Now within the (Names), go down to second cell and color fill cell
background as Yellow. Should be yellow in cell [A11].
7) Now from A11, count every 3rd cell and color fill background as
Green. Should be cell [A15].
8) Go to last cell within the {Name} range. In this example, it will be
cell [A15] again. Color fill background as Red, no matter what color
was there before.
9) Delete C:\Extract names.xls

Your help is greatly appreciate.

Thanks in advance,
Ricky

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Ricky,

The best way to get started with a problem like this is to turn on the
macro recorder (Tools/Macro/Record New Macro from the Excel menu) and then
perform the steps below manually. This will create a skeleton of the code
required to do what you want to do. It won't be very well written or
flexible, but it's great to get you started. Once you have this skeleton
code recorded you can start cleaning it up and making it more general. Post
back with specific questions related to this process.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Ricky Pang" wrote in message
...
Hello Experts,
Could you help with this question. How do you write out the following
command within code? (This code will be embedded in the current
worksheet)

1) Open C:\Extract names.xls
2) Copy A1 (Name, ie. Joe), B1 (Beginning Date ie. Dec.1,2003), C1
(Ending Date, ie. Dec.5,2003), D1 (Number, ie. 105).
3) In the current worksheet, use the (Number) to match row A's header.
In this example, the (Number) of 105 is located in F1.
4) Once in column [F], go down to the corresponding beginning date and
paste {Name}s throughout the entire range including the ending date. In
this example, the beginning date of Dec.1,2003 is in cell [A10] and
ending date of Dec.5,2003 is in cell [A14]. So there should be Joe in
every cell from [A10] to [A14] inclusive.
5) When pasting the names, keep all of the current worksheet's existing
formats such as cell color backgrounds. Paste only the name.
6) Now within the (Names), go down to second cell and color fill cell
background as Yellow. Should be yellow in cell [A11].
7) Now from A11, count every 3rd cell and color fill background as
Green. Should be cell [A15].
8) Go to last cell within the {Name} range. In this example, it will be
cell [A15] again. Color fill background as Red, no matter what color
was there before.
9) Delete C:\Extract names.xls

Your help is greatly appreciate.

Thanks in advance,
Ricky

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Rob,
My specific question how do you lookup the number to match according to
the copied (Name) cell? And, how do you count the cell, ie. go to the
2nd cell within the pasted range? And, how do you count down the column
and go to every 3rd cell? And finally, how do you go right to the last
cell within the pasted within? So basically, points #4 through #8. The
macro recorder doesn't record these steps. That's where I need help with
writing the codes.

Thanks again,
Ricky


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Ricky,

Can you post the macro recorded code? Although it's not perfect, it does
give a much clearer indication of what you want to do. I know it's clear to
you, but it's much easier for someone like me, who's looking in from the
outside, to help you if you have something to start with rather than trying
to write an entire subroutine based on a verbal description. That's the
point I was getting at.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Ricky Pang" wrote in message
...
Rob,
My specific question how do you lookup the number to match according to
the copied (Name) cell? And, how do you count the cell, ie. go to the
2nd cell within the pasted range? And, how do you count down the column
and go to every 3rd cell? And finally, how do you go right to the last
cell within the pasted within? So basically, points #4 through #8. The
macro recorder doesn't record these steps. That's where I need help with
writing the codes.

Thanks again,
Ricky


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Rob,
Thanks for your response. Here's my macro, albeit rough as it is, I
wasn't not able to do a (Number) lookup; thus, I had to manually goto
the proper cell ranges to paste my (Names).

Workbooks.Open Filename:= _
"C:\Extract Names.xls
Range("A1").Select
'A1 is just the Names. I should have also copied A2 beginning date, A3
ending date, A4 Number, but here's where I to specify what each of these
cells represent ie. perhaps dim A1 as ....
Selection.Copy
Windows("Current Worksheet.xls").Activate
'Instead of manually goto this range, here's where a lookup function to
search the copied A4 Number to match current worksheet's Row A header.
'Also need to match the beginning and ending dates listed on current
worksheet's column A
Range("F10:F14").Select
'before pasting the Names, check if there are data that's already there.
Popup warning and ask "Do you want to overwrite?". If yes, continue
with next line. If not, then end macro here.
'the Names are now pasted
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Need to goto 2nd cell and color as yellow
Range("F11").Select
Selection.Interior.ColorIndex = 6
'Then, goto every 3rd cell and color as green
Range("F14").Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
'Goto the last cell and color as red
Selection.Interior.ColorIndex = 3
End


I've been searching throughout the newsgroups for something similar but
of no avail. Hopefully you could offer some guidance.

Thanks again,
Ricky

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Ricky,

This is a pretty complex macro you're asking for. I've done my best to
write one that does what you want. It works here on some dummy data I've
created, but I'm sure you'll need to alter it to suit your needs. I'm off on
vacation very shortly, so I apologize if I'm not here to answer follow-up
questions for a few days. Maybe someone else can jump in and help you out in
my absence.

Sub Demo()

Dim dteStart As Date
Dim dteEnd As Date
Dim lNumber As Long
Dim szName As String
Dim szBookName As String
Dim lColumn As Long
Dim lRowStart As Long
Dim lRowEnd As Long
Dim lIndex As Long
Dim rngCell As Range
Dim rngLookup As Range
Dim rngDest As Range
Dim rngTemp As Range
Dim wksDest As Worksheet
Dim wkbSource As Workbook

''' Open the source workbook and get the data.
Set wkbSource = Workbooks.Open("E:\Extract Names.xls")
With wkbSource.Worksheets(1)
szName = .Range("A1").Value
dteStart = .Range("A2").Value
dteEnd = .Range("A3").Value
lNumber = .Range("A4").Value
End With

''' Close and delete the source workbook.
szBookName = wkbSource.FullName
wkbSource.Close False
Kill szBookName

''' Substitute your worksheet name here.
Set wksDest = ThisWorkbook.Worksheets("Sheet1")

''' Find the column with "Number" in row 1.
Set rngLookup = wksDest.UsedRange.Resize(1)
lColumn = Application.Match(lNumber, rngLookup, False)

''' Find the rows with the start and end dates in column A.
''' We can't use Application.Match here because it is not
''' reliable with date values.
Set rngLookup = wksDest.UsedRange.Resize(, 1)
For Each rngCell In rngLookup
If DateDiff("d", rngCell.Value, dteStart) = 0 Then _
lRowStart = rngCell.Row
If DateDiff("d", rngCell.Value, dteEnd) = 0 Then _
lRowEnd = rngCell.Row
Next rngCell

''' Construct the destination range
Set rngDest = wksDest.Cells(lRowStart, _
lColumn).Resize(lRowEnd - lRowStart + 1)

''' If there is already data in the range, warn the user.
If Application.CountA(rngDest) 0 Then
If MsgBox("Data exists, continue?", _
vbYesNo) = vbNo Then Exit Sub
End If

''' Add the name to the destination range
rngDest.Value = szName

''' Color the destination range.
rngDest.Interior.ColorIndex = xlNone
rngDest.Cells(2, 1).Interior.ColorIndex = 6
Set rngTemp = wksDest.Range(rngDest.Cells(3, 1), _
rngDest.Cells(rngDest.Rows.Count, 1))
lIndex = 1
For Each rngCell In rngTemp
If lIndex Mod 3 = 0 Then rngCell.Interior.ColorIndex = 4
lIndex = lIndex + 1
Next rngCell
rngDest.Cells(rngDest.Rows.Count, 1).Interior.ColorIndex = 3

End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Ricky Pang" wrote in message
...
Hi Rob,
Thanks for your response. Here's my macro, albeit rough as it is, I
wasn't not able to do a (Number) lookup; thus, I had to manually goto
the proper cell ranges to paste my (Names).

Workbooks.Open Filename:= _
"C:\Extract Names.xls
Range("A1").Select
'A1 is just the Names. I should have also copied A2 beginning date, A3
ending date, A4 Number, but here's where I to specify what each of these
cells represent ie. perhaps dim A1 as ....
Selection.Copy
Windows("Current Worksheet.xls").Activate
'Instead of manually goto this range, here's where a lookup function to
search the copied A4 Number to match current worksheet's Row A header.
'Also need to match the beginning and ending dates listed on current
worksheet's column A
Range("F10:F14").Select
'before pasting the Names, check if there are data that's already there.
Popup warning and ask "Do you want to overwrite?". If yes, continue
with next line. If not, then end macro here.
'the Names are now pasted
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Need to goto 2nd cell and color as yellow
Range("F11").Select
Selection.Interior.ColorIndex = 6
'Then, goto every 3rd cell and color as green
Range("F14").Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
'Goto the last cell and color as red
Selection.Interior.ColorIndex = 3
End


I've been searching throughout the newsgroups for something similar but
of no avail. Hopefully you could offer some guidance.

Thanks again,
Ricky

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!