Help with Macro generated by ChatGP

[Bakad01]

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

[#2]

Perhaps you can paste the entire routine, rather than just one line?

[#3 · Accepted solution]

@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

[#4]

Perfect, thanks

[#5]

@Bakad01 

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.

[#6]

Thanks. The IsNumeric worked for me this time but I can see that the IsNumber option will likely be better for future use.