check if range has value

[k0kster]

Hi everyone!

Im new to the forum and macros. have been using excel for a long time but very basic stuff. decided to dive deeper and gave myself a mini project.

the first step of the project, is to check whether a range in sheet 1 has any value, if true, then copy the range into sheet 2, else, check another range, if true then copy the range into sheet 3.
then, onto the next row.

this range is a column of 5 cells, and not every cell will be filled.

as i am very new, i dont know where to start.

please help!

 

[gallen]

Hello and welcome

Best way would be to create a function that returns true if any cell in the range has a value.

Does this code make sense?

Function HasValue(Target As Range) As Boolean
'this function takes a range and returns true if any of the cells in that range have a vlaue


    Dim c As Range 'Used in loop
    
    'loop through all cells and exit if a value is found
    For Each c In Target
        If c <> "" Then HasValue = True: Exit Function
    Next c
    
End Function


Sub Test()


    If HasValue(Range("A1:A6")) Then 'any cells in this range have a value?
        MsgBox "Value Found"
    Else
        MsgBox "Value Not Found"
    End If
End Sub

 

[Peter_SSs]

Same concept and Sub Test() as gallen, but perhaps this function instead.

Function HasValue(rng As Range) As Boolean
  HasValue = Not rng.Find(What:="*", LookIn:=xlValues) Is Nothing
End Function

 

[k0kster]

Thank you gallen and peter for your replies!

this is the code i used.

Function HasValue(rng As Range) As Boolean
HasValue = Not rng.Find(What:="*", LookIn:=xlValues) Is Nothing
End Function




Sub Test()




If HasValue(Range("b3:g3")) Then 'any cells in this range have a value?
Worksheets("Sheet1").Range("A3:G3").Copy Worksheets("Sheet2").Range("A3:G3")
Else
MsgBox "Value Not Found"
End If
End Sub




managed to get it to check the first row and copy the data to the next sheet.

a little more about my project.

my employer is a food manufacturer, with a factory that has 2 stories. currently, orders are received in the office, and has to be disseminated to 2 different production lines.

orders will be written in a row, that is split into 6 and 5 columns.

with the code above, i managed to check and copy the first line of orders to sheet 1 for production 1. how should i code it so that it will automatically check till the last line?