Formula in loop stops working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a problem that I cant seem to find a solution to.

-----------

Do While rowcount < 24

Tbook.Worksheets("Data1").Cells(i, j) =
"=VLOOKUP(RC[-1],Library!R2C[-2]:R192C _ [1],3,FALSE)"
rowount = rowcount + 1
j = j + 1
Loop

-----------

In the first iteration/cell the formula:
=VLOOKUP(B21,Library!A$2:D$192,3,FALSE)
comes up in the cell. (This is the way I want it).

For my next iteration/cell following shows up:
=VLOOKUP(RC[-1],Library!R2C[-2]:R192C[1],3,FALSE)
i.e. it does not change the formula like it should. It fills the cell like
it was text.

Does anyone a solution to this?

Thanx in advance
Jim
 
Jim,

Perhaps your cell was formatted for text. Try

Do While RowCount < 24
With Tbook.Worksheets("Data1").Cells(i, j)
.NumberFormat = "General"
.FormulaR1C1 = "=VLOOKUP(RC[-1],Library!R2C[-2]:R192C[1],3,FALSE)"
End with
RowCount = RowCount + 1
j = j + 1
Loop

HTH,
Bernie
MS Excel MVP
 
It worked. Now I dont need to work overtime (today) =)

Thanx

Bernie Deitrick said:
Jim,

Perhaps your cell was formatted for text. Try

Do While RowCount < 24
With Tbook.Worksheets("Data1").Cells(i, j)
.NumberFormat = "General"
.FormulaR1C1 = "=VLOOKUP(RC[-1],Library!R2C[-2]:R192C[1],3,FALSE)"
End with
RowCount = RowCount + 1
j = j + 1
Loop

HTH,
Bernie
MS Excel MVP

Jim73 said:
Hi,

I have a problem that I cant seem to find a solution to.

-----------

Do While rowcount < 24

Tbook.Worksheets("Data1").Cells(i, j) =
"=VLOOKUP(RC[-1],Library!R2C[-2]:R192C _ [1],3,FALSE)"
rowount = rowcount + 1
j = j + 1
Loop

-----------

In the first iteration/cell the formula:
=VLOOKUP(B21,Library!A$2:D$192,3,FALSE)
comes up in the cell. (This is the way I want it).

For my next iteration/cell following shows up:
=VLOOKUP(RC[-1],Library!R2C[-2]:R192C[1],3,FALSE)
i.e. it does not change the formula like it should. It fills the cell like
it was text.

Does anyone a solution to this?

Thanx in advance
Jim
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Entering Formula using Array 1
Macro autofills formula when a cell value is changed 4
Progress Bar 2
Do..Loop in multi sheets 7
Loop don't work 1
Runtime error 1004 when trying to insert formula in cells 11
conditional format based on column statistics and loop over columns 1
Why are the two codes below not getting the right formula for myworksheet event change formula? 2

Back
Top