Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Code to Change a Formula to Value (XL 97: SR2)

    Is it possible to send a value directly into a cell if the code contains a formula? The code below send the formula into a cell instead of the calculated value.


    Sub Go()
    Dim sngTargetEntityColumn As Single
    Dim sngTargetAccountColumn As Single
    Dim sngTargetAmountColumn As Single
    Dim sngTargetRow As Single

    sngTargetEntityColumn = 1
    sngTargetAccountColumn = 2
    sngTargetAmountColumn = 3

    sngTargetRow = Sheets("CSV_A").range("A65536").End(xlUp).Row + 1

    'Formula
    Sheets("CSV_A").Cells(sngTargetRow, sngTargetAccountColumn).Value = "Account"
    Sheets("CSV_A").Cells(sngTargetRow, sngTargetAmountColumn).Value = "=Round(Days_In_Month/Day_Of_Month, 0)"

    End Sub


    Thanks,John

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code to Change a Formula to Value (XL 97: SR2)

    <pre>Sub Go()
    Dim sngTargetEntityColumn As Single
    Dim sngTargetAccountColumn As Single
    Dim sngTargetAmountColumn As Single
    Dim sngTargetRow As Single

    sngTargetEntityColumn = 1
    sngTargetAccountColumn = 2
    sngTargetAmountColumn = 3

    sngTargetRow = Sheets("CSV_A").Range("A65536").End(xlUp).Row + 1

    'Formula
    Sheets("CSV_A").Cells(sngTargetRow, sngTargetAccountColumn).Value = "Account"
    Sheets("CSV_A").Cells(sngTargetRow, sngTargetAmountColumn).Value = _
    Application.WorksheetFunction.Round(Days_In_Month / Day_Of_Month, 0)

    End Sub

    </pre>

    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code to Change a Formula to Value (XL 97: SR2)

    Legare,

    Thank you for the advice. It works. Although it should work for "VLookup" I get an error message of "Unable to get the VLookup property of the worksheet function class". According to the wsfunction class VLookup should work. Any ideas?

    I'm using it in the following code:

    Sub go()
    Var1 = 15
    Range("A1").Value = Application.WorksheetFunction.Vlookup(Var1,Table,2 ,false)
    End Sub

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code to Change a Formula to Value (XL 97: SR2)

    There are some worksheet functions that can not be used in VBA with Application.WorksheetFunction and I think that VLOOKUP is one of those. What you will have to do is write VBA code to search the table and copy the value into the target cell. Something like this:

    <pre>Public Sub GetValue()
    Dim iVar1 As Integer, oCell As Range
    iVar1 = 15
    For Each oCell In Range(Range("Table").Columns(1).Address)
    If oCell.Value = iVar1 Then
    Worksheets("Sheet1").Range("A1").Value = oCell.Offset(0, 1).Value
    Exit For
    End If
    Next oCell
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

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