Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tom Ogilvy - Request for follow-up on previous help offered
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tom Ogilvy - Request for follow-up on previous help offered
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:bJhqb.3017$0d2.2786@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 | |||
Macro Request: Fill in empty cells with previous Filled cell in column | Excel Worksheet Functions | |||
Simplifying my Previous Request for Assistance | Links and Linking in Excel | |||
Simplifying my previous Request for Assistance | Excel Worksheet Functions | |||
Simplifying my previous Request for Assistance | Excel Discussion (Misc queries) | |||
Simplifying my previous Request for Assistance | Links and Linking in Excel |