Writing excel formulas in VBA

dnagaraj

New Member
Joined
Mar 11, 2008
Messages
3
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

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:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
try
Not sure if the formula is correct or not
Code:
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
 
Upvote 0
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.
 
Upvote 0
Perhaps you could tell us the purpose/need of this calculation?

Also, why to need/want to use VBA?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top