I just want to have my columns formatted without actually formatting 6000 rows.

[Bon Rouge]

Does this make sense?

As it is, I have sheets that get longer with each new entry. There is
formula in just one cell in column G that I drag down every time to fil
the right cell in the row.

If I fill the whole column with the formula it will fill about 600
rows and screw up my scrollbar (I kind of like having the scrollbar g
from the first row to the last used row).

How can I do the same thing without actually screwing up the scrollbar
- I figure there must be a way.

Cheers

 

[Debra Dalgleish]

If you use Excel's built-in data form, the formula will be entered
automatically.

To use it, select a cell in the table.
Choose Data>Form
Click the New button, and enter your data

Or download John Walkenbach's Enhanced Data Form. It's a free add-in,
allows combo boxes, inserts new rows with formulas, and has unlimited
fields.

http://j-walk.com/ss/dataform/index.htm

 

[Bon Rouge]

Thanks Debra, but to be honest, that's more of a hassle than what I d
at the moment. Right now, I just have to drag the formula down afte
typing the data into another cell. What you're suggesting there seem
to be about 3 extra clicks.

What I'm looking for is no extra clicks and to cut out the need to dra
the formula down. I'm lazy - what can I say?

Of course, I may have misunderstood what you were suggesting... if
have, please let me know.

Cheers

 

[Debra Dalgleish]

Turn on the AutoExtend feature (Tools>Options, Edit tab, 'Extend list
formats and formulas), and the formula should be automatically entered
as you add data to the table.

The following MSKB article explains how Auto Extend works:

XL2000: How Auto Extend List Behavior Works
http://support.microsoft.com/?id=231002

 

[Don Guillett]

If you didn't like that idea then
right click sheet tab>view code>insert this>modify to suit>save
now when you enter a number in col C, col G will be that num * 2.3

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo fixit
Application.EnableEvents = False
If Target.Row < 3 And Target.Column <> 3 Then Exit Sub
Target.Offset(0, 4 = Target * 2.3
fixit:
Application.EnableEvents = True
End Sub

 

[Bon Rouge]

The Auto Extend feature was already on when I checked.... Not sure wh
it wasn't working...

Don - thanks for this. Now, can you just tell me how to change it s
that it works with this formula? :

=SUM(G57,C58,-E58)

It's a really simple formula - I'm sure this isn't difficult for yo
guys.

I appreciate your help.

Stev

 

[Debra Dalgleish]

One more option -- when you select the cell that should contain the
formula, press Ctrl+D, to copy the formula down from the cell above.

 

[Bon Rouge]

Thanks for that, but it's still more hassle than just dragging the cel
down.
I want to do one thing now so that I never have to touch the G colum
again - it'll work by itself

 

[Don Guillett]

Just play around with it. Have a look in HELP index for OFFSET

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 3 Or Target.Column <> 3 Then Exit Sub
On Error GoTo fixit
Application.EnableEvents = False
'Target.Offset(0, 4) = Target * 2.3
Target.Offset(, 4) = Target.Offset(-1, 4) _
+ Target - Target.Offset(-1, 2)
'=SUM(G57,C58,-E58) assuming target in C
fixit:
Application.EnableEvents = True
End Sub

 

[Bon Rouge]

Thanks again, but...

I tried that and it pointed to an error in the first line of your code


I don't know very much about this code stuff, so...

Am I just doing something wrong? (I think I did just what you said).

Cheer

 

[Don Guillett]

Is is possible that you did not put it in the sheet module, but in a regular
module?

 

[Bon Rouge]

No - I did just what you said.

 

[Bon Rouge]

Any other ideas?

I basically want to fill the cell in column G with the formula above
but only when there is something in column A (which will be the date o
the entry)

 

[Don Guillett]

Just modify my macro to fit your situation.

 

[Bon Rouge]

Don - I'd like to use your macro, but I got an error message when
tried to use it and I don't know enough about these things to fix th
problem

 

[Don Guillett]

Send me a SMALL sample workbook with exactly what you want.

 

[Bon Rouge]

Thanks Don.

That was exactly what I wanted.

If you have any more tips about my workbook, I'm all ears.

For anyone else reading this, this was the solution :

-------------------------------------

Private Sub Workbook_Open()

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target A
Range)
If Target.Worksheet.Name = "LIST" Or _
Target.Row < 5 Or Target.Column <> 1 Then Exit Sub
On Error GoTo fixit
Application.EnableEvents = False

'comment this & uncomment others for values
Cells(Target.Row - 1, "g").Copy Cells(Target.Row, "g")

' Target.Offset(, 6) = Target.Offset(-1, 6) _
'+ Target.Offset(0, 2) - Target.Offset(0, 4)

fixit:
Application.EnableEvents = True
End Sub

--------------------------------------------

I have a question about freezing the frames now (maybe this should g
somewhere else on the message board, but...

How can I do what you did - freeze the top few rows - all at the sam
time? I tried selecting all the sheets and doing it, but that didn'
work.

Cheers,

Stev

 

[Don Guillett]

try
sub freezeall()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
Range("F3").Select
ActiveWindow.FreezePanes = True
Next
end sub

 

[Bon Rouge]

Again - just what I wanted.

I really should take some time to learn what's going on here..