Previous Topic Next Topic

Auto-Formatting

Closed
DeathByExcel Posts 2 Registration date Wednesday May 23, 2012 Status Member Last seen May 24, 2012 - May 23, 2012 at 09:37 PM
 DeathByExcel - May 25, 2012 at 01:36 AM
Hello, all!

I hope for some advice to a problem I've been stuck with to perform manually and getting rather sick of it.

May I know if it is possible and how to perform the following?

If A1='YEN', value in B1 has no decimal, else it will have 2 decimal places.

Also, inversely, if value in B1 has no decimal, C1 shows "Japanese Yen".

Please help!
Related:

4 responses

Answer 1 / 4
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
May 24, 2012 at 12:31 AM
right click sheet tab and click view code
copy this event code 



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
If Target = "YEN" Then
Target.Offset(0, 1).NumberFormat = "0.00"
Else
 Target.Offset(0, 1).NumberFormat = "0"
End If
If Target.Offset(0, 1).NumberFormat = "0" Then
Target.Offset(0, 2) = ""
Else
Target.Offset(0, 2) = "JAPANES YEN"
End If
Application.EnableEvents = True
End Sub



NOTE. YEN SHOULD BE WRITTEN IN CAPS
test the above event code
.
0
Answer 2 / 4
DeathByExcel Posts 2 Registration date Wednesday May 23, 2012 Status Member Last seen May 24, 2012
May 24, 2012 at 01:02 AM
Sorry, venkat1926.

The code is logical (as in, there is no error) but it could not perform what I require. It's possible that there are too many other numerical data and texts in the sheet that caused it to be unable to function.

Thanks for the assistance anyway.
0
Answer 3 / 4
Mehedad Posts 22 Registration date Thursday April 19, 2012 Status Member Last seen April 16, 2013 3
May 24, 2012 at 01:38 AM
I think Vankat's one should work fine, I am not a pro, but this is what I was doing, and it worked.

Sub DecimalPointer()
Dim Cur As Range
Dim itemCount As Long
Set Cur = Range("A:A")
itemCount = Range("A1").CurrentRegion.Rows.Count
For i = 1 To itemCount
If Cur(i, 1) = "YEN" Then
Cells(i, 2).NumberFormat = "0"
End If
If Cur(i, 2).NumberFormat = "0" Then
Cells(i, 3).Value = "Japanese Yen"
End If
Next i
End Sub
0
Answer 4 / 4
DeathByExcel
May 25, 2012 at 01:36 AM
Thanks, Mehedad.

I'm still getting the same problem except that, now, the fields that were supposed to show no decimal places have 2 decimal places instead.

I've cleaned up the file to reduce amount of texts and numerals.

My input field for "YEN" or "USD" is now in Cell E8.

I only wish the formatting to be applied to Cells B4 and D9 to D14.

If there is still no way to alter the situation, I'll just have to amend it manually.

Once again, thank you guys so much for assisting.
0