PDA

View Full Version : [SOLVED] Multiply cell values using worksheet change event



oldman
09-15-2013, 02:59 PM
I need to multiply two cells, column "E" x column "G" and have the answer in column "H". How do I apply the code below in order to perform this only when a value is entered in column "E". The worksheet starts at row 2 and the sub must be applied with each row entry.



Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = Range("B1").Address Then

If Range("B1") = 10 Then

Range("B1").Value = 5

Else

Range("B1").Value = 5

End If

End If


End Sub

GarysStudent
09-15-2013, 03:42 PM
Consider:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rw As Long
If Intersect(Range("E2:E" & Rows.Count), Target) Is Nothing Then Exit Sub
rw = Target.Row
Application.EnableEvents = False
Cells(rw, "H") = Cells(rw, "E") * Cells(rw, "G")
Application.EnableEvents = True
End Sub

snb
09-16-2013, 12:39 AM
Sub M_snb()
[H2:H100]=[if(E2:E100="","",E2:E100*G2:G100)]
End Sub

I wouldn't use the worksheet_change event in this case

Did you consider the formula in H2:

= if(E2="","",E2*G2)

oldman
09-16-2013, 04:12 AM
formulas in cells was my original thought but the number of rows will change depending upon the user.

oldman
09-16-2013, 04:17 AM
I believe this is what I need. Thank you.

I was in Jersey City last month to visit with my daughter. We stayed on the Hudson. What a beautiful part of the city.

oldman
09-16-2013, 11:07 AM
Sub M_snb()
[H2:H100]=[if(E2:E100="","",E2:E100*G2:G100)]
End Sub

I wouldn't use the worksheet_change event in this case

Did you consider the formula in H2:

= if(E2="","",E2*G2)

If not as a worksheet change event, where do you recommend this code should be entered?

oldman
09-16-2013, 11:12 AM
Consider:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rw As Long
If Intersect(Range("E2:E" & Rows.Count), Target) Is Nothing Then Exit Sub
rw = Target.Row
Application.EnableEvents = False
Cells(rw, "H") = Cells(rw, "E") * Cells(rw, "G")
Application.EnableEvents = True
End Sub




This executes as expected. Thank you.

My userform has a "Clear All" command button programmed to clear all data from row 8 down. The cells in column "H" do not clear but remain at "0". Is this a result of using a worksheet change event? Also, If the user changes the quantity or the retail price the change event will not make the changes.

snb
09-16-2013, 11:59 AM
I have no clue what you are after, so it's hardly possible to give a sound advice.

oldman
09-16-2013, 01:16 PM
My apologies SNB for it was not my intention to involve you regarding one other's code. I would have used your recommendation but originally did not want to limit the number of entries to the worksheet.



Sub M_snb()
[H2:H100]=[if(E2:E100="","",E2:E100*G2:G100)]
End Sub


I could use it if I knew in which module to place it and I knew how to limit the entries to a specific range based upon the following:



Private Sub lb1_Click()
Dim lIndex As Integer
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet2")

irow = ws.Cells.Find(what:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


With lb1
lIndex = .ListIndex
ws.Cells(irow, 4).Value = .List(lIndex, 1)
ws.Cells(irow, 6).Value = .List(lIndex, 3)
ws.Cells(irow, 7).Value = .List(lIndex, 5)
ws.Cells(irow, 9).Value = .List(lIndex, 6)

End With
End Sub


I need the flexibility of the user changing quantities (Column E) and/or unit retail price (column G) and the changes reflected in ext cost (columns H) and ext retail (column J).

I need the ability of the user to clear sheet 2 from row 8 downward.

snb
09-16-2013, 02:39 PM
You can use:


Private Sub lb1_Click()
With Sheets("sheet2").Cells(Rows.Count, 4).End(xlUp).Offset(1)
.Resize(, 5) = Array(lb1.List(lb1.ListIndex, 1), "", lb1.List(lb1.ListIndex, 3), lb1.List(lb1.ListIndex, 5), "=" & .Offset(, 1).Address & "*" & .Offset(, 3).Address, lb1.List(lb1.ListIndex, 6))
End With
End Sub
If you fill in the quantity the result will be obvious in column H.

oldman
09-16-2013, 03:00 PM
Thank you. I will apply and test.


If I understand correctly, data will be copied to the first available row starting at column D.

The ".Resize(,5)" is equivalent to stating there will be five columns to be filled: D, F, G, I and H as the result of " & .Offset(, 1).Address & "*" & .Offset(, 3).Address, lb1.List(lb1.ListIndex, 6)) ".

How do I append to have column "J" equal column "E" x column "I"?

oldman
09-16-2013, 05:07 PM
I believe I have it using:



Private Sub lb1_Click() 'Listbox selection to first available row
With Sheets("sheet2").Cells(Rows.Count, 4).End(xlUp).Offset(1)
.Resize(, 7) = Array(lb1.List(lb1.ListIndex, 1), "", lb1.List(lb1.ListIndex, 3), lb1.List(lb1.ListIndex, 5), _
"=" & .Offset(, 1).Address & "*" & .Offset(, 3).Address, lb1.List(lb1.ListIndex, 6), "=" & .Offset(, 1).Address & "*" & .Offset(, 5).Address)
End With
End Sub


I hope I can recall it again in the future.

Thank you again.

snb
09-17-2013, 12:31 AM
Yes, I think you got it quite right.
In the case resize(,5) means: expand the cell (e.g. D10) to the right so that the resulting range encompasses 5 columns: e.g. D10:H10.