Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit table to have more of a DB structure
At work someone has supplied me with a table of info like:
A B C D E 1 asdfsdf sadfsadf sfasdf sfasdf 89.00 2 wertewrt ewrtet ertwert ertwrt 50.00 3 cvbxcvb cxvb xcvbcvb cvbv 45.00 4 ABCD 184.00 '<<(the total) Then another block (like above) starting at row 5 except maybe it has 6 line items, then a 3rd block with 12 line items and so on ....inconsistent like that to the end. I'd like to In Column F replicate the "ABCD" value In C4 << In No 1 Block on each row (example in F1:F3) and all other Blocks.. down thru the end of the worksheet. What code techniques will assist me in providing this? TIA, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit table to have more of a DB structure
The approach:
in F1 put in the formula =if(A2="",D2,na()) Sub AABBCC() Dim rng As Range Set rng = Range(Cells(1, 5), _ Cells(Rows.Count, 5).End(xlUp)) rng.Offset(0, 1).Formula = "=if(A2="""",D2,F2)" rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value Intersect(rng.Offset(0, -4) _ .SpecialCells(xlBlanks).EntireRow, _ rng.Offset(0, 1)).ClearContents End Sub -- Regards, Tom Ogilvy JMay wrote in message news:CHXpb.2280$0d2.1340@lakeread06... At work someone has supplied me with a table of info like: A B C D E 1 asdfsdf sadfsadf sfasdf sfasdf 89.00 2 wertewrt ewrtet ertwert ertwrt 50.00 3 cvbxcvb cxvb xcvbcvb cvbv 45.00 4 ABCD 184.00 '<<(the total) Then another block (like above) starting at row 5 except maybe it has 6 line items, then a 3rd block with 12 line items and so on ...inconsistent like that to the end. I'd like to In Column F replicate the "ABCD" value In C4 << In No 1 Block on each row (example in F1:F3) and all other Blocks.. down thru the end of the worksheet. What code techniques will assist me in providing this? TIA, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit table to have more of a DB structure
Tom - Thanks for the code.
Q's to follow: 1) Notice the formula you suggest to enter into cell F1; And also the Sub Procedure. Is the entry of the Formula required? Doesn't seem like it is... I have tried the full procedure with and without the formula, and it doesn't seem to matter. 2) Does the code line rng.Offset(0, 1).Formula = "=if(A2="""",D2,F2)" get entered (via the code) starting from the bottom and copied up? It seems again this to be the case otherwise (that is top to bottom order) I don't understand how it is acheiving the entry of the D2 in rows, say 1,2,3? 3) The next code line rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value seems to be saying "take the Values (since this is on the right-hand side of the assignment statement) that were placed in F:F and assign them to the Cells as Formulas ?? -- I'd have thought it would have been the other way around, that is or such as rng.Offset(0, 1).Value = rng.Offset(0, 1).Value << converting formulas to values,,?? Anyway as I run the code in step-through mode watching the spreadsheet.. I do see that it is converting the formulas to values, so anyway...?? Hope these Q's aren't to troublesome for you, but I sure want to understand this. Thanks again for you help; We all admire your frequent contribution(s). Jim May "Tom Ogilvy" wrote in message ... The approach: in F1 put in the formula =if(A2="",D2,na()) Sub AABBCC() Dim rng As Range Set rng = Range(Cells(1, 5), _ Cells(Rows.Count, 5).End(xlUp)) rng.Offset(0, 1).Formula = "=if(A2="""",D2,F2)" rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value Intersect(rng.Offset(0, -4) _ .SpecialCells(xlBlanks).EntireRow, _ rng.Offset(0, 1)).ClearContents End Sub -- Regards, Tom Ogilvy JMay wrote in message news:CHXpb.2280$0d2.1340@lakeread06... At work someone has supplied me with a table of info like: A B C D E 1 asdfsdf sadfsadf sfasdf sfasdf 89.00 2 wertewrt ewrtet ertwert ertwrt 50.00 3 cvbxcvb cxvb xcvbcvb cvbv 45.00 4 ABCD 184.00 '<<(the total) Then another block (like above) starting at row 5 except maybe it has 6 line items, then a 3rd block with 12 line items and so on ...inconsistent like that to the end. I'd like to In Column F replicate the "ABCD" value In C4 << In No 1 Block on each row (example in F1:F3) and all other Blocks.. down thru the end of the worksheet. What code techniques will assist me in providing this? TIA, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit table to have more of a DB structure
No, you don't need to enter the formula - I was just showing you what the
code was using to get the results. The formula is entered topdown with one command. It is equivalent to copying, but it isn't copies. You can do the same manually by selecting several cells, then in the formula bar, enter a formula or constant and do Ctrl+Enter instead of just enter. The value/formula is entered in all the selected cells. I am replacing the formula with the value displayed. In this case, the formula is a constant. It is correct as written. Not troublesome at all. Hope they help you understand the code. -- Regards, Tom Ogilvy JMay wrote in message news:_U6qb.2638$0d2.1663@lakeread06... Tom - Thanks for the code. Q's to follow: 1) Notice the formula you suggest to enter into cell F1; And also the Sub Procedure. Is the entry of the Formula required? Doesn't seem like it is... I have tried the full procedure with and without the formula, and it doesn't seem to matter. 2) Does the code line rng.Offset(0, 1).Formula = "=if(A2="""",D2,F2)" get entered (via the code) starting from the bottom and copied up? It seems again this to be the case otherwise (that is top to bottom order) I don't understand how it is acheiving the entry of the D2 in rows, say 1,2,3? 3) The next code line rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value seems to be saying "take the Values (since this is on the right-hand side of the assignment statement) that were placed in F:F and assign them to the Cells as Formulas ?? -- I'd have thought it would have been the other way around, that is or such as rng.Offset(0, 1).Value = rng.Offset(0, 1).Value << converting formulas to values,,?? Anyway as I run the code in step-through mode watching the spreadsheet.. I do see that it is converting the formulas to values, so anyway...?? Hope these Q's aren't to troublesome for you, but I sure want to understand this. Thanks again for you help; We all admire your frequent contribution(s). Jim May "Tom Ogilvy" wrote in message ... The approach: in F1 put in the formula =if(A2="",D2,na()) Sub AABBCC() Dim rng As Range Set rng = Range(Cells(1, 5), _ Cells(Rows.Count, 5).End(xlUp)) rng.Offset(0, 1).Formula = "=if(A2="""",D2,F2)" rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value Intersect(rng.Offset(0, -4) _ .SpecialCells(xlBlanks).EntireRow, _ rng.Offset(0, 1)).ClearContents End Sub -- Regards, Tom Ogilvy JMay wrote in message news:CHXpb.2280$0d2.1340@lakeread06... At work someone has supplied me with a table of info like: A B C D E 1 asdfsdf sadfsadf sfasdf sfasdf 89.00 2 wertewrt ewrtet ertwert ertwrt 50.00 3 cvbxcvb cxvb xcvbcvb cvbv 45.00 4 ABCD 184.00 '<<(the total) Then another block (like above) starting at row 5 except maybe it has 6 line items, then a 3rd block with 12 line items and so on ...inconsistent like that to the end. I'd like to In Column F replicate the "ABCD" value In C4 << In No 1 Block on each row (example in F1:F3) and all other Blocks.. down thru the end of the worksheet. What code techniques will assist me in providing this? TIA, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot table field structure! | Excel Worksheet Functions | |||
Edit the range of a pivot table | Excel Worksheet Functions | |||
Edit formula in pivot table | Excel Worksheet Functions | |||
how to amend pivot table structure | Excel Worksheet Functions | |||
Edit bar displays data different from table cell | Excel Discussion (Misc queries) |