Macro for IF Statement formulae [SOLVED]

[dev.sutradhar1987]

IF(A9=Dashboard!M7,Dashboard!C31,"")

IF(A10=Dashboard!M7,Dashboard!C31,"")

IF(A11=Dashboard!M7,Dashboard!C31,"")

..

..

IF(A65536=Dashboard!M7,Dashboard!C31,"")


Could anyone please help me in doing this with the help of macro.

 

[NARAYANK991]

Hi Dev ,


Can you please explain in more detail ?


What is it that you want to do ?


Why do you want a macro to do what the formula does ?


Is it just one formula in one cell , or one formula copied down to several thousand cells ?


If you look at the formula , what it is doing is that if Dashboard!M7 matches with any cell in the range A9:A65536 , then put the contents of Dashboard!C31 in the cell where this formula is present , otherwise display a blank. Is this what you want the macro to do ?


Narayan

 

[NARAYANK991]

Hi Dev ,


I am still not clear on why you need this code , or whether you need it as a UDF ; try running this piece of code :

[pre]

Public Sub temp()
Dim match_found As Long
On Error Resume Next
match_found = Application.WorksheetFunction.Match(Worksheets("Dashboard").Range("M7"), ActiveSheet.Range("A9:A65536"), 0)
On Error GoTo 0

If match_found > 0 Then ActiveCell.Value = Worksheets("Dashboard").Range("C31").Value Else ActiveCell.Value = ""
End Sub

[/pre]
Narayan

 

[dev.sutradhar1987]

Hi Narayan,


Thank you for the formaulae. I will try it and will let you know about it.


Thanks a ton

 

[dev.sutradhar1987]

Hi Narayan,


It is not working. I guess i forgot something. The formulae that i gave was in Column "W"(i.e - IF(A9=Dashboard!M7,Dashboard!C31,"")was in "W9"). Actually "Dashboard" is a user form and when they enter the "DATE" in "C31" of "Dashboard" i want it to go to "W9" of "Ongoing Process" if "A9" of "Ongoing Process" matches "M7" of "Dashboard".


When I use IF formulae it cant store the value when the dashboard is refreshed it goes.So i asked for a macro.


Please suggest me how i can fix the entry in Column"W" and update it when needed.

 

[NARAYANK991]

Hi Dev ,


Let me understand you.


You have two worksheet tabs , labelled Dashboard and Ongoing Process ; when data is entered in C31 of Dashboard , the macro should check if the last row of column A in Ongoing Process matches M7 of Dashboard ; if it does , then the corresponding cell in column W of Ongoing Process is filled with the data from C31 of Dashboard ; if it does not , then the corresponding cell in column W of Ongoing Process is cleared.


Is this correct ?


Narayan

 

[dev.sutradhar1987]

Hi,


Yes absolutely right. And once the data gets populated in corresponding cell in column W of Ongoing Process with the data from C31 of Dashboard it should stay there when i refresh my user page of "DashBoard".

 

[dev.sutradhar1987]

Hi Narayan,


Forgot to tell you One thing yesterday that is If data is entered in C31 of DASHBOARD, The macro should check the Whole Column "A" and when it finds the match with M7 of DASHBOARD then the corresponding cell of Column W should get updated if not then everything remains unchanged.

 

[NARAYANK991]

Hi Dev ,


Try this code in the Dashboard worksheet :

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("C31")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Dim match_found As Long
Dim Check_for As Variant
Dim Check_Within As Range

Check_for = ActiveSheet.Range("M7").Value
Set Check_Within = Worksheets("Ongoing Process").Range("A1").EntireColumn

On Error Resume Next
match_found = Application.WorksheetFunction.Match(Check_for, Check_Within, 0)
On Error GoTo 0

If match_found > 0 Then
Application.EnableEvents = False
Check_Within.Cells(1, 1).Offset(match_found - 1, 22).Value = Target
Application.EnableEvents = True
End If

Set Check_Within = Nothing
End Sub

[/pre]
Narayan

 

[dev.sutradhar1987]

Hey Narayan,


Amazing. It is working exactly i want. But one thing i am trying to make it work for one more cell, its like If data is entered in G31 of DASHBOARD, The macro should check the Whole Column "A" and when it finds the match with M7 of DASHBOARD then the corresponding cell of Column AG should get updated if not then everything remains unchanged.I tried playing with the code but it is not happening.I used like this


I need to understand how the code is serching for the Column "W" because "w" is generally 23 rd Column.

 

[NARAYANK991]

Hi Dev ,


Two points :


1. At present , the macro is checking for only a change in cell C31 , in the following statement :

[pre]

If Application.Intersect(Target, Range("C31")) Is Nothing Then Exit Sub
What this says is that if the change has taken place in any cell other than C31 , then exit the macro , without doing anything. If you now wish to check for a change in G31 also , you will need to have to two sections as follows :

[pre][code]If Target.Count > 1 Then Exit Sub
If Not (Application.Intersect(Target,Range("C31") is Nothing) Then
' Existing code will appear here
ElseIf Not (Application.Intersect(Target,Range("G31") is Nothing) Then
' Put the code for what is to be done if a change takes place in G31
EndIf

[/pre]
2. The reason is that the Offset function is always with respect to the range to which it is referred ; in the code , the statement is :

Check_Within.Cells(1, 1).Offset(match_found - 1, 22).Value = Target[/code][/pre]
Here Check_Within refers to column A ; Cells(1,1) refers to the first cell in this range i.e. cell A1 ; the 22nd column from this will be column W.


Narayan

 

[dev.sutradhar1987]

Thanks.


Today i learnt something new. thanks a lot for explaining the thing.


Cheers