Replies

Appreciate it! Already a member and this is actually a discussion a lot of us have on the forums since the majority of discussions seem to revolve around various backup software technologies. I don’t really have a choice in that but do want to be able to ballpark how much storage I’m going to need, regardless of the software the company chooses to use.

Thanks.

I don’t know if this will help but I had the same need a few years back with a giant spreadsheet I worked with daily. The below macro worked for me at that time but this was in Excel 2003. It highlighs the entire row/column to the active cell. For example, if the active cell is L10, row 10, column L will be highlighted and meet at L10. In the workbook I had it saved in, I had it pasted into the Sheet in the VB Editor.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim RngRow As Range
Dim RngCol As Range
Dim RngFinal As Range
Dim Row As Long
Dim Col As Long

Cells.Interior.ColorIndex = xlNone

Row = Target.Row
Col = Target.Column

Set RngRow = Range(“A” & Row, Target)
Set RngCol = Range(Cells(1, Col), Target)
Set RngFinal = Union(RngRow, RngCol)

RngFinal.Interior.ColorIndex = 6

End Sub

This will work wonderfully. All I really needed was the two data extracts (Individual Reads and Total Reads) extracted out onto two separate sheets/books so that I could plot performance data. I can combine the sheets myself. It was interesting watching the macro in action as sheets went all the to Sheet502! 🙂

Thank you much.

Thanks Ian! I attempted something similar to this and only got the first ‘block’. The sample I attached is one very small data set out of what I extracted from the software. I need the macro to loop through the entire spreadsheet instead of just the first data block. Super appreciate the try though!

Wow, that is incredibly close to what I need! I’m trying again with the attachment. I’ve created sample data for the attached file that will all look the same but will hopefully work for this incredibly weird thing I need to get done. So in the attachment, I have split up the tables on separate tabs.

Tab 1/Table 1 can be considered as a replica of what the finance system will dump out related to our positions. So in basic terms, the data I can get out of the system is in Rows 4 to 13. Pretend that the ‘positions’ in Column A are 10 separate employees. On their timecards, they will all charge to the Payroll Object codes in Row 3. Each object has a total amount that that is budgeted there for a full year for all employees.

Tab 2/Table 2 is what I have created using other data dumps and pivot tables. The data I will have available to work from is the Position No (column A), Account No (column B), and the percentage per employee (column C) from historical data. For budgeting purposes, I now need to divvy up where to get the money from to pay everyone. In other words, I can get the first Account No and need to automatically create the remainder by inserting the Object codes from Tab 1 (the only change). Once the accounts are created, using the percentage from Column C, I need to go to Tab 1 and calculate the amount by multiplying the percentage in Tab 2/Column C by the amount in Tab 1/Columns C to P. As you can see, this is quite laborious the way I am currently doing (mainly manually), so I was hoping the Excel gurus in this forum had better ideas.

Tried that. It doesn’t work. While the sample data is in one table, the account numbers are in another.

Table 1, has a list of the Position numbers with the amounts that are associated with the Object numbers.

Table 2, has the Position number, Account number, and percentage. The other data is from me as I attempted various ways to automate this.

What I am trying to do is, look at the position number on Table 1, get the initial Account number from Table 2, create 11 more Account numbers on Table 2 by grabbing the rest of the Object numbers from Table 1, have them inserted in the same column in Table 2 (next empty row), then perform the calculation of taking the amount associated with the object from Table 1 and multiply it with the percentage on Table 2.

Getting closer. Thank you!

Thank you for the quick response. I may not have explained it correctly but here I go again.
1. We have 24 character account shells that are associated with the position no (column A). Each of the account shells have percentages associated with them. (100 employees all associated with all the object codes but the fund/ddf/proj/cct all vary)
2. I want to create 11 duplicate shells and percentages for each string. Each of the duplicate shells shall have one of the other 11 object codes inserted into the shell. So say, my initial string is 01-51400-61111-00000-000. I want the 11 more that are created to have the other object codes from Row 3.
3. Then I want to multiply the percentage assigned to each string with the amount in Row 4.

Did that even make sense?
Thanks.

Ah. Got it. Thank you.

Tried just that. More than once. Even tried installing Firefox. Both browsers are under the impression that the PC is a mobile device. I know it’s a registry settings or User agent issue but I don’t know which one to change and was hoping someone else had some ideas. Thank you though!

That actually works BEAUTIFULLY! Thank you so much!

Appreciated! I tried the formula and the left justify and my totals changed. But thank you so much for trying! I think I’m going to have to go with the manual effort with this entire workbook since I can’t figure out what is going on with that column.

Thank you all for the assistance.

Tried that too. After I do the paste and then format, the problem date cells convert to the numeric value for the date. The ‘okay’ cells are still fine. I’m honestly baffled. No clue what is going on with these cells.

Thanks.

Hmm, I tried that on my end without any luck. The cells with the ‘correct’ format were actually formatted as ‘General’ on my end in order to work with the formula. I tried switching them all to the mm/dd/yyyy format but it did not help on the formula end (at least for me) with the problem cells. The other cells seem to be okay either way.

Thanks.

Will do!

Thank you again.
aapke

Ah. That may just be it. My copy of Word 2007 shows as “MS Word 2007 (12.0.6514.5000) SP2 MSO (12.0.6521.5000). Hopefully that’ll fix everything!

Thank you again for ALL the assistance.
aapke