strange behaviour from vba

hi to you all,

i have created a vba that will do a currency conversion, to be exact someone on this forum has helped me. Basically on the introduction worksheet you select the country and push on the set button and this correclty format all currency fields into the selected countries currency.

something however is seriously wrong as when i push on the set button, i get values appearing in non currency fields for some reason.

Can someone please help and tell me where i have gone wrong,

For Some reason it was not picking up the first currency field on the worksheet, so i converted the cell to a custom currency field.

Also i would ideally like the currency simbol to appear in a colum next to the set button, but when i try to add a column it messes up the entire worksheet.

really appreciate your help,

I have edited your code a little shorter and without loop !!

Sub T()

With Application: .Calculation = xlCalculationManual: .ScreenUpdating = False
End With

On Error Resume Next
Dim Intro As Worksheet
Dim data As Worksheet
Set Intro = Sheets("Introduction")
Set data = Sheets("Data_Entry")
Range("D4").Value = Application.WorksheetFunction.VLookup(Intro.Range("B3").Value, data.Range("A:B"), 2, 0)
Range("D5").Value = Application.WorksheetFunction.VLookup(Intro.Range("B3").Value, data.Range("A:C"), 3, 0)

For Each ws In ActiveWorkbook.Worksheets
         If ws.Name <> "Introduction" And ws.Name <> "Data_Entry" Then
         ws.Select
         ws.UsedRange.SpecialCells(xlCellTypeConstants, 1).Select
         Selection.NumberFormat = Intro.Range("D5").Value
             
    End If
    

Next ws

With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With

End Sub

'******************************
'Made by :- Mandeep baluja
'https://www.facebook.com/groups/825221420889809/
'https://www.linkedin.com/in/mandeep-baluja-b777bb88
Add Reputation if it helps

ok this is not working,

sorry your codes has gone and converted all fields in the excel i nall the worksheets to currency, when only specific fields are currency, some are date, some are string some are just a number.

can you please help,

i think thats why the loop existed, it was looking to find a fields that was currency and then transforming it to the selected currency,

aprreciate your help,

thanks,

worked like a charm,

So just so that i can learn something what was the problem with the previous code?

thank you so much for the help,

harris

Hey harryv27,

One thing I would like to know how can I judge which field is need to be formatted as currency and which Not, Basically according to your previous code you're just checking whether the cell is numeric and not blank, Thing I would like to share is Dates are actually number, % are actually numbers. It would be difficult for us to predict which cell need to be formatted as Currency until you confirm us. So that I can find the exact pattern to format them.

Hi Mandeep,
basically i want to see if the field is numeric, and format = currency then change to the selected currency from the drop down in the introduction tab.

the previous codes was working for some reason with the loop, but when i tried to insert it into my original excel file it didnt. here is a the file i managed to get this to work on,

when i move this macro over to the orginal file, it doesnt accept the first currency or leaves it the same, and then it adds junk characters to the bottom of each worksheet right under the table.

your help is greatly appreciated.

thanks,

Please find the attached file.I have tried this code more than 50 times, now it is working correctly, thing you should note that this attachment contains a sheet rough which need to be there if you wish You can remove that after doing your work.
This code will highlight cell in yellow which is formatted in currency format, if that suits your need, comment this line after checking
y.Interior.Color = vbYellow so that it will not mark yellow color on formatted cells.(Yellow color is just for check for right cells done)

Sub T()

With Application: .Calculation = xlCalculationManual: .ScreenUpdating = False: .DisplayAlerts = False
End With

On Error Resume Next
Dim Intro As Worksheet
Dim data As Worksheet
Dim rough As Worksheet
Set Intro = Sheets("Introduction")
Set data = Sheets("Data_Entry")
Set rough = Worksheets("Rough")
Range("D4").Value = Application.WorksheetFunction.VLookup(Intro.Range("B3").Value, data.Range("A:B"), 2, 0)
Range("D5").Value = Application.WorksheetFunction.VLookup(Intro.Range("B3").Value, data.Range("A:C"), 3, 0)

For Each ws In ActiveWorkbook.Worksheets
         If ws.Name <> "Introduction" And ws.Name <> "Data_Entry" And ws.Name <> "Rough" Then
                ws.Select
            For Each y In ws.UsedRange
            
            Debug.Print y.Address
                        If IsNumeric(y) And y.Value <> "" Then
                        rough.Range("A1").Value = "=cell(""format""," & "'" & ws.Name & "'!" & ws.Range(y.Address).Address & ")"
                                    If rough.Range("A1").Value = "C2" Or rough.Range("A1") = ",2" Then
                                           y.NumberFormat = Intro.Range("D5").Value
                                           y.Interior.Color = vbYellow
                                           rough.Range("A1").Value = ""
                                 End If
                        End If
                      Next
            End If
    Next
 
With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With

End Sub

ok you are a super star and this works great,

only one more thing, when i press on the set button, it take me to the last worksheet nq, how can i get it to just stay on the exisiting introduction worksheet?

thanks once again for the help,

Harris

Add these two line at the end of code before end sub

Intro.activate
rough.Visible = False

ok perfect that worked lovely.

another one i noticed is the worksheet st side, there is two columns that are not picking up the macro. they are HW Maint cost/year SW Maint cost/year, any ideas why its not doing these?

thanks once again

Change this line to this !!

If rough.Range("A1").Value = "C2" Or rough.Range("A1") = ",2" Or rough.Range("A1") = ",0" Then

thanks so much works like a charm.

Hi Trevor,

thanks for the reply and the explanation. works like a charm and seems to be more clean cut than the previous version.

Thanks for the help

Hi Trevor,

thanks for the reply and the explanation. works like a charm and seems to be more clean cut than the previous version.

Thanks for the help