Run on matrix and find if cell is number or string (letters)

Question

I build a vba program in excel that will run on a 10X10 matrix with numbers and letters (each cell have only one of them - or letter or number) and when u find a cell with numbers turn it to blue and write number (instead of the number) when u find a cell with letters turn it to yellow and write string (instead of the number) and in the end get all the cells with numbers and calculate the average (in the if just get a sum variable and counter of the amount of number cells) the answer should be written to another worksheet - the new matrix with the colors and the words "number" and "string", and below that the average

this is my new program

Sub Num_Str_Matrix()
Sheets("Matrix2").Cells.Clear
Dim i, j As Integer
Dim Sum As Integer
Dim Counter As Integer
Dim Average As Double
Dim x As String
Sum = 0
Counter = 0
i = 1
j = 1
Do Until Sheets("Matrix").Cells(i, j).Value = ""
    j = 1
    Do Until Sheets("Matrix").Cells(i, j).Value = ""
        x = Sheets("Matrix").Cells(i, j).Value
        If IsNumeric(x) Then
            Sum = Sum + Sheets("Matrix").Cells(i, j).Value
            Counter = Counter + 1
            Sheets("Matrix2").Cells(i, j).Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent5
                .TintAndShade = 0.599993896298105
                .PatternTintAndShade = 0
                ActiveCell.FormulaR1C1 = "Number"
            End With
        Else
            Sheets("Matrix2").Cells(i, j).Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent4
                .TintAndShade = 0.599993896298105
                .PatternTintAndShade = 0
                ActiveCell.FormulaR1C1 = "String"
            End With
        End If
    j = j + 1
    Loop
i = i + 1
Loop
Average = Sum / Counter

End Sub

the program for some reason that I can't figure wont go to the next loop t will work on the first row and then stop. and now it wont even work because of this Error: "Select method of Range class failed"

Please help me.

You might benefit from reading [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/a/284237/3219613) • And [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • Note that `Range(i & j)` resolves to something like `Range(11)` which is not a valid range address and two times the same loop `Do Until Sheets("Matrix").Range(i & j).Value = ""` does not make any sense — Pᴇʜ, Nov 09 '20 at 08:27
You can do that much faster without a loop. Search for `SpecialCells` — iDevlop, Nov 09 '20 at 08:28
If you write `Range(i & j)`, VBA will concatenate `i` and `j`. This will result in `11` and that is not a valid range. What you probably mean is `Sheets("Matrix").cells(i, j).Value` — FunThomas, Nov 09 '20 at 08:35
@FunThomas thank u for the "Cell" Method it worked and I wrote it into another sheet. the only problem I have now is why it wont go down 1 row after it finishes with all the columns — Daniel Lichtenstadt, Nov 09 '20 at 09:44
I did the changes.. but it still doesn't work. now it shows me this Error: "Select method of Range class failed via VBA, Error 1004" and I don't know why (it worked till now) this is my program after the changes Do Until Sheets("Matrix").Cells(i, j).Value = "" j = 1 Do Until Sheets("Matrix").Cells(i, j).Value = "" x = Sheets("Matrix").Cells(i, j).Value If IsNumeric(x) Then Sheets("Matrix2").Cells(i, j).Select — Daniel Lichtenstadt, Nov 09 '20 at 12:28

score 0 · Answer 1 · answered Nov 09 '20 at 13:09

0

A Tricky Double Do Loop

A Quick Fix

Option Explicit

Sub Num_Str_Matrix()
    Dim i As Long, j As Long
    Dim x As Variant
    i = 1
    j = 1
    x = Sheets("Matrix").Cells(i, j).Value
    Do
        Do
            If IsNumeric(x) Then
                With Cells(i, j)
                    .Value = "Number"
                    With .Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorAccent5
                        .TintAndShade = 0.599993896298105
                        .PatternTintAndShade = 0
                    End With
                End With
            Else
                With Cells(i, j)
                    .Value = "String"
                    With .Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorAccent4
                        .TintAndShade = 0.599993896298105
                        .PatternTintAndShade = 0
                    End With
                End With
            End If
            ' Debug.Print Cells(i, j).Address, Cells(i, j).Value
            j = j + 1
            x = Sheets("Matrix").Cells(i, j).Value
        Loop Until x = ""
        j = 1
        i = i + 1
        x = Sheets("Matrix").Cells(i, j).Value
    Loop Until x = ""

End Sub

answered Nov 09 '20 at 13:09

VBasic2008

44,888
5
17
28

Thank u very much for this answer (I will use it) but I still wanna know why mine isn't working and also the changes need to happen in another sheet so I guess instead: With Cells... it will be With Sheets("Sheet2").Cells... right? – Daniel Lichtenstadt Nov 09 '20 at 13:17
why this code isn't working??? Sheets("Matrix2").Cells(i, j).Select – Daniel Lichtenstadt Nov 09 '20 at 13:30
I did what u said about ws1= thisworkbook.worksheets("Matrix") (I added Dim ws1 As Worksheet) - but it didnt work (Object variale or with block variable not set, for the row ws1 = ...) and I added the As int to i – Daniel Lichtenstadt Nov 09 '20 at 13:39
I didn't use Select anywhere in the code because it is to be avoided (see the first comment you got on your post). But if you need to additionally add it to the code at the end, you have to activate first to be sure: `ThisWorkbook Worksheets("Matrix2").Activate`. Now you can safely select. – VBasic2008 Nov 09 '20 at 13:40
and also I have a reset for j I added it after the first loop ends – Daniel Lichtenstadt Nov 09 '20 at 13:40
You have to first `Dim`, and only then you can use the variable. If you're trying to rewrite (repair) your own code, you have to study (understand) the lines after `End If` in my code first. They are there, and not at the beginning, for a reason. – VBasic2008 Nov 09 '20 at 13:44
I'm new to VBA so its all just really confusing I'm trying to understand why this line isn't working "Sheets("Matrix2").Cells(i, j).Select" and after that there is "With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent4 .TintAndShade = 0.599993896298105 .PatternTintAndShade = 0 ActiveCell.FormulaR1C1 = "String" End With" because it suppose to be written into another sheet and not the primary – Daniel Lichtenstadt Nov 09 '20 at 13:50
You can only select something in the `ActiveSheet`. If you have selected worksheet `Matrix` and run the code, it will fail. You have to select worksheet `Matrix2` (select it in Excel), or as I already mentioned, use `Sheets("Matrix2").Activate` before `Sheets("Matrix2").Cells(i, j).Select`. – VBasic2008 Nov 09 '20 at 13:55
The problem is that you didn't reset the j anywhere. So when it became 11 it stayed 11 (11th column = empty cells). Qualifying the worksheets is always a good idea. But you can do it before the loops: Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Worksheets("Matrix") and Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Worksheets("Matrix2"). Then you only use ws1 and ws2 and the code becomes more readable. Note that when declaring variables in one line you have to have an As for each variable e.g. Dim i As Integer, j As Integer. – VBasic2008 Nov 09 '20 at 14:03
I forgot to use the `Set` keyword: it should be `Set ws1 = ...` and `Set ws2 = ...` as I have corrected it now in the previous comment. Sorry. – VBasic2008 Nov 09 '20 at 14:06

Run on matrix and find if cell is number or string (letters)

1 Answers1

A Tricky Double Do Loop