none
VBA code for Excel to place value in one column based upon values (there are 400) in another column RRS feed

  • Question

  • I have a spreadsheet with over 1,000 rows that contains several columns. One column T contains 400 different values. I need to be able to search column T and if the value is XXXX, then a value needs to be placed into a different column, column R (same row). I have used the function INDEX MATCH, however, this spreadsheet needs to be imported into another program with just the values only.

    Is there VBA code that I can use in the Visual Basic editor to do this task? For example: If Column T Cell = 2270, then Column R cell (same row) = L2AAG. Again, there are 400 different values in Column T.

    Is there a way to save the spreadsheet with the values only after the INDEX MATCH function has been run? The second sheet lookup values will need to be deleted, as well as the INDEX MATCH. The import must be the values only in Column R.

    Thank you for your help!

    tsphil25

    Monday, November 28, 2016 11:46 PM

All replies

  • I have a spreadsheet where I have a second spreadsheet with lookup values for the first sheet. After I run the INDEX MATCH function to replace the values in one column, I need to save the spreadsheet with the replaced values only and delete the second sheet. This spreadsheet then needs to be imported into another program. Is there a way to save the spreadsheet with the replaced values only?

    tsphil25

    Monday, November 28, 2016 11:28 PM
  • You can try Copy the cells-> Right CLick -> Click PasteSpecial - >Paste Values

    or Data Tab->Edit Links->Select the link to first spreadsheet-> CLick Break Links


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Tuesday, November 29, 2016 4:46 AM
    Answerer
  • Hi,

    In Excel you do not have any built-in option to just save your workbook with/without formulas. You can use a VBA code to perform this task.

    Sub FoundMatch()
        Dim iRow As Integer
        iRow = 2 ' assuming that your data starts from row 2
        With Sheets("YOURSHEETNAME") 'change the sheet name where data is
        While (.Range("T" & iRow).Value <> "")
            If .Range("T" & iRow).Value = "2270" Then .Range("R" & iRow).Value = "L2AAG"
            iRow = iRow + 1
        Wend
        End With
    End Sub
    


    Vish Mishra

    Tuesday, November 29, 2016 5:33 AM
  • Hi,

    >>Is there a way to save the spreadsheet with the values only after the INDEX MATCH function has been run?

    In my opinion, you could create a new worksheet Sheets.Add Method (Excel) to store the value only using Range.PasteSpecial Method (Excel). Then you could use Worksheet.SaveAs Method (Excel) to save the worksheet as a new workbook. So we could keep the original sheets with formulas for next using.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 1, 2016 10:02 AM
    Moderator