Excel VBA Convert Formula to Value In Specific Column

Question

I need vba code in Excel to convert formula to its value, but only in specific column and only convert till last row with data

Example: Sheet 1 Column B, E:J filled with lookup formula to get data from Sheet 2. Formula is filled from row 1 to 1500. Based on table array in sheet 2. Sheet 1 row 1 to 1000 return lookup result. I need to convert column B, E:J row 1 to 1000 formula to value while row 1001 to 1500 is still have lookup formula

From internet search i can use this code...

Sub FormulaToValue()
Dim N As Long, rng As Range

N = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range(Cells(1, 1), Cells(N, Columns.Count))

rng.Value2 = rng.Value2
End Sub

This code will convert formula to value until last row with data only, in this case only row 1 to 1000 will be converted. But this code convert all columns in the sheet. I think i need to change Columns.Count to specific column range but i don't now how to write the right code. I'm not familiar with vba code

Please help. Thank you

Convert formula in range defined as rng to its value

Robot.Kantam
– Robot.Kantam

2018-03-20 08:19:25 +00:00
Commented Mar 20, 2018 at 8:19 — Robot.Kantam
– Robot.Kantam, Commented Mar 20, 2018 at 8:19

Paul Kelly · Accepted Answer · 2018-03-22 05:14:33Z

3

Put the column you require in this line

Set rng = Range(Cells(1, 1), Cells(N, Columns.Count))

e.g. If you want to use column E

Set rng = Range(Cells(1, 5), Cells(N, 5))

If you want to use multiple columns you can use a loop to go through them

Sub FormulaToValue()

    Dim N As Long, rng As Range

    Dim arr As Variant
    arr = Array("B", "E", "F", "G", "H", "I", "J")

    ' Read through each column
    Dim col As Variant
    For Each col In arr
        N = Cells(Rows.Count, col).End(xlUp).Row
        Set rng = Range(Cells(1, col), Cells(N, col))
        rng.Value2 = rng.Value2
    Next col

End Sub

If you have columns like B:E then you need to go through them individually to find the last cell with data in each.

edited Mar 22, 2018 at 5:14

answered Mar 20, 2018 at 7:55

Paul Kelly

9851 gold badge7 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Robot.Kantam Over a year ago

Ok, that works. Only formula in column E converted to its value. But how to convert formula in multiple columns instead just one column? Let say the columns is B, E to J

aaa Over a year ago

wow, I'm a regular visitor of your blog. They're really great and easy to understand!

TAAAAAA · Accepted Answer · 2021-07-16 09:07:24Z

I have this RON code where i have tweaked my excel where in excel file path there is formula and while i am trying to send an email VBA is picking reading it as formula and giving error no cells were found in line

For Each FileCell In rng.SpecialCells(xlCellTypeConstants)

thankful if you can please advice

code copied here

Sub Send_Files() 'Working in Excel 2000-2016 'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm Dim OutApp As Object Dim OutMail As Object Dim sh As Worksheet Dim cell As Range Dim FileCell As Range Dim rng As Range

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set sh = Sheets("Sheet1")

Set OutApp = CreateObject("Outlook.Application")

For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)

    'Enter the path/file names in the K:Z column in each row
    Set rng = sh.Cells(cell.Row, 1).Range("K1:Z1")

    If cell.Value Like "?*@?*.?*" And _
       Application.WorksheetFunction.CountA(rng) > 0 Then
        Set OutMail = OutApp.CreateItem(0)

        With OutMail
            .to = cell.Value
            .Subject = "XXXXXX"
            .Body = "XXXXX"
            'rng = rng.Text
            For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
                If Trim(FileCell) <> "" Then
                    If Dir(FileCell.Value) <> "" Then
                        .Attachments.Add FileCell.Value
                    End If
                End If
            Next FileCell

            .Send  'Or use .Display
        End With

        Set OutMail = Nothing
    End If
Next cell

Set OutApp = Nothing
With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With

End Sub

Collectives™ on Stack Overflow

Excel VBA Convert Formula to Value In Specific Column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related