problem formulas
 

Hi,

I have a sheet where the user is asked to put in columns A:F.

in column
A : number
B : Name
C : Date of birth
D : Salary
E : Parttime%
F : Hiredate

Now, what i want in columns G:P is to put in certain formulas.
Because i do not know how many employees (rows) are filled in by the user, i
put my formulas in all cells ranging from G1:P5000.
This works ofcourse (unless the user puts in more than 5000 names) but the
applications becomes very slow due to all the calculations it does.

Is there a way to only put in the formulas in colums G:P when the user has
put something in columns A:F ?
In this case there would be only formulas on the rows that the user uses !

Any ideas how to program this ?
Thanks,
Pierre

--
Message posted via http://www.officekb.com

problem formulas
 

see if this works for you

Sub fill()

Dim lastrow As Long

lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Range("G1:p1").AutoFill Destination:=Range("G1:P" & lastrow),
Type:=xlFillDefault


End Sub

--


Gary


"Pierre via OfficeKB.com" <u13950@uwe wrote in message
news:58bbd2b0eb079@uwe...
Hi,

I have a sheet where the user is asked to put in columns A:F.

in column
A : number
B : Name
C : Date of birth
D : Salary
E : Parttime%
F : Hiredate

Now, what i want in columns G:P is to put in certain formulas.
Because i do not know how many employees (rows) are filled in by the user,
i
put my formulas in all cells ranging from G1:P5000.
This works ofcourse (unless the user puts in more than 5000 names) but the
applications becomes very slow due to all the calculations it does.

Is there a way to only put in the formulas in colums G:P when the user has
put something in columns A:F ?
In this case there would be only formulas on the rows that the user uses !

Any ideas how to program this ?
Thanks,
Pierre

--
Message posted via http://www.officekb.com

problem formulas
 

i forgot about the word wrap problem

Option Explicit
Sub fill()

Dim lastrow As Long

lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Range("G1:p1").AutoFill Destination:=Range("G1:P" & lastrow), _
Type:=xlFillDefault


End Sub


--


Gary


"Pierre via OfficeKB.com" <u13950@uwe wrote in message
news:58bbd2b0eb079@uwe...
Hi,

I have a sheet where the user is asked to put in columns A:F.

in column
A : number
B : Name
C : Date of birth
D : Salary
E : Parttime%
F : Hiredate

Now, what i want in columns G:P is to put in certain formulas.
Because i do not know how many employees (rows) are filled in by the user,
i
put my formulas in all cells ranging from G1:P5000.
This works ofcourse (unless the user puts in more than 5000 names) but the
applications becomes very slow due to all the calculations it does.

Is there a way to only put in the formulas in colums G:P when the user has
put something in columns A:F ?
In this case there would be only formulas on the rows that the user uses !

Any ideas how to program this ?
Thanks,
Pierre

--
Message posted via http://www.officekb.com

problem formulas
 

You can create the formulas after the user enters the info in column F. Here
is an example, put in your own formulas.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 6 Then
Cells(Target.Row, "G").Formula = "=A" & Format(Target.Row)
Cells(Target.Row, "H").Formula = "=B" & Format(Target.Row)
Cells(Target.Row, "I").Formula = "=C" & Format(Target.Row)
Cells(Target.Row, "J").Formula = "=D" & Format(Target.Row)
End If

End Sub


"Pierre via OfficeKB.com" wrote:

Hi,

I have a sheet where the user is asked to put in columns A:F.

in column
A : number
B : Name
C : Date of birth
D : Salary
E : Parttime%
F : Hiredate

Now, what i want in columns G:P is to put in certain formulas.
Because i do not know how many employees (rows) are filled in by the user, i
put my formulas in all cells ranging from G1:P5000.
This works ofcourse (unless the user puts in more than 5000 names) but the
applications becomes very slow due to all the calculations it does.

Is there a way to only put in the formulas in colums G:P when the user has
put something in columns A:F ?
In this case there would be only formulas on the rows that the user uses !

Any ideas how to program this ?
Thanks,
Pierre

--
Message posted via http://www.officekb.com

problem formulas
 

Hi Gary,

