Solved: If Statement Using Named Ranges

[khalid79m]

I have written the following code


Sub a()

If Range("Call_Activity_01") <> "" Then
MsgBox "hi"
End If

End Sub


It keeps coming up with type miss match

[Bob Phillips]

Looks okay. Are you sure that name exists? is it local or global name?

[khalid79m]

Hi Xld, Maybe Im doing things wrong again , nothing new there...

I have the following named ranges
"Call_Data_Za" and this is range ar3 to ar50
"Call_Data_Ca" and this is range az3 to az50

If ("Call_Data_Za") does not equal "Products"then ("Call_Data_Ca") = "NotApp"

for example
if ar 25 = Products then do nothing
if ar 15 = Sales then az 15 = NotApp

does this make sense ?


Thanks for all your help in advance

[Bob Phillips]

You can't do that. Do you mean if a cell within the range(AR3:AR50") contains the word Products, or all cells?

[khalid79m]

a cell within the range ("AR3:AR50") , the only problem is I cant do it using a fixed reference because sometimes it could be ar3 to ar???.

(the named range is set using a lastrow in column so ar3 to lastrow = range name)

[p45cal]

For Each cll In Range("Call_Data_Za").Cells
If cll.Value <> "Products" Then Range("Call_Data_Za").Parent.Cells(cll.Row, Range("Call_Data_Ca").Column) = "NotApp"
Next cll

[Bob Phillips]

Dim cell As Range

For Each cell In Range("Call_Data_Za")

If cell.Value = "Sales" Then Range("Call_Data_Ca").Cells(cell.Row, cell.Column) = "NotApp"
Next cell

[p45cal]

Careful xld, the cells in
Range("Call_Data_Ca").Cells(cell.Row, cell.Column)
are the cells of Range("Call_Data_Ca") rather than the sheet's entire cell range, so the cell.Row and cell.Column values will be relative to that range.

[khalid79m]

Dim CLL As Range
For Each CLL In Range("Check_Call_Activity_01").Cells
If CLL.Value = "O" Then Range("Check_Call_Activity_01").Parent.Cells(CLL.Row, Range("Call_Activity_01").Column) = ""
If CLL.Value = "O" Then Range("Check_Call_Activity_01").Parent.Cells(CLL.Row, Range("Call_Activity_Data_01_01").Column) = ""
If CLL.Value = "O" Then Range("Check_Call_Activity_01").Parent.Cells(CLL.Row, Range("Call_Activity_Data_01_02").Column) = ""
If CLL.Value = "O" Then Range("Check_Call_Activity_01").Parent.Cells(CLL.Row, Range("Com_01").Column) = ""
If CLL.Value = "O" Then Range("Check_Call_Activity_01").Parent.Cells(CLL.Row, Range("Proce_01").Column) = ""
Next CLL


The above code is what I have thanks to all you guys ... i have one problem, i now need it "if cell value is O or ""(Blank) then.." how do I do that ?

[p45cal]

not tested:Dim CLL As Range
For Each CLL In Range("Check_Call_Activity_01").Cells
If CLL.Value = "O" Or CLL.Value = "" Then
With Range("Check_Call_Activity_01").Parent
.Cells(CLL.Row, Range("Call_Activity_01").Column) = ""
.Cells(CLL.Row, Range("Call_Activity_Data_01_01").Column) = ""
.Cells(CLL.Row, Range("Call_Activity_Data_01_02").Column) = ""
.Cells(CLL.Row, Range("Com_01").Column) = ""
.Cells(CLL.Row, Range("Proce_01").Column) = ""
End With
End If
Next CLL

[khalid79m]

:cloud9: Thanks works a treat //