PDA

View Full Version : Solved: If Statement Using Named Ranges



khalid79m
06-17-2009, 03:26 AM
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

xld
06-17-2009, 04:18 AM
Looks okay. Are you sure that name exists? is it local or global name?

khalid79m
06-17-2009, 08:20 AM
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

xld
06-17-2009, 08:46 AM
You can't do that. Do you mean if a cell within the range(AR3:AR50") contains the word Products, or all cells?

khalid79m
06-17-2009, 08:51 AM
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
06-17-2009, 11:03 AM
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

xld
06-17-2009, 11:35 AM
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
06-17-2009, 12:05 PM
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
06-26-2009, 04:43 AM
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
06-26-2009, 05:25 AM
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
06-26-2009, 08:10 AM
:cloud9: Thanks works a treat //