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

  • Thread starter Thread starter Jbm
  • Start date Start date
J

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?
 
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
 
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
 
Back
Top