See if i understand your code...

Does this mean that i only have to put in the formulas in the fist row ? and
if the user puts in more data (more rows) the formulas of the first row are
copied downwards?

Pierre

Gary Keramidas wrote:
see if this works for you

Sub fill()

Dim lastrow As Long

lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Range("G1:p1").AutoFill Destination:=Range("G1:P" & lastrow),
Type:=xlFillDefault

End Sub

Hi,

[quoted text clipped - 22 lines]
Thanks,
Pierre

--
Message posted via http://www.officekb.com

problem formulas
 

Hi Gary,

I adapted your code to the following:

Sub formules_vullen()
Dim lastrow As Long

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("W4").AutoFill Destination:=Range("W" & lastrow), Type:=xlFillDefault
End Sub

I wanted to try filling column W with the value in W4 but it did not work...
But it does not work. I get the message :

error 1004
Method Autofill of class range

Any ideas?
Thanks,
Pierre


Gary Keramidas wrote:
see if this works for you

Sub fill()

Dim lastrow As Long

lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Range("G1:p1").AutoFill Destination:=Range("G1:P" & lastrow),
Type:=xlFillDefault

End Sub

Hi,

[quoted text clipped - 22 lines]
Thanks,
Pierre

--
Message posted via http://www.officekb.com

problem formulas
 

Hi charlie,

Thanks but can you explain what happens because i do not understand....
please elaborate on your code please...
Pierre

Charlie wrote:
You can create the formulas after the user enters the info in column F. Here
is an example, put in your own formulas.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 6 Then
Cells(Target.Row, "G").Formula = "=A" & Format(Target.Row)
Cells(Target.Row, "H").Formula = "=B" & Format(Target.Row)
Cells(Target.Row, "I").Formula = "=C" & Format(Target.Row)
Cells(Target.Row, "J").Formula = "=D" & Format(Target.Row)
End If

End Sub

Hi,

[quoted text clipped - 21 lines]
Thanks,
Pierre

--
Message posted via http://www.officekb.com

problem formulas
 

Hi gary,

I adapted my code, that was the adapted code from you to:

Sub formules_vullen()

Dim lastrow As Long

lastrow = Worksheets(1).Cells(Rows.Count, "D").End(xlUp).Row
Range("W4").AutoFill Destination:=Range("W4:W" & lastrow), _
Type:=xlFillDefault
End Sub

And now it works fine !
Thanks for your help,
Pierre

Gary Keramidas wrote:
i forgot about the word wrap problem

Option Explicit
Sub fill()

Dim lastrow As Long

lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Range("G1:p1").AutoFill Destination:=Range("G1:P" & lastrow), _
Type:=xlFillDefault

End Sub

Hi,

[quoted text clipped - 22 lines]
Thanks,
Pierre

--
Message posted via http://www.officekb.com

problem formulas
 

Sub formules_vullen()
Dim lastrow As Long

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("W4").AutoFill Destination:=Range("W4:W" & lastrow),
Type:=xlFillDefault
End Sub

--
Regards,
Tom Ogilvy

"Pierre via OfficeKB.com" <u13950@uwe wrote in message
news:58bc2a440d3a5@uwe...
Hi Gary,

I adapted your code to the following:

Sub formules_vullen()
Dim lastrow As Long

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("W4").AutoFill Destination:=Range("W" & lastrow),
Type:=xlFillDefault
End Sub

I wanted to try filling column W with the value in W4 but it did not
work...
But it does not work. I get the message :

error 1004
Method Autofill of class range

Any ideas?
Thanks,
Pierre


Gary Keramidas wrote:
see if this works for you

Sub fill()

Dim lastrow As Long

lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Range("G1:p1").AutoFill Destination:=Range("G1:P" & lastrow),
Type:=xlFillDefault

End Sub

Hi,

[quoted text clipped - 22 lines]
Thanks,
Pierre

--
Message posted via http://www.officekb.com

problem formulas
 

Hi charlie,

