Greetings,
I'm a VBA newbie--I did a bit of programming in VBA for Access a couple years ago, but haven't had any opportunities to work in VBA for Excel--and I was hoping to solicit some advice for how I might go about performing the following task in VBA:
In Sheet1: I have a dataset consisting of 1000 rows of data and ~25 columns (B3:AB1003)
In Sheet2:
-The range A6:A29 is populated with transposed values from a single row from Sheet1 (Sheet1!Bx:Yx, where x is the active row number from Sheet1).
-Another cell in Sheet2, AZ3, is populated with Sheet1!ABx, where x is the active row number from Sheet1.
-A bunch of other cells in Sheet2 contain either constant (i.e., static) values or formulas that reference the populated values (Sheet2!A6:A29 and Sheet2!AZ3) or other formula cells in Sheet2....basically, there's a lot of calculations going on in this sheet!
-The range BL6:BL21 contains the final results of the calculations for the specific Sheet1 row.
In Sheet3:
-This sheet would be set up to contain a 1000row by 16column matrix containing the values calculated in Sheet2!BL6:BL21 for each of the Sheet1 rows (e.g., Sheet3, row 1 contains results for Sheet1, row 1).
What I'm hoping to do is automate the process of populating Sheet2 with each row from Sheet1 and outputting the final calculated results in Sheet3. I imagine the general steps would look something like this:
1) Copy relevant values from first row in Sheet1.
2) Paste the copied values from Sheet1 into the relevant ranges in Sheet2.
3) Update all the formula fields in Sheet2 to reflect the copied values.
4) Copy the range in Sheet2 containing the final calculated results.
5) Paste the transposed range in the first row of Sheet3.
6) Loop through all the rows in Sheet1, with the final calculated results pasted into the next empty row in Sheet 3.
Like I said, I'm very much new to Excel VBA, so at this point I'm hoping figure out how to best break down the task into steps that can be handled efficiently in VBA. I think once I get those steps figured out, I'll be much better positioned to dive into the voluminous Excel for VBA manual I have at my disposal! Any advice--general or specfic--would be very much appreciated!
Last edited by leejc; 11-03-2011 at 07:10 PM. Reason: solved
Hi
Here goes
ryloSub aaa() Dim OutSH As Worksheet, DataSH As Worksheet, WorkSH As Worksheet Set OutSH = Sheets("Sheet3") Set WorkSH = Sheets("Sheet2") Set DataSH = Sheets("Sheet1") With DataSH For Each ce In .Range("B3:B" & .Cells(Rows.Count, 2).End(xlUp).Row) WorkSH.Range("A6:A29").Value = WorksheetFunction.Transpose(ce.Resize(1, 24).Value) WorkSH.Range("AZ3").Value = .Cells(ce.Row, 28).Value OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 16).Value = WorksheetFunction.Transpose(WorkSH.Range("BL6:BL21").Value) Next ce End With End Sub
Oh wow, that worked like a charm! Thank you! I didn't expect such a quick and specific solution, so you've saved me countless hours of work, although I fully intend to try to figure out how that VBA is working :-)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks