My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Re: assistance to make a vba code run on a dynamic range
Hi
Tried code but when adding a dummy record on work tab , did not give expected results on summary tab which are unique with, the zero in all columns of summary tab on the far right is the original data
That of yellow given when added a dummy record added
Sub Demo1() Dim F$ F = "=SUMIFS(WORK!#1#2,WORK!$H$2:$H$#2,$A2,WORK!$I$2:$I$#2,$B2,WORK!$K$2:$K$#2,$C2)" F = Replace$(F, "#2", Worksheets("WORK").UsedRange.Rows.Count) With Worksheets("SUMMARY").UsedRange.Rows With .Item("2:" & .Count).Columns .Item(4).Formula = Replace$(F, "#1", "$S$2:$S$") .Item(5).Formula = Replace$(F, "#1", "$T$2:$T$") .Item(6).Formula = Replace$(F, "#1", "$U$2:$U$") .Item(7).Formula = Replace$(F, "#1", "$V$2:$V$") .Item(8).Formula = Replace$(F, "#1", "$AC$2:$AC$") End With .Cells(.Count + 1, 4).Resize(, 5).Formula = "=SUM(D2:D" & .Count & ")" End With End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 05-09-2017 at 10:20 PM.
Reason: optimization …
Re: assistance to make a vba code run on a dynamic range
Jindon
The code works well, a little issue when I add records on data tab, on summary tab it does not adjust the values and totals it shows zero in i all columns.
What I actually mean , sample data ends at row 58 in sheet work when data are added e.g to row to 60. the code does not expand to row to 60, it remains at row 58 in the formula thus showing zeros in all columns ,
A little tweak in the code as well sum of the values at end of numerical data .
Re: assistance to make a vba code run on a dynamic range
Hi Jindon
An insight that applying code those starting with a 0 digits in column A of summary tariff highlighted yellow is removed when code is applied , those codes are extracted from column H of workings tabs .
LinkBack URL
About LinkBacks
Register To Reply
As a starter …
Bookmarks