Solved: IF Function Help

[LutonBarry]

Hope someone can help me. I attach a spreadsheet with two tabs of service calls that are duplicated one labelled today the other yesterday. I want to write I think an IF statement that looks at the Today spreadsheet and taking for example cells C2,D2 and E2, if the cell has nothing in it it goes to the corresponding cell on the Yesterday Tab and copies that cell including the formatting if possible in the the cell on the Today sheet. If the cell has data in it as has cell D2 on the Today Tab then return that value. Then intention being to write this into some VBA code in a a DO LOOP or similar that will run the formula down colums C:F and I:M for the rows present then once complete move onto the next instruction in the code.

[SamT]

Module1 code

Sub SamT()
Dim CheckCells as Range
Dim Cel As Range
Set CheckCells = Sheets("Today").Range("C2:E2")
For Each Cel in CheckCells
If Cel.Value = "" Then
Sheets("Yesterday").Range(Cel.Address).Copy Destination:=Cel
End If
Next Cel
End Sub

[LutonBarry]

Sam, Thanks for the reply and the code. I'm learning all the time here so thanks. I've tested the code and it works on column C but then doesn't progress to the other columns to be checked any ideas?

[SamT]

Add this line as shown and let me know what it says
Set CheckCells = Sheets("Today").Range("C2:E2")
MsgBox "Checking " & CheckCells.Count & " Cells"
For Each Cel In CheckCells

[LutonBarry]

Sam,

This is how I pasted the lines in below and the msg box said checking 3 cells?:

Sub SamT()
Dim CheckCells As Range
Dim Cel As Range
Set CheckCells = Sheets("Today").Range("C2:E2")
MsgBox "Checking " & CheckCells.Count & " Cells"
For Each Cel In CheckCells
If Cel.Value = "" Then
Sheets("Yesterday").Range(Cel.Address).Copy Destination:=Cel
End If
Next Cel

End Sub

[SamT]

Next 2 troubleshooting steps:

Replace
Sheets("Yesterday").Range(Cel.Address).Copy Destination:=Cel
With
'TS Step 1
Sheets("Yesterday").Range(Cel.Address).Select
MsgBox ("Compare " & Cel.Value _
& "to the Value in the Selected Cell on Sheet(Yesterday)"
Sheets("Yesterday").Range(Cel.Address).Select
'TS Step 2
Sheets("Yesterday").Range(Cel.Address).Copy
Cel.PasteSpecial

[LutonBarry]

Sam getting a compile syntaX error on line this line
MsgBox ("Compare " & Cel.Value & "to the Value in the Selected Cell on Sheet(Yesterday)"

This is how it looked after I pasted it in.

Sub SamT()
Dim CheckCells As Range
Dim Cel As Range
Set CheckCells = Sheets("Today").Range("C2:E2")
MsgBox "Checking " & CheckCells.Count & " Cells"
For Each Cel In CheckCells
If Cel.Value = "" Then
'TS Step 1
Sheets("Yesterday").Range(Cel.Address).Select
MsgBox ("Compare " & Cel.Value & "to the Value in the Selected Cell on Sheet(Yesterday)"
Sheets("Yesterday").Range(Cel.Address).Select
'TS Step 2
Sheets("Yesterday").Range(Cel.Address).Copy
Cel.PasteSpecial
End If
Next Cel

End Sub

[LutonBarry]

Sam ,

I've modified the line
MsgBox ("Compare " & Cel.Value & "to the Value in the Selected Cell on Sheet(Yesterday)"
to
MsgBox ("Compare " & Cel.Value & "to the Value in the Selected Cell on Sheet(Yesterday) ")

What happens is a msg box appears still saying it is checking 3 cells and when it reaches this line
If Cel.Value = "" Then
Jumps straight to the End If statement.

Got to say that IO am very grateful for your help on this.

[LutonBarry]

Sam,

Thanks to you for your prompts and help somehow I think I have cracked it with this modification of your code.

Sub SamT()
Dim CheckCells As Range
Dim Cel As Range
Selection.CurrentRegion.Select
Set CheckCells = Range("A1").CurrentRegion
CheckCells.Select
For Each Cel In CheckCells
If Cel.Value = "" Then
Sheets("Yesterday").Range(Cel.Address).Copy Destination:=Cel
End If
Next Cel

End Sub

[SamT]

Sub SamT()
Dim CheckCells As Range
Dim Cel As Range
Selection.CurrentRegion.Select 'Superfluous, remove
Set CheckCells = Range("A1").CurrentRegion 'Who Knew? Learn sumtheeng everyday
CheckCells.Select 'Superfluous, remove
For Each Cel In CheckCells
If Cel.Value = "" Then
Sheets("Yesterday").Range(Cel.Address).Copy Destination:=Cel
End If
Next Cel

End Sub

:thumb :beerchug:

Although recording a Macro adds many Select/Selection lines to code, they are almost never useful except with Event Triggered Procedures.

If you find that CurrentRegion includes too many cells, try
Set CheckCells = Range("A1").Resize(,3)
Resize(NewNumRowsInRange, NewNumColsInRange)

[LutonBarry]

Sam, You're a star that last bit about resizing the range to exclude for example the title row was just what I was going to try next.

Thanks so much for your help, just looking at how you tackled the problem has been an education.

Thanks again.

Barry:bow: