Results 1 to 4 of 4

Thread: Cost Value Calculator
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Cost Value Calculator

    Cost value (Local currency) Currency Cost value
    500 SGD
    3664 EUR

    So I'm stuck in a situation whereby, I'm supposed to convert the cost value (local currency) into cost (in USD).
    I had made a button to run the VBA Macro to calculate the values and paste back into the table.

    Here's my coding:

    Sub Costvalue()
    Dim Country As String, Cost As Double, total As Double, xrate As Double
    Dim r As Integer, c As Integer

    r = 9
    c = 10

    'Cell(9,10) is at value 500.'

    Country = ActiveCell(r, c)

    Do While Country <> ""

    If Country = "SGD" Then
    xrate = 0.74
    ElseIf Country = "EUR" Then
    xrate = 1.13
    ElseIf Country = "CHF" Then
    xrate = 1
    ElseIf Country = "GBP" Then
    xrate = 1.29
    End If

    Cost = ActiveCell(r, c - 1).Value
    Range(r, c + 1).Value = Cost * xrate 'here is the error'

    r = r + 1
    c = c + 1

    Loop
    End Sub

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,806
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Cost Value Calculator

    Code:
    Maybe this....UNTESTED
    Sub Costvalue()
    Dim Country As String, Cost As Double, total As Double, xrate As Double
    Dim r As Integer, c As Integer
    r = 9
    c = 10
    'Cell(9,10) is at value 500.'
    Country = Cells(r, c)
    Do While Country <> ""
        If Country = "SGD" Then
        xrate = 0.74
        ElseIf Country = "EUR" Then
        xrate = 1.13
        ElseIf Country = "CHF" Then
        xrate = 1
        ElseIf Country = "GBP" Then
        xrate = 1.29
        End If
        Cost = Cells(r, c - 1).Value
        Cells(r, c + 1).Value = Cost * xrate 'here is the error'
    r = r + 1
    c = c + 1
    Loop
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    New Member
    Join Date
    Feb 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cost Value Calculator

    Alright! I managed to change your code abit. Thanks. (Y)

  4. #4
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,806
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Cost Value Calculator

    Glad to help...
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

Some videos you may like

User Tag List

Tags for this Thread

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
  •