I tried the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
Cells(Target.Row, "W").Formula = "=if(Or(P4='offerte';P4='afgesloten');if
(T4<"";T4*V4);0)" & Format(Target.Row)
'Cells(Target.Row, "H").Formula = "=B" & Format(Target.Row)
'Cells(Target.Row, "I").Formula = "=C" & Format(Target.Row)
'Cells(Target.Row, "J").Formula = "=D" & Format(Target.Row)
End If

End Sub

But nothing happens.
what is wrong ? any ideas ?
Pierre

Charlie wrote:
You can create the formulas after the user enters the info in column F. Here
is an example, put in your own formulas.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 6 Then
Cells(Target.Row, "G").Formula = "=A" & Format(Target.Row)
Cells(Target.Row, "H").Formula = "=B" & Format(Target.Row)
Cells(Target.Row, "I").Formula = "=C" & Format(Target.Row)
Cells(Target.Row, "J").Formula = "=D" & Format(Target.Row)
End If

End Sub

Hi,

[quoted text clipped - 21 lines]
Thanks,
Pierre

--
Message posted via http://www.officekb.com

problem formulas
 

Whenever a user changes the contents of a cell (and presses Enter or moves
the cursor) the Worksheet_Change macro for that sheet is activated. The
Target argument will be the cell that was changed. You can check the column
number of the Target cell and then fill in your formulas for other columns in
that same row when the desired column has been filled. Since you implied
that the user will enter data into column "F" last I chose column "F" (6) as
the one to look for.

"Pierre via OfficeKB.com" wrote:

Hi charlie,

Thanks but can you explain what happens because i do not understand....
please elaborate on your code please...
Pierre

Charlie wrote:
You can create the formulas after the user enters the info in column F. Here
is an example, put in your own formulas.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 6 Then
Cells(Target.Row, "G").Formula = "=A" & Format(Target.Row)
Cells(Target.Row, "H").Formula = "=B" & Format(Target.Row)
Cells(Target.Row, "I").Formula = "=C" & Format(Target.Row)
Cells(Target.Row, "J").Formula = "=D" & Format(Target.Row)
End If

End Sub

Hi,

[quoted text clipped - 21 lines]
Thanks,
Pierre

--
Message posted via http://www.officekb.com

problem formulas
 

Make sure the code is in the worksheet module and not a standard macro
module...

--
steveB

Remove "AYN" from email to respond
"Charlie" wrote in message
...
Whenever a user changes the contents of a cell (and presses Enter or moves
the cursor) the Worksheet_Change macro for that sheet is activated. The
Target argument will be the cell that was changed. You can check the
column
number of the Target cell and then fill in your formulas for other columns
in
that same row when the desired column has been filled. Since you implied
that the user will enter data into column "F" last I chose column "F" (6)
as
the one to look for.

"Pierre via OfficeKB.com" wrote:

Hi charlie,

Thanks but can you explain what happens because i do not understand....
please elaborate on your code please...
Pierre

Charlie wrote:
You can create the formulas after the user enters the info in column F.
Here
is an example, put in your own formulas.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 6 Then
Cells(Target.Row, "G").Formula = "=A" & Format(Target.Row)
Cells(Target.Row, "H").Formula = "=B" & Format(Target.Row)
Cells(Target.Row, "I").Formula = "=C" & Format(Target.Row)
Cells(Target.Row, "J").Formula = "=D" & Format(Target.Row)
End If

End Sub

Hi,

[quoted text clipped - 21 lines]
Thanks,
Pierre

--
Message posted via http://www.officekb.com

problem formulas
 

Hi Steve,

Good suggestion however the code still does not work....
This is my code so far....

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
Cells(Target.Row, "W").Formula = "=if(Or(P4='quote'];P4='closed');if
(T4<"";T4*V4);0)" & Format(Target.Row)
End If
End Sub

Any ideas?

Thanks,
Pierre

STEVE BELL wrote:
Make sure the code is in the worksheet module and not a standard macro
module...

Whenever a user changes the contents of a cell (and presses Enter or moves
the cursor) the Worksheet_Change macro for that sheet is activated. The
[quoted text clipped - 33 lines]
Thanks,
Pierre

--
Message posted via http://www.officekb.com

problem formulas
 

