Results 1 to 5 of 5

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. #1
    New Member
    Join Date
    Mar 2008
    Posts
    3

    Default 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
    Can someone please help me do this calculation using vba.

    Thanks!!
    Last edited by dnagaraj; Oct 30th, 2008 at 12:00 PM.

  2. #2
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

    Default 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. #3
    New Member
    Join Date
    Mar 2008
    Posts
    3

    Default 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. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,050

    Default 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?
    If posting code please use code tags.

  5. #5
    New Member
    Join Date
    Mar 2008
    Posts
    3

    Default 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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com