Help with building double if statement

[sobieskitech]

Hello all,

I've looked for solutions to this is the Forum and have probably just missed it.

With the example below I would like to do the following:
1. Look in column D and if the first 3 characters of the cell is BAL then,
2. Look in column C and determine if the character preceding the dash is a letter
3 If the last 2 statements are true then replace the "-00" with ""
like this

Selection.Replace What:="-00", Replacement:=""

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]301640A-00[/TD]
[TD]BAL_OS361D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]301501B-00[/TD]
[TD]BAL_OS361F[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]20392C-00[/TD]
[TD]BAL_OS366B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ASGQHAR01001-00[/TD]
[TD]BAL_OS363F[/TD]
[/TR]
</tbody>[/TABLE]


then continue looping till the end of the page.

Thanks in advance for any help

 

[steve the fish]

See if this works. Test on a copy as its going to overwrite your data:

lr = Range("C" & Rows.Count).End(xlUp).Row
 
Set x = Range("C1:C" & lr)
x = Evaluate(Replace("IF(ROW(1:" & lr & "),IF(ISERROR(MID(@,FIND(""-"",@)-1,1)+0),IF(LEFT(OFFSET(@,0,1),3)=""BAL"",SUBSTITUTE(@,""-00"",""""),@),@))", "@", x.Address))
Range("C1:C" & lr) = x

 

[sobieskitech]

Thanks steve the fish! Executed flawlessly!!

(now to try to break it all down in hopes to learn how it does what it does)