Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to: c.value = "a cell value"
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to: c.value = "a cell value"
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to: c.value = "a cell value"
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to: c.value = "a cell value"
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to: c.value = "a cell value"
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to: c.value = "a cell value"
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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
"CELL("FILENAME") NOT UPDATE AFTER "SAVE AS" ACTION | Excel Discussion (Misc queries) | |||
Empty a cell if the values equal to "IN" , "MC" or "PP" | Excel Programming | |||
Help!!! Enter "7" in a cell and Excel changes the "7" to "11" immediately!!! | Excel Discussion (Misc queries) | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) |