Edit table to have more of a DB structure

  • Thread starter Thread starter JMay
  • Start date Start date
J

JMay

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,
 
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
 
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
 
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.
 
Back
Top