vba IsNumeric? Cell with only numerical value

[EMcK01]

Hi,

I'm trying to search a range and do an offset based on the cell value having a numerical value only.

I've tried to use IsNumeric to find a cell with not text and use it to copy offset with the following code.

      Set rngB = Range("B3:B1000")
              For Each cell In rngB

              If IsNumeric(rngB) = True Then
              
                cell.Copy cell.Offset(-1, 9)
                cell.Offset(-1).Copy cell.Offset(-1, 6)
                cell.Offset(1).Copy cell.Offset(-1, 8)
    
            End If
            
        Next cell

I don't think I'm using the

If IsNumeric(rngB) = True Then

argument correctly but not sure exactly how to change this.

For info, the cells I'm trying to base this on is a time value that is formatted to hh:mm if that makes any difference. I also have other cells in the range that have strings with a mixture of letters and numbers so assumed IsNumeric was the best way to skip over this.

Any assistance would be appreciated.

Thanks,
Ewan

 

[Joe4]

You picked the wrong range object.
It should be:

If IsNumeric(cell) Then

btw, you do not need "= True" with boolean functions like IsNumeric. It is redundant and unnecessary.

 

[Fluff]

It should be

If IsNumeric(cell) = True Then

But IsNumeric has it flaws, have a look here

Thinking About Using VBA's IsNumeric Function? Read this first.

I have posted the following many times over the last 10 years or so in responding to old newsgroup and current forum questions. I decided to post it here for all to see. I usually try and steer people away from using IsNumeric to "proof" supposedly numeric text. Consider this (also see note...

www.excelfox.com

 

[EMcK01]

Hi both,

Thanks for the reply, it resolved why it wasn't working but I see what you mean about the flaws. When running it again with the set up I have it was picking up cells that are both a combination of letters and numbers. Something for me to think about and try and understand the link to the post you noted.

Thanks again.

 

[Fluff]

Glad we could help & thanks for the feedback.