Apply formula to entire column with a macro in VBA

croger152003

New Member
Joined
Apr 26, 2018
Messages
2
I download data from our Jets and one of the fields I see is the fuel pressure, the fuel pressure sensor has a signal voltage between 1-5 v. With the flight data depending on how long the flight was it could be any where between a few thousand data points to several 100k data points. The formula I need to apply is V * 205 / 2.5 + -78, V= the data in each cell of BF2 and on, this macro is needed because I can talk someone through using a macro easier than a whole process of applying a formula to all 350k cells. Ideally the formula would be something that has a start of BF2 and ends where there is no data automatically.

I know excel enough to get into trouble sometimes but not always enough to make things work the way I want, and and all help is appreciated.
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,427
Hi croger152003,

Welcome to the MrExcel forum.

The only issue I see is that the column BF can either contain data points or a formula but not both, you would need to put the formula into another column. In this code I used column BG for the formula to fill down.

Code:
Sub Volts()


    Dim lrow As Long
    
    lrow = Cells(Rows.Count, "BF").End(xlUp).Row
    Range("BG2").Formula = "=BF2*205/2.5+-78"
    Range("BG2").AutoFill Destination:=Range("BG2:BG" & lrow)
    
End Sub
I hope this helps.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,066
This script would put the results of the formula into the cell not the formula. Would this work?

Code:
Sub Test()
'Modified 4/26/2018 8:15 PM  EDT
Application.ScreenUpdating = False
Dim c As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "BF").End(xlUp).Row
    For Each c In Range("BF2:BF" & Lastrow)
         c.Value = c.Value * 205 / 2.5 + -78
    Next
Application.ScreenUpdating = True
End Sub
 

croger152003

New Member
Joined
Apr 26, 2018
Messages
2
This script would put the results of the formula into the cell not the formula. Would this work?

Code:
Sub Test()
'Modified 4/26/2018 8:15 PM  EDT
Application.ScreenUpdating = False
Dim c As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "BF").End(xlUp).Row
    For Each c In Range("BF2:BF" & Lastrow)
         c.Value = c.Value * 205 / 2.5 + -78
    Next
Application.ScreenUpdating = True
End Sub
This is exactly what I was looking for, this worked perfectly and gives the pressure number exactly.

Thank you so much
 

Forum statistics

Threads
1,077,982
Messages
5,337,518
Members
399,154
Latest member
gavlink

Some videos you may like

This Week's Hot Topics

Top