Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Revisited: If Cell in row has data then do this: HELP
Code doesn't seemto be working;
At first I thought it was working, then I noticed all it was doing was clearing the contents of the cells in column I When I comment out these two lines nothing happens at all: cells(rng.row,"G").Value = cell cell.Clearcontents Now I fI understood the code corrctly, editing out those two lines should still allow F&G to concatanate. Which they are currently not Actually, I've found that it does work sort of: It concatanates only F1&G1. Funny thing is it does it for as many cells in column I that do have data so as an example: F1 = Hello G1 = World Column I has 4 cells that contain data nowF1 = Hello World World World World "Tom Ogilvy" wrote in message ... Dim rng as Range, cell as Range set rng = Range(Cells(1,"I"),Cells(rows.count,"I").End(xlup) for each cell in rng if not isempty(cell) then cells(rng.row,"F").Value = cells(rng.row,"F").Value _ & cels(rng.row,"G").Value cells(rng.row,"G").Value = cell cell.Clearcontents end if Next This would replace a formula if it is in one of the cells being manipulated, so the assumption is that the cells do not contain formulas. -- Regards, Tom Ogilvy "Yogi_Bear_79" wrote in message ... If column I has data in it I want Excel to perform the following. 1. Concatanate the data in the coresponding row column G to the end of the data in the coresponding row in column F 2. Move the data from column I to the coresponding cell in the same row in column G |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Revisited: If Cell in row has data then do this: HELP
H
Try this. If the range F1:F4 has a,b,c,d and G1:G4 has e,f,g,h and I1:I4 is 1,2,3,4 the output will b F1:F4 ae, bf, cg, dh and G1:G4 1,2,3,4 and I1:I4 blank Ton Sub bbb( Dim rng As Range, cell As Rang Set rng = Range(Cells(1, "I"), Cells(Rows.Count, "I").End(xlUp) For Each cell In rn If Not IsEmpty(cell) The Cells(cell.Row, "F").Value = Cells(cell.Row, "F").Value & Cells(cell.Row, "G").Valu Cells(cell.Row, "G").Value = cel cell.ClearContent End I Nex End Su ----- Yogi_Bear_79 wrote: ---- Code doesn't seemto be working At first I thought it was working, then I noticed all it was doing wa clearing the contents of the cells in column When I comment out these two lines nothing happens at all cells(rng.row,"G").Value = cel cell.Clearcontent Now I fI understood the code corrctly, editing out those two lines shoul still allow F&G to concatanate. Which they are currently no Actually I've found that it does work sort of It concatanates only F1&G1. Funny thing is it does it for as many cells i column I that do have data so as an example F1 = Hell G1 = Worl Column I has 4 cells that contain dat nowF1 = Hello World World World Worl "Tom Ogilvy" wrote in messag .. Dim rng as Range, cell as Rang set rng = Range(Cells(1,"I"),Cells(rows.count,"I").End(xlup for each cell in rn if not isempty(cell) the cells(rng.row,"F").Value = cells(rng.row,"F").Value & cels(rng.row,"G").Valu cells(rng.row,"G").Value = cel cell.Clearcontent end i Nex This would replace a formula if it is in one of the cells bein manipulated so the assumption is that the cells do not contain formulas -- Regards Tom Ogilv "Yogi_Bear_79" wrote in messag .. If column I has data in it I want Excel to perform the following 1. Concatanate the data in the coresponding row column G to the end o th data in the coresponding row in column 2. Move the data from column I to the coresponding cell in the same ro i column |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Revisited: If Cell in row has data then do this: HELP
Thanks..Changing rng to Cells did the trick
"acw" wrote in message ... Hi Try this. If the range F1:F4 has a,b,c,d and G1:G4 has e,f,g,h and I1:I4 is 1,2,3,4 the output will be F1:F4 ae, bf, cg, dh and G1:G4 1,2,3,4 and I1:I4 blank. Tony Sub bbb() Dim rng As Range, cell As Range Set rng = Range(Cells(1, "I"), Cells(Rows.Count, "I").End(xlUp)) For Each cell In rng If Not IsEmpty(cell) Then Cells(cell.Row, "F").Value = Cells(cell.Row, "F").Value _ & Cells(cell.Row, "G").Value Cells(cell.Row, "G").Value = cell cell.ClearContents End If Next End Sub ----- Yogi_Bear_79 wrote: ----- Code doesn't seemto be working; At first I thought it was working, then I noticed all it was doing was clearing the contents of the cells in column I When I comment out these two lines nothing happens at all: cells(rng.row,"G").Value = cell cell.Clearcontents Now I fI understood the code corrctly, editing out those two lines should still allow F&G to concatanate. Which they are currently not Actually, I've found that it does work sort of: It concatanates only F1&G1. Funny thing is it does it for as many cells in column I that do have data so as an example: F1 = Hello G1 = World Column I has 4 cells that contain data nowF1 = Hello World World World World "Tom Ogilvy" wrote in message ... Dim rng as Range, cell as Range set rng = Range(Cells(1,"I"),Cells(rows.count,"I").End(xlup) for each cell in rng if not isempty(cell) then cells(rng.row,"F").Value = cells(rng.row,"F").Value _ & cels(rng.row,"G").Value cells(rng.row,"G").Value = cell cell.Clearcontents end if Next This would replace a formula if it is in one of the cells being manipulated, so the assumption is that the cells do not contain formulas. -- Regards, Tom Ogilvy "Yogi_Bear_79" wrote in message ... If column I has data in it I want Excel to perform the following. 1. Concatanate the data in the coresponding row column G to the end of the data in the coresponding row in column F 2. Move the data from column I to the coresponding cell in the same row in column G |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Revisited: If Cell in row has data then do this: HELP
Sorry for the typo - it is a common typo I make unfortunately.
-- Regards, Tom Ogilvy Yogi_Bear_79 wrote in message ... Thanks..Changing rng to Cells did the trick "acw" wrote in message ... Hi Try this. If the range F1:F4 has a,b,c,d and G1:G4 has e,f,g,h and I1:I4 is 1,2,3,4 the output will be F1:F4 ae, bf, cg, dh and G1:G4 1,2,3,4 and I1:I4 blank. Tony Sub bbb() Dim rng As Range, cell As Range Set rng = Range(Cells(1, "I"), Cells(Rows.Count, "I").End(xlUp)) For Each cell In rng If Not IsEmpty(cell) Then Cells(cell.Row, "F").Value = Cells(cell.Row, "F").Value _ & Cells(cell.Row, "G").Value Cells(cell.Row, "G").Value = cell cell.ClearContents End If Next End Sub ----- Yogi_Bear_79 wrote: ----- Code doesn't seemto be working; At first I thought it was working, then I noticed all it was doing was clearing the contents of the cells in column I When I comment out these two lines nothing happens at all: cells(rng.row,"G").Value = cell cell.Clearcontents Now I fI understood the code corrctly, editing out those two lines should still allow F&G to concatanate. Which they are currently not Actually, I've found that it does work sort of: It concatanates only F1&G1. Funny thing is it does it for as many cells in column I that do have data so as an example: F1 = Hello G1 = World Column I has 4 cells that contain data nowF1 = Hello World World World World "Tom Ogilvy" wrote in message ... Dim rng as Range, cell as Range set rng = Range(Cells(1,"I"),Cells(rows.count,"I").End(xlup) for each cell in rng if not isempty(cell) then cells(rng.row,"F").Value = cells(rng.row,"F").Value _ & cels(rng.row,"G").Value cells(rng.row,"G").Value = cell cell.Clearcontents end if Next This would replace a formula if it is in one of the cells being manipulated, so the assumption is that the cells do not contain formulas. -- Regards, Tom Ogilvy "Yogi_Bear_79" wrote in message ... If column I has data in it I want Excel to perform the following. 1. Concatanate the data in the coresponding row column G to the end of the data in the coresponding row in column F 2. Move the data from column I to the coresponding cell in the same row in column G |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Revisited: If Cell in row has data then do this: HELP
Tom,
I couldn't accomplish half of the projects without you...a typo is nothing but a learning tool for me! "Tom Ogilvy" wrote in message ... Sorry for the typo - it is a common typo I make unfortunately. -- Regards, Tom Ogilvy Yogi_Bear_79 wrote in message ... Thanks..Changing rng to Cells did the trick "acw" wrote in message ... Hi Try this. If the range F1:F4 has a,b,c,d and G1:G4 has e,f,g,h and I1:I4 is 1,2,3,4 the output will be F1:F4 ae, bf, cg, dh and G1:G4 1,2,3,4 and I1:I4 blank. Tony Sub bbb() Dim rng As Range, cell As Range Set rng = Range(Cells(1, "I"), Cells(Rows.Count, "I").End(xlUp)) For Each cell In rng If Not IsEmpty(cell) Then Cells(cell.Row, "F").Value = Cells(cell.Row, "F").Value _ & Cells(cell.Row, "G").Value Cells(cell.Row, "G").Value = cell cell.ClearContents End If Next End Sub ----- Yogi_Bear_79 wrote: ----- Code doesn't seemto be working; At first I thought it was working, then I noticed all it was doing was clearing the contents of the cells in column I When I comment out these two lines nothing happens at all: cells(rng.row,"G").Value = cell cell.Clearcontents Now I fI understood the code corrctly, editing out those two lines should still allow F&G to concatanate. Which they are currently not Actually, I've found that it does work sort of: It concatanates only F1&G1. Funny thing is it does it for as many cells in column I that do have data so as an example: F1 = Hello G1 = World Column I has 4 cells that contain data nowF1 = Hello World World World World "Tom Ogilvy" wrote in message ... Dim rng as Range, cell as Range set rng = Range(Cells(1,"I"),Cells(rows.count,"I").End(xlup) for each cell in rng if not isempty(cell) then cells(rng.row,"F").Value = cells(rng.row,"F").Value _ & cels(rng.row,"G").Value cells(rng.row,"G").Value = cell cell.Clearcontents end if Next This would replace a formula if it is in one of the cells being manipulated, so the assumption is that the cells do not contain formulas. -- Regards, Tom Ogilvy "Yogi_Bear_79" wrote in message ... If column I has data in it I want Excel to perform the following. 1. Concatanate the data in the coresponding row column G to the end of the data in the coresponding row in column F 2. Move the data from column I to the coresponding cell in the same row in column G |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
14 Day Average REVISITED | Excel Discussion (Misc queries) | |||
Custom Cell Format - Revisited | Excel Discussion (Misc queries) | |||
Help with averages revisited | Excel Worksheet Functions | |||
Graphic within Cell Comment (revisited) | Excel Discussion (Misc queries) | |||
Blank cell revisited | Excel Discussion (Misc queries) |