Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another head banging problem
Thanks to all who helped me solve my last problem now, I have another.
I have a spreadsheet that has alphanumeric information in column b, and telephone number and the letters y or n (as in yes or no) in column d on the same row. So for instance: column b column c column d column e row1 298784 Y row4 10025 (212)280-4018 (917)596-8194 row7 10019 (212)315-5607 (347)968-9446 row10 11355 (718)463-0674 (212)369-2490 row13 Y row16 10024 (212)799-6026 (631)265-9168 row19 517472 N row22 10024 (212)874-0220 (917)846-0910 row24 10023 (212)874-2356 row26 10025 (212)865-0329 (212)792-4155 row29 547852A1 Y So, here's what I need to do. I need to grab all alphanumeric data off of a row column b, where the value of the same row in column d is equal to y or n and move it one column to the right in column a, all the while keeping it on the same corresponding row. If the value of column d is empty or it has one of those telephone numbers in it, it can ignore it. I would really appreciate it if someone can figure it out. Thanks in advance. Marvin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another head banging problem
We almost have it. I tried the code that moves data to the left. I
see what it's doing, it's looking at the value of the first column in question (in this case, column d), and evaluating whether or not there is 1)a value in it and 2) whether or not the corresponding column b in the same row has data. If these conditions are met, it moves the data in column b over to column a. What I wanted it to do was to look at column d, evaluate whether or not there is a Y or N as the value of that cell and then go to the corresponding column in the same row and then perform step 2). If there is anything else other than Y or N, treat it the same as null, which in this case would be to ignore it. Thanks for your help so far. Marvin On Jan 8, 11:37 am, Tom Ogilvy wrote: Sub AABB() Dim rng As Range Set rng = Columns(4).SpecialCells(xlConstants, xlTextValues) For Each cell In rng Cells(cell.Row, 1).Insert shift:=xlShiftToRight Next End Sub shifts all the data right (Kind of what you said - but I have doubts) if you want to move the value in column B left Sub AABB() Dim rng As Range Set rng = Columns(4).SpecialCells(xlConstants, xlTextValues) For Each cell In rng Cells(cell.Row, 2).copy cells(cell.row,1) cells(cell.row,2).ClearContents Next End Sub -- Regards, Tom Ogilvy " wrote: Thanks to all who helped me solve my last problem now, I have another. I have a spreadsheet that has alphanumeric information in column b, and telephone number and the letters y or n (as in yes or no) in column d on the same row. So for instance: column b column c column d column e row1 298784 Y row4 10025 (212)280-4018 (917)596-8194 row7 10019 (212)315-5607 (347)968-9446 row10 11355 (718)463-0674 (212)369-2490 row13 Y row16 10024 (212)799-6026 (631)265-9168 row19 517472 N row22 10024 (212)874-0220 (917)846-0910 row24 10023 (212)874-2356 row26 10025 (212)865-0329 (212)792-4155 row29 547852A1 Y So, here's what I need to do. I need to grab all alphanumeric data off of a row column b, where the value of the same row in column d is equal to y or n and move it one column to the right in column a, all the while keeping it on the same corresponding row. If the value of column d is empty or it has one of those telephone numbers in it, it can ignore it. I would really appreciate it if someone can figure it out. Thanks in advance. Marvin- Hide quoted text -- Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
over my head | Excel Discussion (Misc queries) | |||
I'm in over my head. | Excel Programming | |||
I'm over my head on this one... | Excel Programming | |||
Mailmerge problem from Excel to Word placies data 1st row in head | Excel Discussion (Misc queries) | |||
Banging head against wall with VLOOKUP | Excel Discussion (Misc queries) |