# Writing excel formulas in VBA

This is a discussion on Writing excel formulas in VBA within the Excel Questions forums, part of the Question Forums category; Hi, I'm new to excel and VBA . I'm trying to automate an excel calculation by using VBA . Right ...

1. ## Writing excel formulas in VBA

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`

Thanks!!

2. ## Re: Writing excel formulas in VBA

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```

3. ## Re: Writing excel formulas in VBA

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.

4. ## Re: Writing excel formulas in VBA

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

Also, why to need/want to use VBA?

5. ## Re: Writing excel formulas in VBA

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•