Looping through a Column on One Sheet and Posting to Specific Cells on a Second Sheet

[mamamia93]

I will try to simplify this problem so that maybe someone can provide an efficient solution.

I am dealing with an accounting application that I created in Excel, and I cannot seem to get the data in the format that I need. The attached workbook is a simplified version of the application because the original contains private information and tons of VBA code, and it would be too large to upload. The example workbook was built solely for this specific question.

The Excel workbook contains two sheets. The first sheet contains columns to input data "Input Sheet." This data is accounting information for clients I deal with daily. Column 'A' is the style of the transaction, for example, a transfer or a fee charge, etc.. Column 'B' is the direction of the transaction, for example, a contribution or withdrawal. Column 'C' is the account identifier (because an account can have multiple ways of being identified). Column 'D' is the identifying number, and column 'E' is the central account number, which is the column that needs focus and is what all the identifiers are trying to reference to. Basically, column D is the main identifying number.

The second sheet ("Proof") is the area where I save proof that the client requested this transaction to take place. So, there is a cell that is supposed to hold the client's name (column 'B') and a cell for the account number (column 'E') with which the transaction took place under that account name. Each client can have various account numbers under the same name. Also, around the 200th row is a list of the reference data I am pulling. I also placed the same table on the "Input Sheet" so that you can go ahead and play with the different variables and have an idea of what are the account numbers and names (The account names and numbers were generated with an online name and number generator).

What I need is a sort of loop that reads the account numbers in column 'E' on the "Input sheet" starting from 'E2' and takes that number and inputs it in the first row containing an empty white cell on the "Proof" sheet ('E4') under the account number section. Once the number is placed there, the "long name" (found in the table on the 200th row) associated with the account number in the cell 'E4' appears in the first empty white cell ('B4') under the account name section. Then, the loop continues and reads the next cell ('E3') on the "Input sheet", and if that account number belongs to the same name, place that number in the next white cell ('G4') on the "Proof" sheet. If the account number is not associated with the account name in cell 'B4', put it in the next line containing an empty white cell 'E12' and assign the appropriate name for that number in cell under account name in cell 'B12' and continue down the list in column 'E' on the Input sheet and repeat the process until all the account numbers on the first sheet are complete and all the appropriate account numbers are on the row that has the appropriate 'long name' for those account numbers.

I hope this request was clear. I am sorry for the length of the request, but I do believe there is a way to loop this process instead of manually inputting the information. Currently, the way that it is done, I have a dropdown list in each white cell on the "Proof" sheet with all the account numbers in column 'E' on the "input sheet" that when I choose that account number the number disappears from the list and the account name appears through an INDEX MATCH formula. This formula is not efficient because it only references the first cell, and I cannot know if the account numbers in the following cells on the same row are associated with the account name because the INDEX MATCH is only pulling from the cell in the 'E' column on the "Proof" sheet.

This process is done for thousands of accounts and the proof must be associated with the appropriate account names and numbers for auditing purposes. The proof is added with a macro button etc. etc..

Thank you in advance for your help!!!

 

[svendiamond]

I don't see any attachment... maybe you could upload to some kind of filesharing site or share with us via Google drive or something. Not sure what the rules are about that or if the forum allows you to attach a file. But it sounds like you basically want to loop through a list of stuff, look up a value and either paste related information or move to the next cell. Nothing too complex here just need to see the file. Also I wouldn't recommend having a separate table of information starting in the 200th row of a sheet. Why not just put that on its own sheet?

 

[mamamia93]

I don't see any attachment... maybe you could upload to some kind of filesharing site or share with us via Google drive or something. Not sure what the rules are about that or if the forum allows you to attach a file. But it sounds like you basically want to loop through a list of stuff, look up a value and either paste related information or move to the next cell. Nothing too complex here just need to see the file. Also I wouldn't recommend having a separate table of information starting in the 200th row of a sheet. Why not just put that on its own sheet?

