VBA Code that runs through all cell types

[yaacoubp]

Hello VBA Masters! Peter here and I need some help with my code pleaaase.

I am trying to translate spreadsheets from Arabic to English. I have made a list of all the words in Arabic (Column A) and their equivalent in English (Column B) in a worksheet called "Descriptors".

The issue is that the code ignores cells that contain a text value but linked to an external workbook. I have tried SpecialCells(xlCellTypeAllFormatConditions) but it doesn't seem to work.

I have attached a screenshot of the code I have. I appreciate any comments or thoughts on this.

Thank you,

Peter

 

[6StringJazzer]

cells that contain a text value but linked to an external workbook.

A cell can contain a text value, or it can have a formula that links to an external workbook, but not both. Do you mean that it displays a text value that is linked to an external workbook?

Replace will not work on the results of a formula, only cell values or the content of the formula itself.

If you want to do this using VBA, and some of the cells you want to translate have formulas instead of values, you will have to loop through each cell for a match instead of using Replace.

 

[yaacoubp]

A cell can contain a text value, or it can have a formula that links to an external workbook, but not both. Do you mean that it displays a text value that is linked to an external workbook?

Replace will not work on the results of a formula, only cell values or the content of the formula itself.

If you want to do this using VBA, and some of the cells you want to translate have formulas instead of values, you will have to loop through each cell for a match instead of using Replace.

Hi StringJazzer,

Thank you for your response.

Yes I mean some of my cells display a text value that is linked to an external workbook.

I am not a VBA expert. I have tried doing this project using Vlookups but it takes too long looking at the amount of worksheets and variations I have.

Any suggestion on how to make it loop through all cells instead of Replace?

Thanks in advance,

Peter

 

[6StringJazzer]

Please paste your actual code instead of a picture of your code.

Also I highly recommend using code tags when pasting code. After you have pasted your code, highlight it then click the "VBA" button at the top of the edit window to mark it.

 

[yaacoubp]

Sub NewTranslator()


    Const lang1 As Long = 1
    Const lang2 As Long = 2
    Dim arr1    As Variant
    Dim arr2    As Variant
    Dim i       As Long
    Dim lr      As Long
    
    With ActiveWorkbook.Worksheets("Words")
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        arr1 = .Range(.Cells(2, lang1), .Cells(lr, lang1))
        arr2 = .Range(.Cells(2, lang2), .Cells(lr, lang2))
    End With
    
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            Select Case (ws.Name)
                Case "Words"
                Case Else
                  For i = 1 To lr - 1
            .Cells.Replace What:=arr1(i, 1), Replacement:=arr2(i, 1), LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        Next
    End Select
End With
Next
End Sub

 

[6StringJazzer]

Your code does not compile due to structure errors (mismatched With, For)

Here is what I think will work but I can't test it without your actual file. I think the easiest and fastest way to do this is just to convert all the formulas to values then run your existing code. I assumed that everything was just text. If you have formulas that must be preserved, then we need a more complicated solution that checks each individual cell for a text match, and if it finds one, converts that cell from a formula to text to change the word.

Sub NewTranslator()


   Const lang1 As Long = 1
   Const lang2 As Long = 2
   Dim arr1    As Variant
   Dim arr2    As Variant
   Dim i       As Long
   Dim lr      As Long
   Dim ws      As Worksheet
   
   
   With ActiveWorkbook.Worksheets("Words")
      lr = .Cells(.Rows.Count, 1).End(xlUp).Row
      arr1 = .Range(.Cells(2, lang1), .Cells(lr, lang1))
      arr2 = .Range(.Cells(2, lang2), .Cells(lr, lang2))
   End With
   
   For Each ws In ActiveWorkbook.Worksheets
      If ws.Name <> "Words" Then
         With ws
            .UsedRange.Value = .UsedRange.Value
            For i = 1 To lr - 1
               .Cells.Replace What:=arr1(i, 1), Replacement:=arr2(i, 1), LookAt:=xlWhole, _
                              SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                              ReplaceFormat:=False
            Next i
         End With
      End If
   Next ws

End Sub

 

[yaacoubp]

Hey Jeff,

I have tried the modified code and it works perfectly. Thank you.

I do not need to preserve the formula that displays a text value, I just wanted to replace them with their equivalent in another language.

Also, any way to include chart titles in the translation process or is it better done through macros?

Thanks again for the help.

Peter

 

[6StringJazzer]

It would be possible to include chart titles but it's quite different than the code above and I would have to dig into it to refresh my memory of how it works. If I can find the time I will get back to you.

 

[yaacoubp]

Thank you so much for your reply and help.