how to: c.value = "a cell value"

[Jbm]

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?

 

[ker_01]

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

 

[ryguy7272]

Drop the quotes; quotes are for Text not Dates.

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

HTH,
Ryan---

 

[Jbm]

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.

 

[ryguy7272]

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/index.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---

 

[Gary Keramidas]

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