In VBA my macro is this:

Sub test2()
RowCount = 1
For Each c In Range("G:G")
If c.Value = "P1.Value" Then
Cells(RowCount, "R").Value = c.Value
Cells(RowCount, "S").Value = c.Offset(0, 1).Value
Cells(RowCount, "T").Value = c.Offset(0, 2).Value
Cells(RowCount, "U").Value = c.Offset(0, 3).Value
Cells(RowCount, "V").Value = c.Offset(0, 4).Value
RowCount = RowCount + 1
End If
Next
End Sub

The part that does not seem to work is the line
'If c.Value = "P1.Value" Then'
I've tried many different variations to try to get c.Value to check against
the value of P1 (which is a date, 07/27/09). How do I get the if statement
to check for a match to P1?

If P1 is a variable you have assigned, then
If c.Value = P1
Or, if the date is stored in cell P1, then get rid of the outer quotes and
use a full reference
If c.Value = Sheet1.range("P1").value

When you put it all in quotes, it sees it as a string;
= "P1.Value" makes Excel look to see if any of the cells contain the text
[P1.Value] instead of the date you are looking for.

HTH,
Keith

"Jbm" wrote:

> In VBA my macro is this:
>
> Sub test2()
> RowCount = 1
> For Each c In Range("G:G")
> If c.Value = "P1.Value" Then
> Cells(RowCount, "R").Value = c.Value
> Cells(RowCount, "S").Value = c.Offset(0, 1).Value
> Cells(RowCount, "T").Value = c.Offset(0, 2).Value
> Cells(RowCount, "U").Value = c.Offset(0, 3).Value
> Cells(RowCount, "V").Value = c.Offset(0, 4).Value
> RowCount = RowCount + 1
> End If
> Next
> End Sub
>
> The part that does not seem to work is the line
> 'If c.Value = "P1.Value" Then'
> I've tried many different variations to try to get c.Value to check against
> the value of P1 (which is a date, 07/27/09). How do I get the if statement
> to check for a match to P1?

Drop the quotes; quotes are for Text not Dates.

This is a great resource.
http://www.cpearson.com/excel/datetime.htm

HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Jbm" wrote:

> In VBA my macro is this:
>
> Sub test2()
> RowCount = 1
> For Each c In Range("G:G")
> If c.Value = "P1.Value" Then
> Cells(RowCount, "R").Value = c.Value
> Cells(RowCount, "S").Value = c.Offset(0, 1).Value
> Cells(RowCount, "T").Value = c.Offset(0, 2).Value
> Cells(RowCount, "U").Value = c.Offset(0, 3).Value
> Cells(RowCount, "V").Value = c.Offset(0, 4).Value
> RowCount = RowCount + 1
> End If
> Next
> End Sub
>
> The part that does not seem to work is the line
> 'If c.Value = "P1.Value" Then'
> I've tried many different variations to try to get c.Value to check against
> the value of P1 (which is a date, 07/27/09). How do I get the if statement
> to check for a match to P1?

Keith,
Thanks for the detailed and clear explanation, that is exactly what I was
looking for and that explains more of the workings of VBA with which I am as
of yet unfamiliar (Excel Help within Excel is pretty opaque, at least for
me). Thanks again, and Ryan thanks for the link, I'll be using the
information there in the future.

Welcome to the wonderful world of VBA. Here are a few more resources which
will inevitably prove useful over time:

http://www.erlandsendata.no/english/...php?t=envbadac
http://www.contextures.com/tiptech.html
http://www.rondebruin.nl/sendmail.htm
http://www.rondebruin.nl/tips.htm
http://www.andypope.info/charts.htm
http://peltiertech.com/Excel/Charts/ChartIndex.html

When all else fails, of course you have this very same discussion forum!

Good luck,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Jbm" wrote:

> Keith,
> Thanks for the detailed and clear explanation, that is exactly what I was
> looking for and that explains more of the workings of VBA with which I am as
> of yet unfamiliar (Excel Help within Excel is pretty opaque, at least for
> me). Thanks again, and Ryan thanks for the link, I'll be using the
> information there in the future.

looks like you're just copying a range of 5 cells, so you should be able to
do something like this. i set the lastrow in column g so you don't have to
test the entire column.

Sub test2()
Dim RowCount As Long
Dim ws As Worksheet
Dim lastrow As Long
Dim c As Range
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "G").End(xlUp).Row
RowCount = 1

For Each c In ws.Range("G2:G" & lastrow)
If c.Value = ws.Range("P1").Value Then
Cells(RowCount, "R").Resize(1, 5).Value = c.Resize(1, 5).Value
RowCount = RowCount + 1
End If
Next
End Sub

--

Gary Keramidas
Excel 2003


"Jbm" <(E-Mail Removed)> wrote in message
news:85900A89-4962-42AC-A5FD-(E-Mail Removed)...
> In VBA my macro is this:
>
> Sub test2()
> RowCount = 1
> For Each c In Range("G:G")
> If c.Value = "P1.Value" Then
> Cells(RowCount, "R").Value = c.Value
> Cells(RowCount, "S").Value = c.Offset(0, 1).Value
> Cells(RowCount, "T").Value = c.Offset(0, 2).Value
> Cells(RowCount, "U").Value = c.Offset(0, 3).Value
> Cells(RowCount, "V").Value = c.Offset(0, 4).Value
> RowCount = RowCount + 1
> End If
> Next
> End Sub
>
> The part that does not seem to work is the line
> 'If c.Value = "P1.Value" Then'
> I've tried many different variations to try to get c.Value to check
> against
> the value of P1 (which is a date, 07/27/09). How do I get the if
> statement
> to check for a match to P1?