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!!!

 

[mamamia93]

So, after testing the macro, this was the result. The macro was able to somewhat postion the information correctly, but do you see that there is an account name that apears twice? Is there a way to put the 949588 account number in the white cell after the 257739 account number?

 

[mamamia93]

Ok, so after making some adjustments to the code you provided, I wanted to ask if there was a way to fix the following issues.

1. There are some account names that are appearing double on two different rows instead of having all the account numbers associated with one account name on a single row. (I am providing snippets below). As you can see, account number 949588 should be in the white cell next to 257739 because it's under the same name.

2. Is there a way to reference the chart from the "Proof" sheet starting from the 200th row instead of referencing the chart located on the "Insert Sheet" from A25?

 

[mamamia93]

So, I was not able to upload the original application because it contains private information, but below are two snippets of what the real application looks like. The first image is the "Proof" sheet and the second sheet is the "Info Input" sheet. On the 200th row of the "Proof" sheet, there is a chart containing reference data. The VBA code above only references the chart on the "Input Sheet". Is there a way to reference the code to the chart on the "Proof" sheet?

Thank you again for your help.

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

So, I was not able to upload the original application because it contains private information, but below are two snippets of what the real application looks like. The first image is the "Proof" sheet and the second sheet is the "Info Input" sheet. On the 200th row of the "Proof" sheet, there is a chart containing reference data. The VBA code above only references the chart on the "Input Sheet". Is there a way to reference the code to the chart on the "Proof" sheet?

Thank you again for your help.

 

[svendiamond]

To change the reference range (it's a table/range, it's not a chart...) just change the part of the code that says

Set refRange = .Range(A25:G35)"

I also realized that we don't want to search the entire reference range, just the column "A" (since columns D, E, and G also have numbers we don't care about) so let's change it to this:

Set refRange = proofSheet.Range(A199:A209)"

As far as the additional account numbers for the same Long Name, you'd have to search for that name first. So I've done that in the below updated code. This works on your test sheet, but I can see more problems arising from your screenshots of the real sheet. For instance, the merged columns and the fact that you have more columns for additional account numbers to the right now.

Option Explicit

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, longname As String, finRange As Range, foundName As Range
Set inputSheet = ThisWorkbook.Sheets("Input Sheet")
Set proofSheet = ThisWorkbook.Sheets("Proof")

With proofSheet
    nextRow = 4 ' waiting to adjust to normal table format
    Set refRange = .Range("A199:A209")
    Set finRange = .Range("B4:B198")
End With

With inputSheet
    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)
    If Not foundAcct Is Nothing Then
    
        longname = foundAcct.Offset(0, 1)
        Set foundName = finRange.Find(what:=longname)
        
        ' see if this name has already been found
        If Not foundName Is Nothing Then
            proofSheet.Cells(foundName.Row, "G") = acct
        Else
        
            proofSheet.Cells(nextRow, "E") = acct
            proofSheet.Cells(nextRow, "B") = longname
        End If
        
        nextRow = nextRow + 8   ' would be nicer to just add one row (see first note)
        Set foundName = Nothing
    End If

    Next r

End With

End Sub

 

[mamamia93]

So the code works great! Thank you so much. I did make adjustments, but your code is perfect. I do have a question. How can I trigger the code to run when I input data on the Input sheet? I already have a Worksheet_Change sub for something else on the input sheet. I placed the code you created in the proof sheet. Any tips?

 

[svendiamond]

Ok great, I'm glad it works. I understand you already have code utilizing your Worksheet_Change event, but why not simply add to it? You could leave this code in a separate module, and just "Call" it whenever there's a change.

Literally you could write:

Call loopything

But also you shouldn't have placed this code in the Proof sheet to begin with. You should, on the VB Editor, click Insert > Module and paste it there.