Conditional Calculation

[matpj]

hi there,

I need to code some VBA that will analyse the contents of a string field.
If it meets a certain requirement, then I need to multiply a numeric value that exists in another column on the same row.

e.g. (simplified view)

Project_Group Actuals Likelihood
Committed 112 1.00
Very Likely 98 0.85
Prospects 101 0.45


I need to search the entire range.
any rows that have "prospects" in Column A, I need to multiply the Actuals figure by the likelihood.


In reality I have 12 columns of Actuals (one for each month), which will all need multiplying by the likelihood figure.


Please can someone show me how to do this,
thanks in advance.
Matt

[Norman Jones]

Hi Mattpj,

Are the 12 monthly Actuals columns contiguous and does a given liklihood
factor apply to all Actuals columns? Are the existing Actuals to be
replaced?


---
Regards,
Norman


"matpj" <matpj.1z7j4z_1133181902.6538@excelforum-nospam.com> wrote in
message news:matpj.1z7j4z_1133181902.6538@excelforum-nospam.com...
>
> hi there,
>
> I need to code some VBA that will analyse the contents of a string
> field.
> If it meets a certain requirement, then I need to multiply a numeric
> value that exists in another column on the same row.
>
> e.g. (simplified view)
>
> Project_Group Actuals Likelihood
> Committed 112 1.00
> Very Likely 98 0.85
> Prospects 101 0.45
>
>
> I need to search the entire range.
> any rows that have "prospects" in Column A, I need to multiply the
> Actuals figure by the likelihood.
>
>
> In reality I have 12 columns of Actuals (one for each month), which
> will all need multiplying by the likelihood figure.
>
>
> Please can someone show me how to do this,
> thanks in advance.
> Matt
>
>
> --
> matpj
> ------------------------------------------------------------------------
> matpj's Profile:
> http://www.excelforum.com/member.php...o&userid=21076
> View this thread: http://www.excelforum.com/showthread...hreadid=488678
>

[matpj]

Hi Norman,

the actuals start in column T and go until Column AE.
THe values should be replaced by a value equal to the existing value mulitplied by the value in column O.

but ONLY on rows where column E contains either "Prospects" or "Unplanned Prospects"

does that make sense?

the code basically needs to scan each row, and when it finds either value in Column E, then perform the calculation...

thanks for any help,

Matt

[Norman Jones]

Hi Matt,

Try:

'================>>
Sub TestIt()
Dim rng As Range, rng2 As Range
Dim rcell As Range
Dim aCell As Range
Dim LRow As Long
Dim rngPlanned As Range
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

LRow = Cells(Rows.Count, "E").End(xlUp).Row

Set rng = Range("E2:E" & LRow) '<<==== CHANGE

For Each rcell In rng.Cells
With rcell
If LCase(.Value) = "planned pospects" _
Or LCase(.Value) = "unplanned prospects" Then
Set rng2 = .Offset(0, 15).Resize(1, 12)
rcell.Offset(0, 10).Copy
rng2.PasteSpecial Paste:=xlValues, _
Operation:=xlMultiply, _
SkipBlanks:=False, _
Transpose:=False
End If
End With
Next rcell

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
'<<================



---
Regards,
Norman



"matpj" <matpj.1z7kzn_1133184303.4909@excelforum-nospam.com> wrote in
message news:matpj.1z7kzn_1133184303.4909@excelforum-nospam.com...
>
> Hi Norman,
>
> the actuals start in column T and go until Column AE.
> THe values should be replaced by a value equal to the existing value
> mulitplied by the value in column O.
>
> but ONLY on rows where column E contains either "Prospects" or
> "Unplanned Prospects"
>
> does that make sense?
>
> the code basically needs to scan each row, and when it finds either
> value in Column E, then perform the calculation...
>
> thanks for any help,
>
> Matt
>
>
> --
> matpj
> ------------------------------------------------------------------------
> matpj's Profile:
> http://www.excelforum.com/member.php...o&userid=21076
> View this thread: http://www.excelforum.com/showthread...hreadid=488678
>