macro to output a string

[diageminc]

Hello,
[TABLE="width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Qty[/TD]
[TD][/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]P18456[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]P 18456 quantity 2[/TD]
[/TR]
[TR]
[TD]SP10458[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]SP 10458 quantity 1[/TD]
[/TR]
[TR]
[TD]SR10456-9[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]SR 10456 dash 9 quantity 2[/TD]
[/TR]
[TR]
[TD]E8986[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]E 8986 quantity 4[/TD]
[/TR]
</tbody>[/TABLE]

Please refer to the above table, we have the following formula to translate col A and Col B to the desired output
=REPLACE(SUBSTITUTE(A2,"-"," dash "),IF(ISNUMBER(VALUE((MID(A2,2,1)))),2,3),0," ")&" quantity "&B2

We are looking for a macro that can accomplish the same output instead of a formula.

Any ideas?

Thanks
Amit

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub ReplaceDashAffixQuantity()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C2:C" & LastRow) = Evaluate(Replace("IF(A2:A#="""","""",SUBSTITUTE(REPLACE(A2:A#,3-ISNUMBER(0+MID(A2:A#,2,1)),0,"" ""),""-"","" dash "")&"" quantity ""&B2:B#)", "#", LastRow))
End Sub[/td]
[/tr]
[/table]

 

[diageminc]

Give this macro a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ReplaceDashAffixQuantity()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C2:C" & LastRow) = Evaluate(Replace("IF(A2:A#="""","""",SUBSTITUTE(REPLACE(A2:A#,3-ISNUMBER(0+MID(A2:A#,2,1)),0,"" ""),""-"","" dash "")&"" quantity ""&B2:B#)", "#", LastRow))
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thanks Rick,

this is perfect.

amit