Logo 
Search:

VB.Net Forum

 Ask Question   UnAnswered
Home » Forum » VB.Net       RSS Feeds

Automatically apply specific formulas to specific columns

  Asked By: Lee    Date: Jul 11    Category: VB.Net    Views: 633
  

Hi,

I am working in a spreadsheet that gets updated with new data weekly - and I have some fixed formulas in specific columns that never changes, only when new values are added to the cells from the weekly update (i.e. column A to X).

The columns are Y, Z, AA, AB, AC, AD and AE and they all start from Row 6:

- Y: =WEEKNUM(K6;2)
- Z: =VLOOKUP(S6;Wagendetails!F$2:G$310;2;FALSE)
- AA: =VLOOKUP(I6;Wagendetails!B$2:D$420;2;FALSE)
- AB: =VLOOKUP(I6;Wagendetails!B$2:D$420;3;FALSE)
- AC: =IF(V6=0;AB6;V6+AB6)
- AD: =VLOOKUP(C6;Relationen!A$2:C$550;2;FALSE)
- AE: =VLOOKUP(D6;Relationen!E$2:F$10;2;FALSE)

I have tried different codes such as lFormulaCount and LastRow but they do not get applied to all columns.

To sum up: the weekly date will contain the exact same columns but the number of rows will differ, so I need a code to fill down the fixed formulas for the specific columns and down - but only down to the last row with data, which will most likely change weekly.

I would appreciate any inputs to what I am doing wrong.

Thanks.

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Lee Koefoed     Answered On: Jul 12

FYI the codes I have used - without any result - are:

Sub CMRDatenDSc_Button2_Click()

If Not Intersect(Target, Range("A:X")) Is Nothing Then 'This makes the code execute only when a value in column C is changed

Application.EnableEvents = False 'This prevents infinate loop

lastrow = Range("A1048576:X1048576").End(xlUp).Row
Range("Y6:AE6").AutoFill Destination:=Range("Y6:AE" & lastrow), Type:=xlFillDefault
Application.EnableEvents = True
End If
End Sub

Or

Sub CMRDatenDSc_Button2_Click()

Private Sub Worksheet_Change(ByVal Target As Range)
lastrow = Range("A1048576:X1048576").End(xlUp).Row
Application.EnableEvents = False 'This prevents infinate loop
Range("Y6:AE6").AutoFill Destination:=Range("Y6:AE" & lastrow), Type:=xlFillDefault
Application.EnableEvents = True
End Sub

Or

Sub CMRDatenDSc_Button2_Click()

a = Cells(A:X).End(xlUp).Row
b = "Y6:AE6" & a
Selection.AutoFill Destination:=Range(b)

End Sub

 
Answer #2    Answered By: Lee Koefoed     Answered On: Jul 16

Solution found:

Sub CMRDatenDSc_Button2_Click()


On Error GoTo ErrorHandle


Application.EnableEvents = False 'Prevents infinite loop
lastrow = Range("A1048576:X1048576").End(xlUp).Row 'Last row in column A
Range("Y6:AE6").AutoFill Destination:=Range("Y6:AE" & lastrow), Type:=xlFillDefault


BeforeExit:
Application.EnableEvents = True
Exit Sub


'Manage programme error
ErrorHandle:
Resume BeforeExit 'Directs back to BeforeExit


End Sub

 





 
 
 
 

Related Post