Forum Discussion
Help with Macro generated by ChatGP
Bakad01 The Run-time error is occurring because the first argument of COUNTIF is expecting a Range and you're passing it a value. Having said that, the formula will not work as expected anyways, because the criteria "*[a-zA-Z]*" is searching for the literal text string "[a-zA-Z]", NOT for each letter in the alphabet individually.
If the objective is to identify cells in column B that contain numbers only, try this instead:
If IsNumeric(ws.Cells(i, 2).Value) Then
Note that VBA's function IsNumeric will not always act as you might expect. As you can see here and in the attached workbook:
- Text values are not copied. That includes an empty string in a cell, as in row 3.
- Simple numbers are copied.
- Excel errors are not copied.
Those are as expected. However:
- Boolean values are copied, as their underlying representation is a number (0 or 1).
- Empty cells are copied.
- Times are copied, as their underlying representation is a number (typically 0 to just under 1).
- Dates and date-times are not copied, even though their underlying representation is a number. (The last four rows in the example have the same numeric value in column B, but with different formatting.)
As you can see in columns F and G, the VBA alternative of using WorksheetFunction.IsNumber gives different results for Boolean values and empty cells.