i assumed you had formulas in g1:p1 and the code just autofilled down those
formulas for the number of rows of data you have. you could actually create
the formulas for g1:p1 in vb and then autofill them down, too.


was there something else you were trying to accomplish? i noticed your code
is only filling column W.


--


Gary


"Pierre via OfficeKB.com" <u13950@uwe wrote in message
news:58bc37f9ce939@uwe...
Hi gary,

I adapted my code, that was the adapted code from you to:

Sub formules_vullen()

Dim lastrow As Long

lastrow = Worksheets(1).Cells(Rows.Count, "D").End(xlUp).Row
Range("W4").AutoFill Destination:=Range("W4:W" & lastrow), _
Type:=xlFillDefault
End Sub

And now it works fine !
Thanks for your help,
Pierre

Gary Keramidas wrote:
i forgot about the word wrap problem

Option Explicit
Sub fill()

Dim lastrow As Long

lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Range("G1:p1").AutoFill Destination:=Range("G1:P" & lastrow), _
Type:=xlFillDefault

End Sub

Hi,

[quoted text clipped - 22 lines]
Thanks,
Pierre

--
Message posted via http://www.officekb.com

problem formulas
 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.count = 1 then
If Target.Column = 4 Then
sForm = "=if(OR(P4=""quote"",P4=""closed""),if(T4<"""",T4 *V4,0),0)
Cells(Target.Row, "W").Formula = Replace(sForm,4,Target.row)
End If
End if
End Sub

Adjust the formula to reflect what you actually want to appear if the OR
condition is not met.
--
regards,
Tom Ogilvy

"Pierre via OfficeKB.com" <u13950@uwe wrote in message
news:58bcea8fd42e1@uwe...
Hi Steve,

Good suggestion however the code still does not work....
This is my code so far....

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
Cells(Target.Row, "W").Formula = "=if(Or(P4='quote'];P4='closed');if
(T4<"";T4*V4);0)" & Format(Target.Row)
End If
End Sub

Any ideas?

Thanks,
Pierre

STEVE BELL wrote:
Make sure the code is in the worksheet module and not a standard macro
module...

Whenever a user changes the contents of a cell (and presses Enter or
moves
the cursor) the Worksheet_Change macro for that sheet is activated.
The
[quoted text clipped - 33 lines]
Thanks,
Pierre

--
Message posted via http://www.officekb.com

problem formulas
 

Pierre,

The problem might be the use of single quotes: 'quote'

Here's some simple code that I recorded to demonstrate the quote issue...

Range("A1").Select
ActiveCell.Formula = "=IF(P1=""quote"",""Yes"",""no"")"

--
steveB

Remove "AYN" from email to respond
"Pierre via OfficeKB.com" <u13950@uwe wrote in message
news:58bcea8fd42e1@uwe...
Hi Steve,

Good suggestion however the code still does not work....
This is my code so far....

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
Cells(Target.Row, "W").Formula = "=if(Or(P4='quote'];P4='closed');if
(T4<"";T4*V4);0)" & Format(Target.Row)
End If
End Sub

Any ideas?

Thanks,
Pierre

STEVE BELL wrote:
Make sure the code is in the worksheet module and not a standard macro
module...

Whenever a user changes the contents of a cell (and presses Enter or
moves
the cursor) the Worksheet_Change macro for that sheet is activated. The
[quoted text clipped - 33 lines]
Thanks,
Pierre

--
Message posted via http://www.officekb.com

problem formulas
 

Hi Tom,

As Allways, your suggestion works perfectly !
I have still one question because i do not completely understand how your
code works.
Can you eleborate on the code please:
Thanks,
Pierre

P.S. What if i want a different formula in column 'X' ?

Tom Ogilvy wrote:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.count = 1 then
If Target.Column = 4 Then
sForm = "=if(OR(P4=""quote"",P4=""closed""),if(T4<"""",T4 *V4,0),0)
Cells(Target.Row, "W").Formula = Replace(sForm,4,Target.row)
End If
End if
End Sub

Adjust the formula to reflect what you actually want to appear if the OR
condition is not met.
Hi Steve,

[quoted text clipped - 21 lines]
Thanks,
Pierre

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1