Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

add formula to multiple columns by vba

0

hello  

I want  adding  the  formula  from column D: F  based on  filled  in  column B 

actually  I  use   specific  range  b2:  b100  but   if  there  is  dynamic  way   without  change  the  range  in column B   every  time  when  increase  data  it  will be  better.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("b2:b100")) Is Nothing Then
var1a = Range("a" & Rows.Count).End(xlUp).Row
With Worksheets("EmployeeCosts")
.Range("d9:f" & var1a).Formula = "= IFERROR(HOUR(D9-$D$3)*60+MINUTE(D9-$D$3);"")"
End With
End Sub
 
excel
MS Office Version: 2019
posted by: Alaa (rep: 28) on Tue Nov 2, 2021 at 5:39 am
Answer
Discuss

Discussion

Alaa

I'm not clear why you are using a Worksheet_Change event macro to do this or what your data looks like (and especially why you'd want to add the same formula in 3 columns i.e. giving the same values in D, E and F). Please edit your question to use the Add Files... button to attach a representative Excel file.
John_Ru (rep: 6142) Nov 2, '21 at 7:25 am
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login