Hello,
I am working on a project in a workbook with two sheets. One sheet (Sheet1) comes directly from an external program and I am trying to clean it up on the second sheet (Sheet2).
Sheet1 column B has a list of hundreds of account numbers in "****-***-**" where * is always an integer or a letter. However, they do not occur every line, at equal intervals, or without reoccurrence
I have already used a macro to sort through this column B and place every unique account number in column A of Sheet2 starting in row 6. It is:
In Sheet2 I also have columns for January-December
Now for where I'm stuck. ColumnI of Sheet1 contains intermittent text that reads "PERIOD XX ACTIVITY" where "XX" is 01-12. Where 01 is January, 02 in February, etc. The value that I want to get off of Sheet1 and into Sheet2 is always four cells to the right of the "PERIOD XX ACTIVITY" cell (therefore it is always in column M). Thus I have used the following to copy the cell that I want. I have one for each of the 12 months.
What I don't know is to tell it where or how to paste. Basically, it needs to paste on Sheet2 in the cell that is the intersection of the column (month) and row (account number) that it is associated with.
To find the column in Sheet2 it needs to go in: go four cells to the left of the copied cell. If "PERIOD 01 ACTIVITY", put in January column. If "PERIOD 02 ACTIVITY", put in February column, etc.
To find the row in Sheet2 it needs to go in: go up from copied cell until a blank cell is selected. Then go back to column B in that row and go down EITHER one or NO cells. If there is a "Beginning of Year" balance for that account, go down one cell in column B. Here is an example of this. If there is no "Beginning of Year" balance for that account, go down no cells in column B. Here is an example of this. If "0000-000-01", put in row with "0000-000-01" in column A in Sheet2. If "0000-000-02", put in row with "0000-000-02" in column A in Sheet2.
I realize this is terribly confusing so I've linked to screenshots of a dumbest down version of what I'm dealing with. I've kept all the rows and columns the same but left out all of the superfluous data.
Thank you so much for any help! Please let me know what needs more clarification.
Best,
I am working on a project in a workbook with two sheets. One sheet (Sheet1) comes directly from an external program and I am trying to clean it up on the second sheet (Sheet2).
Sheet1 column B has a list of hundreds of account numbers in "****-***-**" where * is always an integer or a letter. However, they do not occur every line, at equal intervals, or without reoccurrence
I have already used a macro to sort through this column B and place every unique account number in column A of Sheet2 starting in row 6. It is:
Code:
Sub Add_Account_Numbers()
Dim lRowCount&
lRowCount = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
With Sheets("Sheet2").Range("A6").Resize(lRowCount)
.Formula = "=IF(MID('Sheet1'!B1,5,1)=""-"",MID('Sheet1'!B1,1,11),"""")"
.Value = .Value
.Columns("A").Sort key1:=Range("A6"), _
order1:=xlAscending
End With
Range("A6", Range("A20000").End(xlUp)).NumberFormat = "General"
ActiveSheet.Range("A6:A20000").RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub
In Sheet2 I also have columns for January-December
Now for where I'm stuck. ColumnI of Sheet1 contains intermittent text that reads "PERIOD XX ACTIVITY" where "XX" is 01-12. Where 01 is January, 02 in February, etc. The value that I want to get off of Sheet1 and into Sheet2 is always four cells to the right of the "PERIOD XX ACTIVITY" cell (therefore it is always in column M). Thus I have used the following to copy the cell that I want. I have one for each of the 12 months.
Code:
Sub Add_Account_Values() Dim Rng As Range, rCell As Range
Worksheets("Sheet1").Activate
Set Rng = Range("I1:I20000")
For Each rCell In Rng.Cells
If rCell.Value = "PERIOD 01 ACTIVITY" Then
rCell.Select
ActiveCell.Offset(, 4).Copy
Exit For
End If
Next rCell
End Sub
What I don't know is to tell it where or how to paste. Basically, it needs to paste on Sheet2 in the cell that is the intersection of the column (month) and row (account number) that it is associated with.
To find the column in Sheet2 it needs to go in: go four cells to the left of the copied cell. If "PERIOD 01 ACTIVITY", put in January column. If "PERIOD 02 ACTIVITY", put in February column, etc.
To find the row in Sheet2 it needs to go in: go up from copied cell until a blank cell is selected. Then go back to column B in that row and go down EITHER one or NO cells. If there is a "Beginning of Year" balance for that account, go down one cell in column B. Here is an example of this. If there is no "Beginning of Year" balance for that account, go down no cells in column B. Here is an example of this. If "0000-000-01", put in row with "0000-000-01" in column A in Sheet2. If "0000-000-02", put in row with "0000-000-02" in column A in Sheet2.
I realize this is terribly confusing so I've linked to screenshots of a dumbest down version of what I'm dealing with. I've kept all the rows and columns the same but left out all of the superfluous data.
Thank you so much for any help! Please let me know what needs more clarification.
Best,
Last edited: