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
and I've tried to do the same in VBA like this:
But I get a "run time error : type mismatch" error at line:
Can someone please help me do this calculation using vba.
Thanks!!
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
Code:
=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:
Code:
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:
Code:
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!!
Last edited: