Hi & thanks for taking the time to click on my post.
As the title suggests I'm having some issues with the ForEach row function.
In an attempt to automate our company's extremely painful payroll function, i'm trying to create a function that will scrape a spreadsheet, as below, and for each Pay Code Column with an amount, output a line to csv file in the format of,
ECode,PCode,PValue
eg, for:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Employee name[/TD]
[TD]Employee Code[/TD]
[TD]Base Wage[/TD]
[TD]PayCode1[/TD]
[TD]PayCode2[/TD]
[TD]PayCode3[/TD]
[/TR]
[TR]
[TD]Test Employee1[/TD]
[TD]TEST1[/TD]
[TD]400[/TD]
[TD]250[/TD]
[TD][/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]Test Employee 2[/TD]
[TD]TEST2[/TD]
[TD]250[/TD]
[TD][/TD]
[TD]182[/TD]
[TD]45[/TD]
[/TR]
</tbody>[/TABLE]
I am hoping to eventually end up with:
TEST1,PayCode1,250
TEST1,PayCode3,38
TEST2,PayCode2,182
TEST2,PayCode3,45
I have tried to think through the logic and hoped I might be able to accomplish this with a ForEach Row Function, the underlying logic being:
FOR EACH ROW :SAVE EMPLOYEE CODE as VARIABLE1
FOR EACH COLUMN: SAVE COLUMN HEADING AS VARIABLE2
IF COLUMN HEADING CONTAINS DATA CHECK CELL
IF CELL CONTAINS DATA
WRITE LINE TO CSV : VARIABLE1,VARIABLE2,CELLCONTENTS
I hadn't quite figured out check each column heading yet, and was just starting to get the basics hashed out to save the variables if the cell contains data, however it seems that it keeps looping around over the same cell and doesn't progress any further. Am I missing something or is the entire premise flawed?
Any help would be greatly appreciated.
VB code:
Sub Process()
Dim rng As Range
Dim row As Range
Dim cell As Range
Dim ECode As String
Dim PCode As String
Dim PValue As Integer
Dim CellAdd As Range
Set rng = Range("B2:R15")
'move to first row of actual data
Range("B2").Select
For Each row In rng.Rows
'Move the cursor to Employee Code column'
Cells(ActiveCell.row, 2).Select
'Set employee code as variable
ECode = ActiveCell.Value
Cells(ActiveCell.row, 4).Select
For Each cell In row.Cells
'If cell is numeric save as variable PValue, scrape column header & save as variable PCode
If IsNumeric(ActiveCell) Then
PValue = ActiveCell.Value
Cells(1, ActiveCell.Column).Select
PCode = ActiveCell.Value
Range("A9").Value = ECode
Range("B9").Value = PCode
Range("C9").Value = PValue
End If
Next cell
Next row
End Sub
As the title suggests I'm having some issues with the ForEach row function.
In an attempt to automate our company's extremely painful payroll function, i'm trying to create a function that will scrape a spreadsheet, as below, and for each Pay Code Column with an amount, output a line to csv file in the format of,
ECode,PCode,PValue
eg, for:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Employee name[/TD]
[TD]Employee Code[/TD]
[TD]Base Wage[/TD]
[TD]PayCode1[/TD]
[TD]PayCode2[/TD]
[TD]PayCode3[/TD]
[/TR]
[TR]
[TD]Test Employee1[/TD]
[TD]TEST1[/TD]
[TD]400[/TD]
[TD]250[/TD]
[TD][/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]Test Employee 2[/TD]
[TD]TEST2[/TD]
[TD]250[/TD]
[TD][/TD]
[TD]182[/TD]
[TD]45[/TD]
[/TR]
</tbody>[/TABLE]
I am hoping to eventually end up with:
TEST1,PayCode1,250
TEST1,PayCode3,38
TEST2,PayCode2,182
TEST2,PayCode3,45
I have tried to think through the logic and hoped I might be able to accomplish this with a ForEach Row Function, the underlying logic being:
FOR EACH ROW :SAVE EMPLOYEE CODE as VARIABLE1
FOR EACH COLUMN: SAVE COLUMN HEADING AS VARIABLE2
IF COLUMN HEADING CONTAINS DATA CHECK CELL
IF CELL CONTAINS DATA
WRITE LINE TO CSV : VARIABLE1,VARIABLE2,CELLCONTENTS
I hadn't quite figured out check each column heading yet, and was just starting to get the basics hashed out to save the variables if the cell contains data, however it seems that it keeps looping around over the same cell and doesn't progress any further. Am I missing something or is the entire premise flawed?
Any help would be greatly appreciated.
VB code:
Sub Process()
Dim rng As Range
Dim row As Range
Dim cell As Range
Dim ECode As String
Dim PCode As String
Dim PValue As Integer
Dim CellAdd As Range
Set rng = Range("B2:R15")
'move to first row of actual data
Range("B2").Select
For Each row In rng.Rows
'Move the cursor to Employee Code column'
Cells(ActiveCell.row, 2).Select
'Set employee code as variable
ECode = ActiveCell.Value
Cells(ActiveCell.row, 4).Select
For Each cell In row.Cells
'If cell is numeric save as variable PValue, scrape column header & save as variable PCode
If IsNumeric(ActiveCell) Then
PValue = ActiveCell.Value
Cells(1, ActiveCell.Column).Select
PCode = ActiveCell.Value
Range("A9").Value = ECode
Range("B9").Value = PCode
Range("C9").Value = PValue
End If
Next cell
Next row
End Sub