problem formulas

[Pierre via OfficeKB.com]

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 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 G15000.
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 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

 

[Gary Keramidas]

see if this works for you

Sub fill()

Dim lastrow As Long

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


End Sub

 

[Gary Keramidas]

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("G11").AutoFill Destination:=Range("G1" & lastrow), _
Type:=xlFillDefault


End Sub

 

[Guest]

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]

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

see if this works for you

Sub fill()

Dim lastrow As Long

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

End Sub

Hi,

[quoted text clipped - 22 lines]

Thanks,
Pierre

 

[Pierre via OfficeKB.com]

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

see if this works for you

Sub fill()

Dim lastrow As Long

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

End Sub

Hi,

[quoted text clipped - 22 lines]

Thanks,
Pierre

 

[Pierre via OfficeKB.com]

Hi charlie,

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

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

 

[Pierre via OfficeKB.com]

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

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("G11").AutoFill Destination:=Range("G1" & lastrow), _
Type:=xlFillDefault

End Sub

Hi,

[quoted text clipped - 22 lines]

Thanks,
Pierre

 

[Tom Ogilvy]

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

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

see if this works for you

Sub fill()

Dim lastrow As Long

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

End Sub

Hi,

[quoted text clipped - 22 lines]

Thanks,
Pierre

 

[Pierre via OfficeKB.com]

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

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

 

[Guest]

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.

Hi charlie,

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

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

 

[STEVE BELL]

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

--
steveB

Remove "AYN" from email to respond

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.

Hi charlie,

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

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

 

[Pierre via OfficeKB.com]

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

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]

 

[Gary Keramidas]

i assumed you had formulas in g11 and the code just autofilled down those
formulas for the number of rows of data you have. you could actually create
the formulas for g11 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

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

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("G11").AutoFill Destination:=Range("G1" & lastrow), _
Type:=xlFillDefault

End Sub

Hi,

[quoted text clipped - 22 lines]

Thanks,
Pierre

 

[Tom Ogilvy]

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

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

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

 

[STEVE BELL]

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

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

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

 

[Pierre via OfficeKB.com]

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' ?

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]