check if range has value

k0kster

New Member
Joined
Sep 3, 2019
Messages
2
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!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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?

Code:
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
 
Upvote 0
Same concept and Sub Test() as gallen, but perhaps this function instead.

Code:
Function HasValue(rng As Range) As Boolean
  HasValue = Not rng.Find(What:="*", LookIn:=xlValues) Is Nothing
End Function
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top