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
DeathByExcel - May 25, 2012 at 01:36 AM
Related:
- Auto-Formatting
- Grand theft auto v free download no verification for pc - Download - Action and adventure
- Facebook auto refresh - Guide
- Grand theft auto iv download apk for pc - Download - Action and adventure
- Nvidia drivers auto detect - Guide
- Auto redial samsung - Guide
4 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
May 24, 2012 at 12:31 AM
May 24, 2012 at 12:31 AM
right click sheet tab and click view code
copy this event code
NOTE. YEN SHOULD BE WRITTEN IN CAPS
test the above event 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
.
DeathByExcel
Posts
2
Registration date
Wednesday May 23, 2012
Status
Member
Last seen
May 24, 2012
May 24, 2012 at 01:02 AM
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.
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.
Mehedad
Posts
22
Registration date
Thursday April 19, 2012
Status
Member
Last seen
April 16, 2013
3
May 24, 2012 at 01:38 AM
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
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
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.
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.
