For each cell - if IsNumber - return if no number found

[Barsoe]

Hi

My question is in the code

Public Sub findforeach()
Dim ws As Worksheet
Set ws = ActiveSheet
' how do I return these :
' if no cell in below range with number value is found then
' if below if statement is true for the first number value found (most likely in A45) then
Dim Cell As Range
For Each Cell In Range("A45:a61")
If Application.WorksheetFunction.IsNumber(Cell.Value) = True And Cell.Offset(0, 37) = "" Then
    MsgBox ("Found number " & Cell.Value)
    Cell.Offset(0, 37) = "username & date"         
    Else
    End If
Next Cell
End Sub

 

[rollis13]

If I correctly understood your request, have a try:

Option Explicit
Public Sub findforeach()
    Dim ws     As Worksheet
    Set ws = ActiveSheet
    ' how do I return these :
    ' if no cell in below range with number value is found then
    ' if below if statement is true for the first number value found (most likely in A45) then
    Dim Cell   As Range
    If WorksheetFunction.Count(Range("A45:A61")) > 0 Then '<- added
        For Each Cell In Range("A45:A61")
            If WorksheetFunction.IsNumber(Cell.Value) = True And Cell.Offset(0, 37) = "" Then
                MsgBox ("Found number " & Cell.Value)
                Cell.Offset(0, 37) = "username & date"
            Else
            End If
        Next Cell
    Else                                          '<- added
        MsgBox ("No numbers found")               '<- added
    End If                                        '<- added
End Sub

 

[Barsoe]

Appreciated!
However, not working as intended.

Say I have in Column A two number values
A45 = 4555
A47 = 5652
AL45 and AL47 (offset 0, 37) are blank
I run my code, both numbers are found and AL45+AL47 will have added text
When I run it again, it will do nothing, as Cell.Offset(0, 37) = "" is no longer true.

Your code, however, still finds the number values in A45 and A47, and therefore the Else statement is not triggered

 

[Barsoe]

Fixed it based on your input!
thank you

Option Explicit
Public Sub findforeach()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ' how do I return these :
    ' if no cell in below range with number value is found then
    ' if below if statement is true for the first number value found (most likely in A45) then

Dim x As Integer                                ' Added
x = WorksheetFunction.Count(Range("b45:b61"))   ' Added
    Dim Cell   As Range
    If WorksheetFunction.Count(Range("A45:A61")) > x Then  '<- added - (replaced with x)
        For Each Cell In Range("A45:A61")
            If WorksheetFunction.IsNumber(Cell.Value) = True And Cell.Offset(0, 37) = "" Then
                MsgBox ("Found number " & Cell.Value)
                Cell.Offset(0, 1) = 1                               ' Added - 1 to be replaced with code for entry ID
                Cell.Offset(0, 37) = Application.userName & Date
      
            Else
            End If
        Next Cell
    Else                                          '<- added
        MsgBox ("No new numbers found")               '<- added
    End If                                        '<- added
End Sub

 

[Barsoe]

so...
FIXED: ' if no cell in below range with number value is found then
STILL UNSOLVED: ' if below if statement is true for the first number value found then........

 

[rollis13]

STILL UNSOLVED: ' if below if statement is true for the first number value found then....

I'm sorry but I just can't understand the request. Do you mean "if the first number found is in A45 (and without timestamp in AL45) ... do something else..." ?
Please try to explain again with other words.

 

[bebo021999]

Is it what you mean:
1) Before 1st run: A45 is number, AL45 is blank
2) After 1st run: A45 is still number, AL45 is text "username & data
3) A45 is changed from value to text (by user input ?) then AL45 should be blank again
4) A45 is changed from text to value (by user input ?) then AL45 should be text "username & data again
Is it true?

 

[Barsoe]

Is it what you mean:
1) Before 1st run: A45 is number, AL45 is blank YES
2) After 1st run: A45 is still number, AL45 is text "username & data YES
3) A45 is changed from value to text (by user input ?) then AL45 should be blank again NO
4) A45 is changed from text to value (by user input ?) then AL45 should be text "username & data again NO

I'm sorry but I just can't understand the request. Do you mean "if the first number found is in A45 (and without timestamp in AL45) ... do something else..." ?
Please try to explain again with other words.

I

editing this post....

 

[Barsoe]

didn't edit fast enough

In column "A" I have cells with number values and cells with text values. They are in no specific order.
Number values represent product codes and text values represent costs of various kind.

All data are transferred to another workbook with 1 row per product code - transfer shall only be possible 1 time (so in terms of A45=number then AL45 remains with "Application.userName & Date" telling the user, that this has been transferred)
Some costs need to be added for each product code....... these costs will always be in the row directly below the product line, so I use offset(1, column) for this.

if some products are transferred and another product line is added, this can then be transferred seperately.... since it doesn't have the username&date stamp yet.
All this works just fine now!

Challenge is
Other costs are not directly related to each product code. They are of a more general nature, and should only be transferred together with the first product code found from the top of the range.

I hope I am making sense now?

 

[rollis13]

All this works just fine now!

So, this to me means that this topic has been solved.
I believe that for any other question/challenge you should tidy up your macro and with it start a new thread with new topic. We are supposed to resolve single questions, not entire projects.