Apply formula to entire column with a macro in VBA

[croger152003]

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]

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.

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]

This script would put the results of the formula into the cell not the formula. Would this work?

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]

This script would put the results of the formula into the cell not the formula. Would this work?

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

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

This is exactly what I was looking for, this worked perfectly and gives the pressure number exactly.

Thank you so much

 

[Gogo007]

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.

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.

Dim lrow As Long

lrow = Cells(Rows.Count, "L").End(xlUp).Row
Range("L6").Formula = "=CONCATENATE(RC[-4],""-AGIS"")"
Range("L6").AutoFill Destination:=Range("L6:L" & lrow)
End Sub

Use this code but giving error as
AutoFill method of range class failed

 

[igold]

Your code as written (direct copy & paste) worked for me with no errors.