"auto fill" Look Up table

[BEEJAY]

Hello:
Am trying to "auto fill" a look up table.
Have a number of sheets that do 'behind the scenes' calculations.
The length determines what the $'s will be.
On my overview sheet, making up a look-up table.
B1 is to be used for length input
D1 will provide the $ value, based on the length in B1
B5 thru B50 is the lengths, in feet, low to high

What I'm picturing is code that will:
Copy B5 to B1. The D1 Value is then changed - Copy and PasteValue to D5.
Copy B6 to B1. The D1 (changed) Value is then Copy and Paste Value to D6.
Etc............
Since there will not be any formulas in the look-up chart, I can then copy
the resulting chart to all the required Work-Books.
Somehow, the program should also (self) determine the last row, since this
macro would be used numerous times, almost always with different lengths of
column.

I have a basic recorded macro started, but it requires a loop, which I
understand is not the most efficient way of doing something like this.

The requirement for this process is numerous, so I'm muchly looking forward
to being able to automate this.

Thanks.

 

[Joel]

I don't think you need autofill. Autofill is only necessary if you have a
pattern of at least two cells and want to repeat the pattern. You need to
select two cells and then copy the pattern down the worksheet. for example
if you had in

A1 = 2
A2 = 4

Then on the worksheet select cells A1 and A2 and pull down the worksheet
excel will repeat the pattern and put in column A 2, 4, 6, 8, 10, 12, ...


You don't need to use autofill if you are just copying a formula down a
column. for example

supoose on the workshedet in colun A yo had employee names. then in columns
B - columns D yo had number you wanted to sum and put in column E. Then use
this code.


'Get last employee in column A
LastRow = range("A" & rows.count).end(xlup).row

'put Sum formula in column E
Range("E1").Formula = "Sum(B11)"
'copy formula down column E
Range("E1").Copy _
Destination:=Range("E1:E" & LastRow)


I can't tell from you request if looping is appropriate or not appropriate.
Looping is not required in this case becasue I'm copying the same formula
down the column which is more efficient then performing multiple copies.

 

[BEEJAY]

Joel:
Thanks for your response. I should have used a better choice of words than
Auto Fill.
The processed is more involved than the standard Excel AutoFill that you are
describing.
If you'd be so kind as to re-read my message, substitute "autoFill" with
Automatically Fill, or probably better yet, programmatically fill.

This procedure is something I have done manually off and on for years and
its high time I somehow get some code for it.
It is very time consuming (and prone to error) to do this manually, due to
the size of the look-up tables I normally have to work with.

Looking forward to what-ever help you can give.

 

[Patrick Molloy]

for rw = 5 to 50
Range("B1") = cells(rw,"B").Value
Range("D1").Formula = Range("D1").Formula ' forces a recalculate on
D1
Cells(rw,"D").Value = Range("D1").Value
next

or better still

with Range("D550")
.Formula = Range("D1").Formula
.Value = .Value
end with

 

[Patrick Molloy]

should have been

With Range("D550")
.FormulaR1C1 = Range("D1").FormulaR1C1
.Value = .Value
End With

 

[BEEJAY]

Patrick:

Thanks for the responses.
The 1st method works great!, as shown.

I'm trying out the (revised) 2nd method, but cannot get it to operate.
It appears to require some additonal code, but I have no idea what.
I presume some combination of the two?.

Could you please advise what is required? For starters, there doesn't seem
to be anything that copies the lengths from column D into B1, which in turn
would then trigger the newly calculated $ value.

Thank you