Consulting

Results 1 to 8 of 8

Thread: Solved: VBA code to turn the formula into value

  1. #1

    Unhappy Solved: VBA code to turn the formula into value

    I'm working on a workbook with multiple worksheets. I tried with the following codes to look up the value from cell G7 in cell A7 in all selected worksheets but came across with different issues for each of them as follows. I attached a sample file which contains extracted data in my original workbook (and also some made-up data). Could anyone help to amend the codes to make either one work?


    1) For unknown reason, this works on all the selected worksheet except the last one.
    [VBA]
    Sub Cname1()

    Dim i As Integer

    For i = 4 To Sheets.Count
    With Sheets(i)

    With .Range("A7")
    .Formula = "=IF(ISNA(VLOOKUP(R7C7,Customer_List,2,0)),"""", VLOOKUP(R7C7,Customer_List,2,0))"
    .Value = .Value
    End With

    End With

    End Sub
    [/VBA]


    2) When I included code ".Value = .Value", it returned empty in A7. When I removed it, the result can be displayed. The problem is, I need to turn the result of the formula into value.
    [VBA]
    Sub Cname2()

    Dim i As Integer

    For i = 4 To Sheets.Count
    With Sheets(i)

    Dim MyCname As String
    MyCname = "=IF(ISNA(VLOOKUP(R7C7,Customer_List,2,0)),"""", VLOOKUP(R7C7,Customer_List,2,0))")"

    .Range("A7") = MyCname
    .Value = .Value

    End With

    End Sub

    [/VBA]
    Attached Files Attached Files

  2. #2
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    493
    Location
    Quote Originally Posted by alexxgalaxy
    1) For unknown reason, this works on all the selected worksheet except the last one.
    Can you clarify in what way doesn't it work?
    -----------------------------------------
    The more you learn about something the more you know you have much to learn.

  3. #3
    Hi Brian

    What I was trying to do was to turn the lookup result in multiple worksheets into value.

    For my first code above, somehow, it works on all the selected worksheet except the last one.

    For the second code, when I included code ".Value = .Value" (highlighted in read above), the return value is empty in A7. When I removed it, the result can be displayed but this couldn't meet my requirement (i.e. turn the lookup result in multiple worksheets into value.)

  4. #4
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    493
    Location
    Your second code is using with on the sheet not the range. That is why .value = .value is failing.
    -----------------------------------------
    The more you learn about something the more you know you have much to learn.

  5. #5
    Sorry, I'm an elementary user. Someone taught me the code one year ago but didn't really explain how it worked. It usually worked but not this time. I don't think I understand what you mean.

    Could you advise what I could do with the code then? Thanks!

  6. #6
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    493
    Location
    [vba]Sub Cname2()

    Dim i As Integer

    For i = 4 To Sheets.Count
    With Sheets(i)

    Dim MyCname As String
    MyCname = "=IF(ISNA(VLOOKUP(R7C7,Customer_List,2,0)),"""", VLOOKUP(R7C7,Customer_List,2,0))")"

    .Range("A7") = MyCname
    .Range("A7").Value = .Range("A7").Value

    End With

    End Sub
    [/vba]
    -----------------------------------------
    The more you learn about something the more you know you have much to learn.

  7. #7
    Quote Originally Posted by BrianMH
    [vba]Sub Cname2()

    [/vba]
    It didn't work. It became empty instead of the result of the formula.

  8. #8
    I finally figured out what's the problem. There was nothing wrong with my original code. What was wrong was the order of the other codes in my marco. The R7C7 in my vlookup formula came before the code that I inserted the value for R7C7. That's why my code could produce the result when it wasn't converted into value (i.e. there was nothing in the cell G7 when the formula was converted into value. So, it returned empty when the .value = .value was added).

    I felt so stupid for wasting so much time on working on different ways to do the code instead of reviewing the whole marco. I'm so embarrassed and sorry for wasting your time, Brain. I apologies.
    Last edited by alexxgalaxy; 01-23-2013 at 10:28 PM.

Posting Permissions

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