PDA

View Full Version : Code for multiplying 2 values based on search



Johnatha
01-20-2016, 11:47 AM
Hi everyone,

I have somewhat of a strange task... I'm looking for VBA code to multiply 2 numbers beside each other. However, it needs to be done based on a certain range of cells.



P

12

22




EEOG

2





X

10

65




X

61

62




EEOG

3





L


26




L






K

5

56




EEOG

5





L






EEOG

10





A

11

12




A






H

2

5




EEOG

20






For example, I'm looking for code that will search column A in the table above for string "EEOG". Then, for each string within the parameters of "EEOG" I want the 2 values beside it multiplied, and put in column 4. So, the resulting table would look like this...



P

12

22




EEOG

2





X

10

65

650



X

61

62

3782



EEOG

3





L


26




L







K

5

56

280



EEOG

5





L






EEOG

10





A

11

12

132



A







H

2

5

10



EEOG

20






I was thinking of somehow incorporating the End(xlDown) function to search for the range and fill in an equation, but notice how some values between "EEOG" are blank.

Any help or feedback on this would be greatly appreciated.

Thanks in advance!! :cool:

SamT
01-20-2016, 12:40 PM
Column D Formula is =IF(A<>"EEOG",B*C,"")

Johnatha
01-20-2016, 01:10 PM
@SamT could you wrap the "ISERROR" function in there? I'm trying to add it with little success.

*Edit...

Found it! :)

=IF(ISERROR((IF(A1<>"EEOG",B1*D1,""))),"",IF(A1<>"EEOG",B1*D1,""))

SamT
01-20-2016, 01:31 PM
=IF(ISERROR(B1*C1),"",IF(A1<>"EEOG",B1*C1,""))

If B1*C1 Fails Then nothing Else original Formula