Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Looping Column in a Formula

Status
Not open for further replies.
vixvee

vixvee

Technical User
Sep 6, 2003
15
US
Hi, For example I want to put this formula in a loop.

ActiveCell.FormulaR1C1 = _
"=INDEX('[Name.xls]L'!R[-11]C[-3]:R[50]C,MATCH('[Name.xls]L'! CELL_LOOP ,'[Name.xls]L'!R[-11]C[-3]:R[50]C[-3],0),3)"

How can I make CELL_LOOP into a column array? How should I construct the for loop to loop through CELL_LOOP?

Thanks!
vix


'
 
Sort by date Sort by votes
Look at "application.worksheetfunction" in the help...I believe that the functions you are using are supported, but check the list.
 
Upvote 0 Downvote
Thanks ETID. I just want to know the syntax/method to use in for loop.

For Each Cell in Selection
ActiveCell.FormulaR1C1 = _
"=INDEX('[Name.xls]L'!R[-11]C[-3]:R[50]C,MATCH('[Name.xls]L'! Cell.Value ,'[Name.xls]L'!R[-11]C[-3]:R[50]C[-3],0),3)"
Next

I cannot use Cell.Value. It doesn't work. Do you know of other any way?
Thanks
 
Upvote 0 Downvote
Hello vixvee,

You don't need a For-Next Loop to do what you want to do.

Code: 
Selection.FormulaR1C1 = _
        "=INDEX('[Name.xls]L'!R[-11]C[-3]:R[50]C,MATCH('[Name.xls]L'!" & _
            " Cell.Value ,'[Name.xls]L'!R[-11]C[-3]:R[50]C[-3],0),3)"

Will work just fine! ;-)

I hope this helps!



Peace! [peace]

Mike

Never say Never!!!
Nothing is impossible!!!
 
Upvote 0 Downvote
Hi Mike :)

Cell.value actually refers to the values of 1 column as I loop through the column... I would still need th for loop for looping through the values in that column right?

Thanks,
vix
 
Upvote 0 Downvote
what are you trying to do here ???
Are you trying to propagate 1 formula down a column or across a row ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
 
Upvote 0 Downvote
Hi Geoff,

Actually, the cells in that 1 column serves as an input parameter to another marco. After transferring one value of that column, the macro is performed. So that is why I need to loop through the cells one at a time. match the value of one cell, do the macro and then move to the next cell. I am not familiar so much with the syntax involved in doing for loop and formula.

Thanks,
vix
 
Upvote 0 Downvote
To loop thru cells, you can use this syntax:

uCol = 5 'this would be the column that the formula goes in
lRow = cells(65536,uCol-1).end(xlup).row
for cell_loop = 2 to lRow
cells(i,uCol).FormulaR1C1 = _
"=INDEX('[Name.xls]L'!R[-11]C[-3]:R[50]C,MATCH('[Name.xls]L'! CELL_LOOP ,'[Name.xls]L'!R[-11]C[-3]:R[50]C[-3],0),3)"
next cell_loop

BUT - you can put the formula into all cells at once:
range("E2:E" & lRow).formular1c1 = yourformula
and then just loop thru the results.....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
 
Upvote 0 Downvote
Hi Geoff,

Actually, I only have 1 target cell. And I have a table as the source of my input to that one cell... Can you explain the algorithm you gave me? thanks.

Ex. I want to loop through these cell values....
Cell Input1
Cell Input2
Cell Input3


And store it one by one to:
Cell Target

Coz I will execute a marco before moving on to Cell Input 2 and so on...

Thanks,
vix
 
Upvote 0 Downvote
aaaaah - finally I think I see what you are doing

You have a table with various entries
You want to loop thru these entries, using them to complete a formula and return a result to 1 cell - then another macro fires, using that data then continue loop for next value in table

Can you confirm ??

That being the case, if the table is a named range called "tbl" then

Sub Iterate()
For each c in [tbl]
activecell.formulaR1C1 = _
"=INDEX('[Name.xls]L'!R[-11]C[-3]:R[50]C,MATCH('[Name.xls]L'!"& c.text &",'[Name.xls]L'!R[-11]C[-3]:R[50]C[-3],0),3)"
call YourOtherMacro
Next
end sub

should be close to what you want

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
 
Upvote 0 Downvote
Hi Geof, How do I assign a Range into tbl. It is not as easy as Range("A2:A60")=tbl
Sorry if I keep bugging you on this...
Thanks,
vix
 
Upvote 0 Downvote
vix,

tbl IS a range that has been named "tbl"

If your select a range, then go to the Name TextBox just above A1 on your sheet and type...

tbl

in that box, hit enter, then that range can be referenced by tbl..

For instance, you could say
Code: 
=SUM(tbl)
and if the range had numeric values, they would sum just as if you put a range in there

:)

Skip,
Skip@TheOfficeExperts.com
 
Upvote 0 Downvote
Hello Again,

Thanks Skip...

I dont know why but I am getting Application defined or Object defined Error on the "ActiveCell.FormulaR1C1.... " line. I actually have 2 files. Name and NameX. My table is in NameX and I want to apply the formula in the Name file...

Sub Iterate()
Dim c As Range
Windows("NameX.xls").Activate
For Each c In [tbl]
Windows("Name.xls").Activate
Range("D13").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('[NameX.xls]L'!R[-11]C[-3]:R[50]C,MATCH('[NameX.xls]L'!" & c.Text & ",'[NameX.xls]L'!R[-11]C[-3]:R[50]C[-3],0),3)"
Next
End Sub

Apologize if I am confusing you guys...

Thanks,
vix
 
Upvote 0 Downvote
Do you have a sheet named L in NameX?
Code: 
&quot;=INDEX('[NameX.xls]L'  <<<<
1 you're missing a numeric value for column here...
Code: 
&quot;=INDEX('[NameX.xls]L'!R[-11]C[-3]:R[50]C  <<<<
That's probably your problem! :)

Skip,
Skip@TheOfficeExperts.com
 
Upvote 0 Downvote
Oooppsss... you're right... Thanks very much! :)
 
Upvote 0 Downvote
I just added C[-3]... But I think C without column number is correct since I just recorded the macro and looked at the code for this...
L is actually the Sheet on NameX...

Sub Iterate()
Dim c As Range
Windows(&quot;NameX.xls&quot;).Activate
For Each c In [tbl]
Windows(&quot;Name.xls&quot;).Activate
Range(&quot;D13&quot;).Select
ActiveCell.FormulaR1C1 = _
&quot;=INDEX('[NameX.xls]L'!R[-11]C[-3]:R[50]C[-3],MATCH('[NameX.xls]L'!&quot; & c.Text & &quot;,'[NameX.xls]L'!R[-11]C[-3]:R[50]C[-3],0),3)&quot;
Next
End Sub

Thanks,
vix
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

IcarusHallsorts
  • Locked
  • Question
I have a column of data in Column A
Replies
3
Views
399
Andrzejek
Andrzejek
sal21
  • Locked
  • Question
scrping in listbox in a web page
Replies
0
Views
482
sal21
sal21
Andrzejek
  • Locked
  • Question
Hyperlinks in Word cut at #
Replies
2
Views
264
Andrzejek
Andrzejek
Andrzejek
  • Locked
  • Question
Data Type in ADODB.Recordset
Replies
6
Views
878
strongm
strongm
davefish
  • Locked
  • Question
Looping through a word doc to delete rows
Replies
7
Views
348
macropod
macropod

Part and Inventory Search

Sponsor

Back
Top