Thank you for taking the time to read my post and reply. I will share with you a link so that you can see what I am talking about. The snapshots would not load here because the file is too big. Also, the reason I pasted the chart on the 200th row on the same sheet is that on the original application the chart contains thousands of rows of data. There are some formulas that will not work if the chart was on another sheet. I ran into a problem and that was the only solution.

 

[mamamia93]

I don't see any attachment... maybe you could upload to some kind of filesharing site or share with us via Google drive or something. Not sure what the rules are about that or if the forum allows you to attach a file. But it sounds like you basically want to loop through a list of stuff, look up a value and either paste related information or move to the next cell. Nothing too complex here just need to see the file. Also I wouldn't recommend having a separate table of information starting in the 200th row of a sheet. Why not just put that on its own sheet?

Here is the link to the file:

Excel VBA Looping Problem - Google Drive

drive.google.com

 

[svendiamond]

Is there any particular reason the Proof sheet is formatted like that? Can it just be a simple table (3 columns, A, B, C) and no blank rows? Or must it be spaced out like it is?

 

[svendiamond]

And is there only a change that there would be ONE additional account number? What if the next 3 lines are the same account?

 

[svendiamond]

I'm going to bed but you need something similar to this:

Sub loopything()


Dim inputSheet As Worksheet, proofSheet As Worksheet, refRange As Range, lastRow As Long, r As Long
Dim acct As String, foundAcct As Range, nextRow As Long
Set inputSheet = ThisWorkbook.Sheets("Input Sheet")
Set proofSheet = ThisWorkbook.Sheets("Proof")

With proofSheet
    nextRow = 4 ' waiting to adjust to normal table format
End With

With inputSheet

Set refRange = .Range("A25:G35")
    lastRow = 7 ' .cells(.rows.count, "E").end(xlup).row ' currently using 7 because you have that ref table in the way


For r = 2 To lastRow

    acct = .Cells(r, "E")
    Set foundAcct = refRange.Find(what:=acct)
    longname = foundAcct.Offset(0, 1)
    
    
    proofSheet.Cells(nextRow, "B") = acct
    proofSheet.Cells(nextRow, "E") = longname
    nextRow = nextRow + 8   ' would be nicer to just add one row (see first note)
    
Next r

End With

End Sub

 

[mamamia93]

Is there any particular reason the Proof sheet is formatted like that? Can it just be a simple table (3 columns, A, B, C) and no blank rows? Or must it be spaced out like it is?

Yes, it has to be spaced out like that. The reason is that on the original application sheet that this example is based on is organized in a way to contain files under those account numbers. Therefore, space is necessary. Logistically, the sheet was constructed in that way.

 

[mamamia93]

And is there only a change that there would be ONE additional account number? What if the next 3 lines are the same account?

So, there may be multiple account numbers under the same name. There can be few of the same numbers too. I would hope that no duplicates should occur. Only paste the account number once. For example, if the client makes transactions for four of his accountant two of them were the same account number, the cells should only contain the account number once.

 

[mamamia93]

I'm going to bed but you need something similar to this:

Sub loopything()


Dim inputSheet As Worksheet, proofSheet As Worksheet, refRange As Range, lastRow As Long, r As Long
Dim acct As String, foundAcct As Range, nextRow As Long
Set inputSheet = ThisWorkbook.Sheets("Input Sheet")
Set proofSheet = ThisWorkbook.Sheets("Proof")

With proofSheet
    nextRow = 4 ' waiting to adjust to normal table format
End With

With inputSheet

Set refRange = .Range("A25:G35")
    lastRow = 7 ' .cells(.rows.count, "E").end(xlup).row ' currently using 7 because you have that ref table in the way


For r = 2 To lastRow

    acct = .Cells(r, "E")
    Set foundAcct = refRange.Find(what:=acct)
    longname = foundAcct.Offset(0, 1)
   
   
    proofSheet.Cells(nextRow, "B") = acct
    proofSheet.Cells(nextRow, "E") = longname
    nextRow = nextRow + 8   ' would be nicer to just add one row (see first note)
   
Next r

End With

End Sub

Thank you very, very much. I will test it out and let you know how it ends up. I cannot thank you enough!