I've got several formulas that work in cells of the active sheet and I reference them from VBA code to create ranges.
I'm trying to get the formulas out of the sheet cells and into variables in VBA that I can use for a range references.
The first step is to find the starting point for the first range.
I need to search column B for a specific set of text. Set that as the start of the range.
Then search column B for a second specific set of text. Set that as the end of the range.
I use these two row values and select a range with rows above them and below them to create a large row group.
Then I sort that range of rows based on column R and column C values.
Then I use a formula in a cell that compares an array of two ranges within the larger range to get the last row with a value in it.
I then use this row number as the start of the range that I used to create a row grouping that I can hide.
The result should be a large group of rows with a smaller group of rows that have been hidden and only the rows with values in column R are displayed. The set of rows with values should be sorted as well as the set of rows that have been grouped and hidden.
Here is my VBA for it so far:
' A9 has a formula in it that finds the location of first key word. [=MATCH("Week Two WORK",B:B,0)]
' A10 has a formula in it that finds the location of the second key word. [=MATCH("Week Two Totals",C:C,0)+14]
' Using a range with these rows I clear the outline of them and then group them
Range("B" & Range("A9"), "T" & Range("A10")).Select
Selection.Rows.ClearOutline
Selection.Rows.Group
' A5 has a formula in it that takes the value from A9 and adds 2 to it to get the start of a range with just project values
' A6 has a formula in it that takes the value from A10 and subtracts 2 from it to get the end of a range with project values
'Using the values from A5 and A6, this sets the range of cells with project descriptions to be selected and sorts them by alphanumeric values using two rows
Range("B" & Range("A5"), "T" & Range("A6")).Select
ActiveWorkbook.Worksheets("Timecard Proration").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Timecard Proration").Sort.SortFields.Add Key:=Range("R" & Range("A5").Value, "R" & Range("A6").Value), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Timecard Proration").Sort.SortFields.Add Key:=Range("B" & Range("A5").Value, "B" & Range("A6").Value), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Timecard Proration").Sort
.SetRange Range("B" & Range("A5"), "T" & Range("A6").Value)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' A8 has a formula in it that finds the last row within an array.
' I use a range that is larger than the selected range used for sorting values because this way when I add rows within the range it updates dynamically.
' Ideally I would like to use the row values from A5 and A6 for this.
' A8 = IF(SUMPRODUCT(MAX((ROW(T61:T92))*(T61:T92<>"")*(T61:T92<1)))=0,A5,SUMPRODUCT(MAX((ROW(T61:T92))*(T61:T92<>"")*(T61:T92<1)))+1)
' This selects the set of rows without hours entered in column R and puts them into a group, and hides them.
Range("B" & Range("A8"), "T" & Range("A6")).EntireRow.Select
Selection.Rows.Group
Selection.EntireRow.Hidden = True
' This selects the range of rows that have hours entered in row R and selects them.
Range("B" & Range("A5"), "T" & Range("A7")).EntireRow.Select
End Sub
I'm trying a separate command button to try and get the result from A8 to return the correct value. But I'm not getting very far with it.
Private Sub CommandButton6_Click()
Dim W2WORKPjWHEnd As String
'If SUMPRODUCT(MAX((ROW(T60:T91))*(T60:T91<>"""")*(T60:T91<1)))=0 is the equation I'm trying to replicate
W2WORKPjWHEnd.Formula = "=IF(SUMPRODUCT(MAX((ROW(T60:T91))*(T60:T91<>"""")*(T60:T91<1)))=0,A5,SUMPRODUCT(MAX((ROW(T60:T91))*(T60:T91<>"""")*(T60:T91<1))))"
MsgBox W2WORKPjWHEnd 'This is the row location result for Week 2 Work Project codes with hours (Should be sorted by the hours column first)
End Sub
Any help would be great. Thanks in advance.
I'm trying to get the formulas out of the sheet cells and into variables in VBA that I can use for a range references.
The first step is to find the starting point for the first range.
I need to search column B for a specific set of text. Set that as the start of the range.
Then search column B for a second specific set of text. Set that as the end of the range.
I use these two row values and select a range with rows above them and below them to create a large row group.
Then I sort that range of rows based on column R and column C values.
Then I use a formula in a cell that compares an array of two ranges within the larger range to get the last row with a value in it.
I then use this row number as the start of the range that I used to create a row grouping that I can hide.
The result should be a large group of rows with a smaller group of rows that have been hidden and only the rows with values in column R are displayed. The set of rows with values should be sorted as well as the set of rows that have been grouped and hidden.
Here is my VBA for it so far:
' A9 has a formula in it that finds the location of first key word. [=MATCH("Week Two WORK",B:B,0)]
' A10 has a formula in it that finds the location of the second key word. [=MATCH("Week Two Totals",C:C,0)+14]
' Using a range with these rows I clear the outline of them and then group them
Range("B" & Range("A9"), "T" & Range("A10")).Select
Selection.Rows.ClearOutline
Selection.Rows.Group
' A5 has a formula in it that takes the value from A9 and adds 2 to it to get the start of a range with just project values
' A6 has a formula in it that takes the value from A10 and subtracts 2 from it to get the end of a range with project values
'Using the values from A5 and A6, this sets the range of cells with project descriptions to be selected and sorts them by alphanumeric values using two rows
Range("B" & Range("A5"), "T" & Range("A6")).Select
ActiveWorkbook.Worksheets("Timecard Proration").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Timecard Proration").Sort.SortFields.Add Key:=Range("R" & Range("A5").Value, "R" & Range("A6").Value), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Timecard Proration").Sort.SortFields.Add Key:=Range("B" & Range("A5").Value, "B" & Range("A6").Value), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Timecard Proration").Sort
.SetRange Range("B" & Range("A5"), "T" & Range("A6").Value)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' A8 has a formula in it that finds the last row within an array.
' I use a range that is larger than the selected range used for sorting values because this way when I add rows within the range it updates dynamically.
' Ideally I would like to use the row values from A5 and A6 for this.
' A8 = IF(SUMPRODUCT(MAX((ROW(T61:T92))*(T61:T92<>"")*(T61:T92<1)))=0,A5,SUMPRODUCT(MAX((ROW(T61:T92))*(T61:T92<>"")*(T61:T92<1)))+1)
' This selects the set of rows without hours entered in column R and puts them into a group, and hides them.
Range("B" & Range("A8"), "T" & Range("A6")).EntireRow.Select
Selection.Rows.Group
Selection.EntireRow.Hidden = True
' This selects the range of rows that have hours entered in row R and selects them.
Range("B" & Range("A5"), "T" & Range("A7")).EntireRow.Select
End Sub
I'm trying a separate command button to try and get the result from A8 to return the correct value. But I'm not getting very far with it.
Private Sub CommandButton6_Click()
Dim W2WORKPjWHEnd As String
'If SUMPRODUCT(MAX((ROW(T60:T91))*(T60:T91<>"""")*(T60:T91<1)))=0 is the equation I'm trying to replicate
W2WORKPjWHEnd.Formula = "=IF(SUMPRODUCT(MAX((ROW(T60:T91))*(T60:T91<>"""")*(T60:T91<1)))=0,A5,SUMPRODUCT(MAX((ROW(T60:T91))*(T60:T91<>"""")*(T60:T91<1))))"
MsgBox W2WORKPjWHEnd 'This is the row location result for Week 2 Work Project codes with hours (Should be sorted by the hours column first)
End Sub
Any help would be great. Thanks in advance.
