Writing excel formulas in VBA

[dnagaraj]

Hi,
I'm new to excel and VBA. I'm trying to automate an excel calculation by using VBA. Right now I'm just experimenting with one cell but eventually I'd like to perform the calculation on all the cells. This is the formula in excel

=IF(MIN(10*LOG(((work!D$11:D$331)^2+(work!$C$11:$C$331)^2+2*(work!D$11:D$331)*(work!$C$11:$C$331)*COS(work!D$341:D$661-work!$C$341:$C$661))/((work!D$11:D$331)^2+(work!$C$11:$C$331)^2-2*(work!D$11:D$331)*(work!$C$11:$C$331)*COS(work!D$341:D$661-work!$C$341:$C$661)))-work!$B$11:$B$331)>0,"MATCH","-")

and I've tried to do the same in VBA like this:

Sub rup()
Dim rVal As Range
Dim vCal As Variant
Dim vCal2 As Variant
Dim vCal3 As Variant
Dim rRng1 As Range
Dim rRng2 As Range
Dim rRng3 As Range
Dim rRng4 As Range
Dim rRng5 As Range
Set rRng1 = Worksheets("work").Range("C11:C331")
Set rRng2 = Worksheets("work").Range("D11:D331")
Set rRng3 = Worksheets("work").Range("C341:C661")
Set rRng4 = Worksheets("work").Range("D341:D661")
Set rRng5 = Worksheets("work").Range("B11:B331")
vCal = ((rRng1 ^ 2) + (rRng2 ^ 2) + 2 * rRng1 * rRng2) * Cos(rRng3 - rRng4)) / ((rRng1 ^ 1) + (rRng2 ^ 2) - 2 * rRng1 * rRng2 * Cos(rRng3 - rRng4)) - rRng5
vCal2 = 10 * Application.WorksheetFunction.Log(vCal)
vCal3 = Application.WorksheetFunction.Min(vCal3)
If (vCal3 > 0) Then
Worksheets("ring").Range("B11").Value = "yes"
Else
Worksheets("ring").Range("B11").Value = "NO"
End If
End Sub

But I get a "run time error : type mismatch" error at line:

vCal = ((rRng1 ^ 2) + (rRng2 ^ 2) + 2 * rRng1 * rRng2) ' * Cos(rRng3 - rRng4)) / ((rRng1 ^ 1) + (rRng2 ^ 2) - 2 * rRng1 * rRng2 * Cos(rRng3 - rRng4)) - rRng5

Can someone please help me do this calculation using vba.

Thanks!!

[jindon]

try
Not sure if the formula is correct or not

Sub rup()
Dim rVal As Range
Dim vCal As String
Dim vCal2 As Variant
Dim vCal3 As Variant
Dim rRng1 As String
Dim rRng2 As String
Dim rRng3 As String
Dim rRng4 As String
Dim rRng5 As String
rRng1 = "work!C11:C331"
rRng2 = "work!D11:D331"
rRng3 = "work!C341:C661"
rRng4 = "work!D341:D661"
rRng5 = "work!B11:B331"
vCal = "((" & rRng1 & "^2) + (" & rRng2 & "^2) + 2 *" & rRng1 & "*" & rRng2 & _
           ")*Cos(" & rRng3 & "-" & rRng4 & "))/((" & rRng1 & "^1)+(" & rRng2 & _
           "^2)-2*" & rRng1 & "*" & rRng2 & "*" & Cos(" & rRng3 & "-" & rRng4 & "))-" & rRng5
vCal2 = 10 * Application.WorksheetFunction.Log(vCal)
vCal3 = Application.WorksheetFunction.Min(vCal3)
If (vCal3 > 0) Then
Worksheets("ring").Range("B11").Value = "yes"
Else
Worksheets("ring").Range("B11").Value = "NO"
End If
End Sub

[dnagaraj]

Thank you for your reply. I tried your suggestion but I still get the "Run time error:13 Type Mismatch" error.

Any other ideas?

Thanks.

[Norie]

Perhaps you could tell us the purpose/need of this calculation?

Also, why to need/want to use VBA?

[dnagaraj]

This calculation is done on about 14 coloumns and has been done just in excel till now. The user has had to change the name of the coloumns in the formula to perform the calculations on different coloumns. So I want to use VBA to automate that. That is to sort of increment the coloumn names in the formula automatically for each cell without the user having to do it manually.
Does that make sense?
If there are any other approaches to do this, I'd be happy to try them
Thanks.