How to replace alphabet by a number using VBA?

Hi folks,
I am new to vba. I want to substitute all "a"s in the worksheet by number 1 wherever a cell contains "a" in the spreadsheet. Note that these "a"s are actually generated by vba code from another worksheet. your help in this will be very much appreciated. Please see the attached test.xlsm file.

Those "a" s are actually somehow exported from "sheet2" to "sheet1" using vba.

Many thanks
Prad

Please Login or Register  to view this content.

Hi AB thanks for the response and it really seems working; however, I only want to find single "a" in the whole sheet and replace those by numeric 1 ...would it be possible? with your formula it changes all "a"s wherever it finds including from words ..

Originally Posted by meprad

I only want to find single "a" in the whole sheet and replace those by numeric 1

try

Please Login or Register  to view this content.

Thanks a lot Jindon...it works perfect !!