| Home |
| Search |
| Today's Posts |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF Statement based on first two characters of cell
Glad I could help and thanks for the feedback
"PVANS" wrote: Hi Mike and Steffi Thanks for the advice. Mike, your one worked like a charm, thanks so much. Really appreciate the help. Regards "Mike H" wrote: Hi, Try this. I wasn't sure if you wanted to add the shhets with code. This doesn't, I have assumed they already exist Sub stance() Dim MyRange Dim CopyrangeRetail As Range Dim CopyrangeOpen As Range lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row Set MyRange = Range("D1:D" & lastrow) For Each c In MyRange If UCase(Left(c.Value, 2)) = "CO" Then If CopyrangeRetail Is Nothing Then Set CopyrangeRetail = c.EntireRow Else Set CopyrangeRetail = Union(CopyrangeRetail, c.EntireRow) End If End If If UCase(Left(c.Value, 2)) < "CO" Then If CopyrangeOpen Is Nothing Then Set CopyrangeOpen = c.EntireRow Else Set CopyrangeOpen = Union(CopyrangeOpen, c.EntireRow) End If End If Next If Not CopyrangeRetail Is Nothing Then CopyrangeRetail.Copy Destination:=Sheets("DT Retail").Range("A1") End If If Not CopyrangeOpen Is Nothing Then CopyrangeOpen.Copy Destination:=Sheets("DT CFD Open Positions").Range("A1") End If End Sub Mike "PVANS" wrote: Good morning, I am currently using the following basic code to copy the contents of a worksheet to another worksheet: Sheets("DT Master").Select Cells.Select Selection.Copy Sheets("DT CFD Open Positions").Select Range("A1").Select ActiveSheet.Paste However, I have now discovered, that there are several rows of data within the worksheet that should not be moved to the other worksheet, but instead be placed on their own worksheet in the workbook. The Column that determines which worksheet they need to be copied to is Column D. If the cell begins with "C0", then it needs to be copied to a new worksheet named "DT Retail"; if it begins with any other two characters, it needs to be copied to the "DT CFD Open Positions" worksheet. I think the best way to achieve this would be to use an IF ELSE statement. However, as a result of further code run modifications of the data in "DT CFD Open Positions", I would like the IF statement to first deal with the "DT Retail" data, and then the "DT CFD Open Positions" data, ie: IF column D starts with C0, Copy rows to "DT Retail" ELSE column D does not start with C0, Copy rows to "DT CFD Open Positions" Can someone please help me with the correct syntax and code? I would really appreciate the help Regards |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to lookup based in the first 3 characters of a cell | Excel Discussion (Misc queries) | |||
| Need to add figures based on alpha characters in the same cell | Excel Worksheet Functions | |||
| move data based on number of characters in a cell | Excel Programming | |||
| Merge a cell based on count of characters | Excel Discussion (Misc queries) | |||
| Change color of characters in a cell based of a cell reference | Excel Programming | |||
Threaded Mode
