Solved: For loop using columns as counter?

[wolf.stalker]

hey all. this is probably a simple fix...but i can't see those darn trees...stupid forest is in the way!

the goal

i have a summary sheet that gets all info from the other sheets in the workbook. i have like 6 of these workbooks.

i have yet another workbook that when code is run, will pull the data from the first sheet of each workbook and concat it all on one sheet.

the problem i am having is when it pulls from each book, the link is not absolute, so i am trying to go back and fix all the data(summary sheets in all 6 books). there is a LOT of cells that need absoulte ref and i don't have time to do them all by hand.

with what i have below, i can simply copy and paste and do it for each Column letter but would like to find a cleaner way of doing this.

now i have a loop that will function for each row that has data...got that...but i can't seem to figure out how to do a loop using the columns or column count? that make sense? here is my code for rows..what i would like to do is s

a = starting column(will be "A")
z = ending column (may only be "J" or "U" or even "B")

For var = A to Z
-----call code segment below
next var
*******************************************************


RowCounterStarter = 4
RowCount = ActiveSheet.Range("A1").CurrentRegion.Rows.Count

For i = 2 To RowCount
RowCounterStarter = RowCounterStarter + 1
Range("D" & i).Formula = "='T-ID (Contract)'!$D$" & RowCounterStarter
Next i

[mikerickson]

I don't understand what you mean by "for each column."
Why do you need absolute references?

This will do what your code did without looping.

rowcount = ActiveSheet.Range("A1").CurrentRegion.Rows.Count

With ActiveSheet
Range(.Cells(2, 4), .Cells(rowcount, 4)).FormulaR1C1 = "='T-ID (Contract)'!R[3]C4"
End With

[wolf.stalker]

The code i have below works great for all items in Column D. However, I have items I need to make absolute in column B:J, not just in D.

the reason behind this....i have 6 wrkbks. each of those has a summary sheet.

(example)
workbook 1 has summary sheet, sheet A, sheet B, sheet C....Sheet Z
the summary sheet pulls info from all 26 sheets into one sheet for that workbook.

i have another macro in another wrkbk (not related) that goes out, grabs the summary sheet in each of the 6 wrkbks above. it then concatenates them all on ONE sheet in a new workbook.

sheet 1 will fill A1:J50 (example)
sheet 2 will fill A51:J100 (example)
and so on.

when the sheets are lined up in this fashion the data thats on those sheets is all links to other data throughout their respected workbook. the first sheet will post correctly as the info to be coppied is in cells A1:J50. sheet two's info is also in cells A1:J50(on it's own sheet) but when it concatenates them, it starts off at A50 and then the link tries to start up at A50 instead of A1 and so on for all books.

w/o the absolute ref, all i get is ZERO's because it's linking to empty cells that contain nothing for all sheets after the first (i have no data in my summary sheets past A50). when i make all the cells absolute, then all the data is presented the way i need. i basicly get all 6 summary sheets stacked on top of each other.

does this clear it up any at all?

RowCounterStarter = 4
RowCount = ActiveSheet.Range("A1").CurrentRegion.Rows.Count

For i = 2 To RowCount
RowCounterStarter = RowCounterStarter + 1
Range("D" & i).Formula = "='T-ID (Contract)'!$D$" & RowCounterStarter
Next i

[mikerickson]

It might be easier to move values rather than formulas.
Adjusting R1C1 formulas in a regular fashion can be done with something like this.
This will put the formulas for Sheet1, Sheet2, Sheet3, and Sheet4 in A1:J200 of Summary, in 4 fifty row blocks.


Dim i As Long

For i = 1 To 4
With ThisWorkbook.Sheets("Summary").Cells((50 * (i - 1) + 1), 1)
With .Range("a1:j50")
.FormulaR1C1 = "=Sheet" & i & "!R[-" & (50 * (i - 1)) & "]C"
End With
End With
Next i

[mikerickson]

Or you could use Insert to have Excel adjust the relative refereneces.

Dim oneSheet As Worksheet
Dim oneSheetRowCount As Long

ThisWorkbook.Sheets("summary").Range("A:J").ClearContents

For Each oneSheet In ThisWorkbook.Sheets
If oneSheet.Name <> "Summary" Then
oneSheetRowCount = oneSheet.Cells(oneSheet.Rows.Count, 1).End(xlUp).Row

With ThisWorkbook.Sheets("summary")
.Range("a1").Resize(oneSheetRowCount, 10).Insert shift:=xlDown
.Range("a1").Resize(oneSheetRowCount, 10).FormulaR1C1 = "=" & oneSheet.Name & "!RC"
End With
End If
Next oneSheet
If you attach an example, the details of your situation can be addressed.
Where I'm coming from is that its much faster to write one formula (with relative references) per sheet, adjusting both the formula and the range it's written to, for each sheet, rather than looping through 500 cells for each sheet's transfer.
One bulk write-to-SummarySheet per source sheet is more than 500 times faster than a write-to-SummarySheet for each source cell.

[wolf.stalker]

Mike:

thanks for your reply. i don't know if your idea will work for what i am looking for, but i will keep playing with it.

all i was really looking for was a way to run a LOOP and incrimenting by column letters, opposed to rows.

i can count how many rows of info i have, and can run a loop from 1 to this number of times.

i can count how many columns i have, but i am not sure how to make the +1 to a column letter work....

thats all i was trying to do :-)

[Bob Phillips]

Use Cells instead of Range



Cells(rownumber, columnnumber)


Both row and column numbers are numeric.