I'm trying to pass data from sheet 3 to sheet 4 based on a criterion (*). With numbers results but with text the program fails.
How to overcome this situation when instead of a number I have text.

Public Sub TestArray3()

    'Array to copy data from Sheet3 to Sheet4 Based on criterion "in this case*"

    Dim tempVar As Integer, anotherIteration As Boolean, i As Integer

    Dim J As Integer, ArraySize As Integer, myArray() As Integer

    Dim newArray() As Integer, FinalRow As Integer, linha As Integer

    Dim counter As Integer, cel1 As Range



    Sheets("Folha3").Select

    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row ' Find the last row of data

    ArraySize = FinalRow 'Get Array Size

    ReDim myArray(ArraySize - 1)

    For linha = 1 To FinalRow

        Set cel1 = Cells(linha, 1)

        If cel1 = "*" Then

            myArray(linha - 1) = Val(Cells(linha, "B").Value) 'Populate de Array

        End If

    Next linha



    ReDim newArray(LBound(myArray) To UBound(myArray)) 'Avoid zeros in Array

    For i = LBound(myArray) To UBound(myArray)

        If myArray(i) <> "0" Then

            J = J + 1

            newArray(J) = myArray(i)

        End If

    Next i



    ReDim Preserve newArray(LBound(myArray) To J)

    ArraySize = J

    Sheets("Folha4").Select 'Write data to Sheet 4 column A

    Range("A1").Resize(J - LBound(newArray) + 1)=Application.Transpose(newArray)

End Sub

I'm not clear on where you're actually trying to paste from/to, but here's one [of several] ways to move data between worksheets, including both with and without transposing

Hopefully this example should clear up the steps:

Sub copyRangeToOtherSheet()



    Dim lastRow As Long, lastCol As Long, rgSrc As Range, rgDest As Range, arr() As Variant



    With ThisWorkbook.Sheets("Sheet1")                               'set source worksheet



        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row              'find last row of Col A

        lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column      'find last col of Row 1

        Set rgSrc = Range(.Range("A1"), .Cells(lastRow, lastCol))    'create range (from A1)



    End With



    arr = rgSrc                                                      'dump range into array



    With ThisWorkbook.Sheets("Sheet2")                               'set destination sheet



      'OPTION #1: Populate destination in "original" orientation

        Set rgDest = .Range("A1")                           'set destination top-left corner

        Set rgDest = rgDest.Resize(UBound(arr, 1), UBound(arr, 2))  'fit to array rows/col's

        rgDest = arr                                          'dump array to worksheet range



      'OPTION #2: Populate destination in "transposed" orientation

        Set rgDest = .Range("D1")                           'set destination top-left corner

        Set rgDest = rgDest.Resize(UBound(arr, 2), UBound(arr, 1))  'fit to array col's/rows

        rgDest = WorksheetFunction.Transpose(arr)  'dump transposed array to worksheet range



    End With



End Sub

Note that it's easiest if you don't set the size of the array in advance — Excel will size it automatically as long as the array isn't already dimensioned (which is why it's declared only as arr() As Variant).

On the destination end, we can pick one cell as the top-left of the range, then ReSize the range based on the arrays' upper bounds (UBound).

If we are going to Transpose the cells, we must swap the number of rows/columns in the destination range.

More Information:

• One resource I've found very helpful is Chip Pearson's VBA Arrays And Worksheet Ranges.

String vs Integer

It is a little unclear what is happening here, but I have noticed that you have declared all your arrays as integer so you cannot pass strings to them. Try to find out which array you're trying to pass strings to and declare it as variant or implement some 'conditional' code like:

If Not IsNumeric(Cells("A1").Value) then

  Variable = 0

End If  

Read ashleedawg's guidelines.

You don't have to select a worksheet to do stuff to it (referring to Select). You can write

FinalRow = Sheets("Folha3").Cells(Rows.Count, 1).End(xlUp).Row

or

Sheets("Folha4").Range("A1").Resize(J - LBound(newArray) + 1) _

= Application.Transpose(newArray)

and save a line but more importantly, not jump around in the workboook. Even better is using With:

With Sheets("Folha3")

    FinalRow = .Cells(Rows.Count, 1).End(xlUp).Row ' Find the last row of data

    ArraySize = FinalRow 'Get Array Size

    ReDim myArray(ArraySize - 1)

    For linha = 1 To FinalRow

        Set cel1 = .Cells(linha, 1)

        If cel1 = "*" Then

            myArray(linha - 1) = Val(.Cells(linha, "B").Value) 'Populate de Array

        End If

    Next linha

End With

Notice the '.' in front of each cells (.cells), it is referring to the sheet object.

Try using variables for objects. When you write

Sheets("folha3"). 

nothing happens you have to remember what it can do. But if you assign it to a variable the intelliSense is activated and you can see the properties and methods of objects e.g.

Dim oWb as Workbook

Dim oWs as Worksheet

Set oWb = Activeworkbook

Set oWs = oWb.Sheets("Folha3")

Now when you write:

oWs.

the IntelliSense shows you the properties and methods of the worksheet object e.g. Activate, Cells, Copy, Delete, Paste etc.

With a few more lines of code you will learn much more.