In an excel sheet column there are entries with %, $ symbols that are coming as the cell's number format while some entries have %, $ symbols explicitly entered in the cells (not as number format). Also there are cells with a mix of alphabets, numbers and special symbols and white space.

I am looping over these cells one by one and trying to put to a different range. I am trying to fill the destination cell with the currentlocation cell's exact value being shown in the cell (including number format) as string value.

The problem I am facing is that in the destination cells the displayed value is not matching correct to the current location cell. I have tried-

DestinationCell.Value = CurrentLocationCell.Text 
DestinationCell.NumberFormat ="@"

But it didn't work, I wanted to somehow use

DestinationCell.Text = CurrentLocationCell.Text 

but VBA throws an exception if "DestinationCell.Text" is used. Thanks in advance for any help.

CodePudding user response：

From the Range.Text documentation:

Returns the formatted text for the specified object. Read-only String.

Try flipping the order: first change the number format of the destination cell to Text, then write the CurrentLocationCell.Text

DestinationCell.NumberFormat ="@"
DestinationCell.Value = CurrentLocationCell.Text