macro to check if cells have values in them...then

[Jim A]

Hi - I am looking for a macro that will check 2 things....then do something.
The first is if a range of cell values in are blank. lets say
Sheets("Sheet1").Range("D6:G48").
Next is if a particular cell contains a given value ,
Sheets("sheet1").Range("E5") should equal "Year 1" if true.
If both these are true then I want the macro to copy a range of cells from
Sheets("Sheet2").Range("H6:H48") to Sheets("Sheet1").Range("D648").

I tried to use IsEmpty but it seems to return a "False" if a range of cells
are being tested.

Thanks for any help - Jim A

 

[Roger Govier]

Hi Jim

One way
Sub test()
If WorksheetFunction.CountA(Sheets("Sheet1").Range("D6:G48")) = 0 _
And UCase(Sheets("Sheet1").Range("E5")) = UCase("Year 1") Then
MsgBox "true"
Else
MsgBox "false"
End If
End Sub

 

[Jim A]

That worked great!
how can I get this macro to check for "Year 1" and if False move on to "Year
2", (then "Year 3" ect,) and its corresponding range of cells?
When it finds the Year value to be true it can then check for blank cells in
its corresponding range. ... then copy.

I really appreciated your help nd quick response - Jim A

 

[Roger Govier]

Hi Jim

What is "it's corresponding range"
Are you saying that Sheets("Sheet1").Range("D6:G48") only belongs to Year 1,
and a different range of cells needs to be checked for Year 2, Year 3 etc.?
If so, what determines the range? Is it an offset from the first range?
Does the range you want to copy change with each Year?

The actual copying part is a one liner

Sheets("Sheet2").Range("H6:H48").Copy Sheets("Sheet1").Range("D6")

Sub test()
If WorksheetFunction.CountA(Sheets("Sheet1").Range("D6:G48")) = 0 _
And UCase(Sheets("Sheet1").Range("E5")) = UCase("Year 1") Then
Sheets("Sheet2").Range("H6:H48").Copy
Sheets("Sheet1").Range("D648")
End If
End Sub

 

[Jim A]

Thanks for replying -
Yes, Sheets("Sheet1").Range("D6:G48") only belongs to Year 1 and
range("D6:G48") need to be checked for being empty.
Sheets("Sheet1").Range(""I6:L48") belongs to Year 2 and range("I6:L48") need
to be checked for being empty.
and so on...through Year 5.

If false I am trying to get the macro to move on to the next year and its
range of cells.

The range I want to copy TO changes with each year. Example:
Year 1 would copy to Sheets("Sheet1").Range("D648")
Year 2 would copy to Sheets("Sheet1").Range("I6:I48") and so on...

I am having trouble using looping, so I have been trying to write this in a
series of IF statements. This seems like it can be problematic.

Thanks - Jim Ayers