How to return value of first non blank cell for each different account number?

[Kelvinator1]

I am trying to pull in the value of a certain account number that is 7 columns to the right of the account number, and then go down to the first non-blank cell in that column. Then I want to repeat for the next account number and so on. So, in the example, for account number 40000-300-00-20, I am trying to get the value of (1,474,221.16), which is also on the same line as "Accumulated From Beginning of This Year." Then I want to go to the next account number, 41200-300-00-20 and get the value (28,569.05). These are both the values in the last non-blank cells in Column I for each respective account number. These amounts will be pulled into another sheet as part of the P&L. What is the easiest way to do this? Any help would be greatly appreciated!

 

[Shortmeister1]

I'm afraid that the problem fairly fundamental. You need to restructure the data so that it is in ONE table. At the moment, you have a formatted report from which you are unsuccessfully trying to extract your data. Don't make it worse by trying to code your way out of a bad design.

There should be no blanks rows and the account number should be in a column, not as a disconnected header of multiple tables.

Once It's in a table format rather than a report, you'll be able extract any required data without recourse to Power Query or VBA.

Hope this helps.
Martin

 

[Kelvinator1]

Thanks, Martinshort! Unfortunately, this is the way the data is dumped out of SAP. If I have to delete blank rows and move account numbers around, it would be just as easy for me to manually go get the number from the "Accumulated From Beginning of This Year" and pull it into my P&L. I thought maybe there would be a way to do an Index/Match or some other function to go get the number, no matter where it is.

 

[jolivanes]

Just a quick cobbled together piece of code that works but which is not the most sophisticated.

Sub Maybe()
Dim c As Range
    For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If c.Value = "Revenues" Then Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, 1).End(xlUp).Offset(1).Value = c.Offset(1, 8).End(xlDown).Value
    Next c
End Sub

 

[Kelvinator1]

Jolivanes, I couldn't get that code to work, I pasted it into a blank macro and when I tried to run it, I got a "Subscript out of range" error.

 

[jolivanes]

Have you changed the references like sheet names to the proper names? You probably don't have a "Sheet2"

 

[Kelvinator1]

I just did, but nothing happens when I try to run the macro. I'm sure I'm not doing this right.

 

[jolivanes]

First you had a "Subscript out of range" error and now nothing happens at all. Maybe we are getting closer.
Have you checked everything like spelling (Revenues), no leading or trailing spaces, no empty cells in Column I, in other words, is your original exactly like the picture in Post #1?

 

[Kelvinator1]

Yes, the file is exactly the same as the screen shot I posted. There are blank cells in Column I, two between each different account number.

 

[jolivanes]

Did you check the sheet that you changed Sheets("Sheet2") into?
Show us your code after changing it.