Problem with multiplying within a cell.

Hi,

I am trying to work out how to multiply certain fields on a spreadsheet based on the value of a target cell. For example:

D2= 56 (however this value will change on a daily basis)
Value entered into D23 is 2, however will automatically change to 112.

I need this to happen in numerous cells.

I am unable to add attachments at the moment, however below is a screen shot of what im working with:



If you require the file then please feel free to ask.

Any help would be much appreciated.


Regards,

John.

What have you tried ?

NoS said:

What have you tried ?

Click to expand...

I am not amazing when it comes to Excel, but i believe this may be a VBA issue. I have tried googling cell multiplication VBA codes, however they tend to deal with a constant value. My problem is that the cell's will not be multiplied by a constant value, as the value will change every night.

I have tried self-editing code such as these:


PrivateSub Worksheet_Change(ByVal Target As Range)If Target.Column =1Then intcolumn = Target.Column introw = Target.Row Cells(introw, intcolumn)= Cells(introw, intcolumn)*"12"EndIfEndSub

*Changing the "Target.Column" to the yellow cell locations and the "12" to D2* but that didn't work.

and


Private Sub Worksheet_Change(ByVal Target As Range)Const WS_RANGE As String = "H1:H10" '<== change to suitConst SET_VALUE As Long = 23 '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If IsNumeric(.Value) Then .Value = .Value * SET_VALUE End With End Ifws_exit: Application.EnableEvents = TrueEnd Sub

*Changing the "WS_RANGE* to the cells i need to multiply and the "SET_VALUE" to D2* but this too didn't work.

I have also seen that copying and "paste special" can work, but it's not practical when considering the amount of cells i need to be able to multiply within.

Hope this helps.

*** For some reason it won't upload the code in the correct format when i paste it into the body of the message :S ***

Don't really know what you're trying to do.
Maybe this re-arrangement of the second sub ?



Info for attaching a file
http://www.excelguru.ca/forums/faq.php?faq=vb3_reading_posting#faq_vb3_attachments

NoS said:

Don't really know what you're trying to do.
Maybe this re-arrangement of the second sub ?

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim WS_RANGE As Range
    Dim SET_VALUE As Long
    
Set WS_RANGE = Range("H1:H10") '<== change to suit
SET_VALUE = Range("D23").Value '<== change to suit

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, WS_RANGE) Is Nothing Then
    With Target
        If IsNumeric(.Value) Then .Value = .Value * SET_VALUE
    End With
End If
ws_exit:
    Application.EnableEvents = True
End Sub

Info for attaching a file

Click to expand...

AHA! I have managed to add my file.

Right, i am going to try an attempt to explain what i need, so bear with me here..

I work for a printers, each night we produce bundles of paper which vary size each night. This figure is represented in the drop down menu in D2.

The tables labelled "Thurrock Run-On's", "Didcot Run-On's", and "Oldham Run-On's" will display any mixed editions.

A figure will be entered into the Figure column representing the locations supply. The figures which appear in the yellow will represent the mixed editions.

But what i would like is.. If i am told that Newmarket for instance has 10 bundles of one edition and 20 of another, i want to enter those values into a cell and it automatically multiply with the value in D2. eg:

D2= 40

Value entered into D24 is 4. This is then multiplied with the 40 to make 160.

I need this same function to happen in all the yellow cells.

I hope this sheds a little more light onto my issue.

Regards,

John.

John, please don't quote entire posts, it's not necessary and just clutters things up.

I think this does what you're wanting

:O That works perfectly!!! I can't thank you enough!

Cheers NoS!! :bounce:

Glad I could help.