Loop until end of table help?

[BBrandt]

I'm having trouble with figuring out how to loop through a table of data that has been input.

The goal is to copy two values (Sheet1 A2, C2) from the table, put them on the same place on another table (Sheet 2 $B$3,$B$4), do a quick calculation, and copy the result back to the first table (Sheet1 A5). I have accomplished this for one row, as follows...

Sub TableMacro()
    'Copy cells from first sheet to appropriate place on second sheet
    Sheets("SHEET2").Range("B3") = Sheets("SHEET1").Range("A2")
    Sheets("SHEET2").Range("B4") = Sheets("SHEET1").Range("C2")
 
    'Run Macro (will put result on sheet 2, cell B10)
    CalcMacro
 
    'Copy results back to table on first sheet
    Sheets("SHEET1").Range("F2") = Sheets("SHEET2").Range("B10")
 
End Sub

...but for the life of me I cannot figure out how to rewrite this so it changes A2, C2, and F2 to be A3, C3, F3, then, A4, C4, F4, etc. until the table ends.

Am I going about this wrong? Any advice would be greatly appreciated. I've been at this for a couple days now with no luck. Thanks in advance!

 

[Richard Schollar]

Hi

What's the calculation you are doing on the values? Is there a particular reason you want to copy the values to another sheet before copying back again? It would be much more efficient to just process the values in your code in one go (possibly dependent on the complexity of the calculation and what other parameters are required).

 

[BBrandt]

The reason for copying them to and from another worksheet is that in doing so, I am essentially automating the input of another already functioning workbook with an already functioning macro that does a number of rather complex calculations. Basically, that workbook does a series of calculations for one instance, and the one I'm working on now automatically comes up with anywhere from about 4 to about 40 instances, and I want to run them through the other workbook to generate results for all instances, rather than just one. In short, the 'single instance' workbook works, and as they say, "if it ain't broke don't fix it!"

Basically what I've done is made a copy of the 'single instance' workbook and inserted it as hidden sheets in this new workbook, and want to send data to and from it based on the table on sheet 1. The code I posted is simplified quite a bit from the actual code (though I certainly can post the actual code if you'd like), but if I can get this simpler code working I think I'll be able to apply it to the more complex code.

As a side note, while the rows in the table on sheet 1 will change, the input on sheet 2 should not. So A2, C2 should go to sheet 2, B3 & B4, A3, C3 should also go to sheet 2, B3 & B4, etc. etc.

Thanks for taking the time to help me with this, it's had me stuck now for days.

 

[Kaps_mr2]

Last_Activity_Line = Application.WorksheetFunction.Match("*", Worksheets("Data Sheet").Range("Table_Col"), -1)

should return the number of rows in a table - you need to define the dynamic range Table_Col to be a column in your table.

"Data Sheet" is the sheetname where the data is stored.

Kaps

 

[BBrandt]

Thanks for the suggestion, could you explain how exactly I might define that range? I'm a bit new to VBA (been teaching myself for the last month or so) and have not yet had to define a dynamic range. I think I could do it if I were defining a set range, but since I'm basically starting at A2 (or C2... though if A2 exists C2 will too so I can use A as the limiting column?) and going until I run out of values, I'm not sure how to accomplish this.

Either way, once I know the number of rows in the table, how would I pick cells from each row and send them off to the other sheet?

Thanks in advance, as you can tell, I can use just about any guidance that you guys can offer.

 

[Kaps_mr2]

1. Use a FOR NEXT LOOP to go through each row
2. Dynamic Ranges aren't VBA they are part of Excel.

I'm a bit busy right now - I will drop by later and see how this thread is progressing.

Kaps

 

[BBrandt]

Thanks for your help, but I don't think I understand what you're asking me to do. I set up a dynamic range (I think) for column A based on what I could find here (http://www.contextures.com/xlNames01.html) by inserting a name "Test" and defining the name to be "=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)"... but I don't know how to test if that worked, or how to put that into the code. Additionally, what would I put inside the For/Next loop? I can't figure out how I would address the cells I want to copy from within a loop because I can't directly call them out (A2 & C2 would not work for instance because the next iteration of the loop would/should use A3 & C3).

Sorry if these are rather basic questions, but as I said I'm rather new to using VBA and perhaps also at using Excel to this degree. I appreciate you taking the time to help me though!

 

[Norie]

Perhaps something like this.

Sub TableMacro()
Dim LastRow As Long
Dim I As Long
    LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    
    For I = 2 To LastRow
       'Copy cells from first sheet to appropriate place on second sheet
       Sheets("SHEET2").Range("B3") = Sheets("SHEET1").Range("A" & I)
       Sheets("SHEET2").Range("B4") = Sheets("SHEET1").Range("C" & I)
    
    'Run Macro (will put result on sheet 2, cell B10)
        CalcMacro
 
    'Copy results back to table on first sheet
        Sheets("SHEET1").Range("F" & I) = Sheets("SHEET2").Range("B10")
    Next I
End Sub

But I've got to say that the posts so far have been a little confusing, for me anyway.

 

[BBrandt]

Thanks!

Just so I'm sure I understand, when you say .Range("C" & I), you effectively are saying column C, whatever the current row number is as defined by your variable I, correct?

I think that'll work for my purposes, and I'll post an update here once I go try it out.

 

[BBrandt]

Works like a charm. Thanks a bunch for helping me out, I really appreciate it. I'd never used "&" to connect variable numbers with column labels before, and that basically solved my problem.

Thanks again!