Range...Excel how to replicate same code

[suraj269]

this code works very well for the one cell B12, where selection made reflects message change in C12.
i want to do it for series of rows.
Like B13, C13 and then B14, C14 , .....and so on.

can someone please help


If Range("B12") = "N/A" Then ' Drop down value selection
Range("C12") = "Details required"
ElseIf Range("B12") = "NO" Then
Range("C12") = "Details required"
ElseIf Range("B12") = "YES" Then
Range("C12") = " "
Else
Range("C12") = " "
End If

[patjones]

I would just open a module window in VBA and write out this function:

Public Function GetMessage(strRange) As String

Select Case Range(strRange)
    Case "NO", "N/A"
        GetMessage = "Details Required"
    Case "YES"
        GetMessage = ""
End Select

End Function

Then in the cells in question you can call this function. For instance, in cell C12 you'll write =GetMessage("B1 2"), and so on. In order to make this work I had to do Application.Cal culateFull in the Worksheet_Chang e event for the sheet that I did this on.

Pat

[suraj269]

i dont understand what do you mean by Application.cal culate.....

[patjones]

Whenever you make a change to one or more cells on an Excel worksheet, this triggers the worksheet's Change event. Inserting the line Application.Cal culateFull inside the Change event as such:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.CalculateFull

End Sub

...causes everything on the worksheet to be re-calculated, including user-defined functions. If you didn't include this, then Excel would not, for example, re-evaluate cell C12 after making a new selection in cell B12.

Pat

[suraj269]

Whenever you make a change to one or more cells on an Excel worksheet, this triggers the worksheet's Change event. Inserting the line Application.Cal culateFull inside the Change event as such:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.CalculateFull

End Sub

...causes everything on the worksheet to be re-calculated, including user-defined functions. If you didn't include this, then Excel would not, for example, re-evaluate cell C12 after making a new selection in cell B12.

Pat

i am calling the funtion but getting #name error

[patjones]

Are you writing =GetMessage("B1 2"), =GetMessage("B1 3"), and so on?

[suraj269]

Are you writing =GetMessage("B1 2"), =GetMessage("B1 3"), and so on?

i am writing
=GetMessage(B12 ) and then i have tried =GetMessage("B1 2")

both gives me #Name error.
I have added necessary Add-Ins but still getting same error

[patjones]

When you wrote out the function definition did you put it in a VBA module called "Module 1" and compile the code?

[suraj269]

When you wrote out the function definition did you put it in a VBA module called "Module 1" and compile the code?

i created Module 1 and wrote the following (and compiled)


Public Function GetMessage(strR ange) As String

Select Case Range(strRange)
Case "NO", "N/A"
GetMessage = "Details Required"
Case "YES"
GetMessage = ""
End Select

End Function


Private Sub Worksheet_Chang e(ByVal Target As Range)

Application.Cal culateFull

End Sub

[patjones]

OK, this code

Private Sub Worksheet_Change(ByVal Target As Range)

Application.CalculateFull

End Sub

needs to be put in the code window for Sheet1, not in Module1 with the function definition. Also, you might be having a problem with macros not being enabled for your file, though I think this is less likely to be the problem.

Pat

[suraj269]

Thanks so much zepphead80, you were so helpfull. its working now. I learned alot from it and it refreshed my knowledge also.
thanks again

[patjones]

It's no problem at all. I'm happy to have helped!

Pat