Forum Discussion
Help with Macro generated by ChatGP
Hi,
I'm not a programmer so turned to ChatGP for help. My first couple of queries returned macros that worked but then I refined the search to be more accurate and ran into an issue. This line is being rejected by Excel but I can't figure out why;
If WorksheetFunction.CountIf(ws.Cells(i, "B").Value, "*[a-zA-Z]*") = 0 Then
Excel returns an error 'Run-time error 424' Object required
The macro is designed to select any cells with numbers and move them from column B to D. The line above is to confirm if there are any letters contained and then ignore them. Variable i is the line number that increments.
Any help appreciated
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
5 Replies
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.- Thanks. The IsNumeric worked for me this time but I can see that the IsNumber option will likely be better for future use.
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
- Perfect, thanks
- Perhaps you can paste the entire routine, rather than just one line?
