Charlene Durand
Board Regular
- Joined
- Sep 19, 2015
- Messages
- 99
- Office Version
- 365
- Platform
- Windows
With EE we are now having to report on fixed and variable. I have a excel doc that when you press the "magic Button" it throws out what you are wanting. but now that we have to split is there not a possibilty that i can have the excel doc first give me the Fixed amounth then the Variable on the next row.
this is my Code:
this is my Code:
VBA Code:
Private Sub CommandButton1_Click()
Dim arrIncl() As Variant
InputRow = 4
With Sheet4
EndRow = .Range("A650000").End(xlUp).Row
If Not EndRow >= 4 Then EndRow = 4
.Range("A4:O" & EndRow).ClearContents
End With
With Sheet2
i = 1
EndRow = .Range("A650000").End(xlUp).Row
For RowNum = 1 To EndRow
If .Range("D" & RowNum) = "x" Then
ReDim Preserve arrIncl(i)
arrIncl(i - 1) = .Range("A" & RowNum)
i = i + 1
End If
Next
End With
With Sheet5
EndRow = .Range("D650000").End(xlUp).Row
For RowNum = 8 To EndRow
If .Range("D" & RowNum) <> "" Then pStart = RowNum: EmployeeNo = .Range("D" & RowNum) Else GoTo NextRow
Do Until EmployeeNo <> .Range("D" & RowNum) And Not pStart = RowNum
RowNum = RowNum + 1
If RowNum > EndRow Then Exit For
Loop
pEnd = RowNum - 1
EmpCode = .Range("D" & pStart)
' Person = .Range("E" & pStart)
' NameCount = UBound(Split(Person, " "))
Surname = .Range("E" & pStart)
FirstName = .Range("F" & pStart)
Sheet4.Range("A" & InputRow).Select
Sheet4.Range("A" & InputRow) = EmpCode
Sheet4.Range("B" & InputRow) = FirstName
Sheet4.Range("C" & InputRow) = Surname
For pRow = pStart To pEnd
If UBound(Filter(arrIncl, .Range("G" & pRow))) >= 0 And .Range("G" & pRow) <> "" Then
For mOffset = 0 To 11
Sheet4.Range("D" & InputRow).Offset(0, mOffset) = Sheet4.Range("D" & InputRow).Offset(0, mOffset) + .Range("H" & pRow).Offset(0, mOffset)
Next
End If
Next
RowNum = RowNum - 1
InputRow = InputRow + 1
NextRow:
Next
End With
End Sub
Private Sub CommandButton2_Click()
With Sheet4
EndRow = .Range("A650000").End(xlUp).Row
If Not EndRow >= 4 Then EndRow = 4
.Range("A4:O" & EndRow).ClearContents
End With
End Sub
Last edited by a moderator: