Conditional Calculation in an existing field 1

[t16turbo]

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.

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

 

[CautionMP]

Not sure you need a macro, this can be done with a formula assuming your worksheet looks like this:

[tt] A B C D
1 Project_Group Actuals Likelihood
2 Committed 112 1.00
3 Very Likely 98 0.85
4 Prospects 101 0.45[/tt]

In Column D use the following formula (I started in row 1):[tt]
=IF(A1="Prospects",B1*C1,"")[/tt]
Then copy it all the way down the column.

Hope this helps,
CMP

Instant programmer, just add coffee.

 

[t16turbo]

thats the thing, I need it in the same cells as the existing values - as there are other macros that read the values in their current position.

and a macro is used to put them in this current format initially.

rather than go through and recode the subsequent macros, I was hoping for a new module I can call which can cater for my requirement..

 

[GlennUK]

Hi t16turbo, you'd best set the likelihood to 1 after the code has run on the Prospects, so that duplicate runs won't do any damage.

For Each c In Range("A2",Range("A65536").End(xlUp))
   If c.Value="Prospects" Then
      c.Offset(0,1).Value= c.Offset(0,2).Value * c.Offset(0,1).Value
      c.Offset(0,2).Value=1 ' set likelihood to 1
   End If
Next

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[CautionMP]

Where should the new value be written?

Instant programmer, just add coffee.

 

[t16turbo]

Glenn,
you're a star! thats perfect!

i've altered this to apply to my exact spreadsheet.
In reality there are 12 columns of values, representing 12 months.
I just included a line for each of them, incrimenting the offset by 1 every time - and it works like a charm.

 

[GlennUK]

Great! Glad that was so useful to you.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.