i'm using UsedRange.Columns.Count on a download file to determine number of
columns (which will increase over time) and am using a lookup formula to pull
data out of that file and into a model.

i am copying that formula over the same number of columns in the model.
problem is, the recorded macro has fixed values which won't handle an
increase. what kind of code will allow my formula to be copied over a
changing number of columns? if this has been discussed previously please
reference those posts, thanks!

It would be easier to answer if you showed your code for the lookup.

"mwam423" wrote:

i'm using UsedRange.Columns.Count on a download file to determine number of
columns (which will increase over time) and am using a lookup formula to pull
data out of that file and into a model.

i am copying that formula over the same number of columns in the model.
problem is, the recorded macro has fixed values which won't handle an
increase. what kind of code will allow my formula to be copied over a
changing number of columns? if this has been discussed previously please
reference those posts, thanks!

hi barb, thanks for response, here's the formula. note, dropper is variable
which is determined by date. just realized, could i replace the 6 in
ActiveCell.Offset with a variable based on RangeUsed.Columns.Count?

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[" & -(dropper - 1) & "]C,MW118487750.xls!C2:C9,8,FALSE)"
ActiveCell.Copy
Range(Selection, ActiveCell.Offset(, 6)).Select




"Barb Reinhardt" wrote:

It would be easier to answer if you showed your code for the lookup.

In order to ensure that the data is being extracted from the right column,
you need to change this:

"=VLOOKUP(R[" & -(dropper - 1) & "]C
to something like this
"=VLOOKUP(R[" & -(dropper - 1) & "]C1

Make sense?

HTH,
Barb Reinhardt
"mwam423" wrote:

hi barb, thanks for response, here's the formula. note, dropper is variable
which is determined by date. just realized, could i replace the 6 in
ActiveCell.Offset with a variable based on RangeUsed.Columns.Count?

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[" & -(dropper - 1) & "]C,MW118487750.xls!C2:C9,8,FALSE)"
ActiveCell.Copy
Range(Selection, ActiveCell.Offset(, 6)).Select




"Barb Reinhardt" wrote:

It would be easier to answer if you showed your code for the lookup.

not sure why C isn't referenced with a number, obtain good results across
columns, but will add the column number. any ideas bout how to copy to a
variable amount of columns


"Barb Reinhardt" wrote:

In order to ensure that the data is being extracted from the right column,
you need to change this:

"=VLOOKUP(R[" & -(dropper - 1) & "]C
to something like this
"=VLOOKUP(R[" & -(dropper - 1) & "]C1

Make sense?

shouldn't C be zero? that value is relative to cell where formula is, and
it's picking up info from the top row of the same column . .

"Barb Reinhardt" wrote:

In order to ensure that the data is being extracted from the right column,
you need to change this:

"=VLOOKUP(R[" & -(dropper - 1) & "]C
to something like this
"=VLOOKUP(R[" & -(dropper - 1) & "]C1

Make sense?

It's difficult to guess what you want.

We don't know what dropper is equal to. We don't know the location of the
activecell. And we don't know what that formula is really trying to do.

You may need to give more description to get more help.

mwam423 wrote:

shouldn't C be zero? that value is relative to cell where formula is, and
it's picking up info from the top row of the same column . .

"Barb Reinhardt" wrote:

In order to ensure that the data is being extracted from the right column,
you need to change this:

"=VLOOKUP(R[" & -(dropper - 1) & "]C
to something like this
"=VLOOKUP(R[" & -(dropper - 1) & "]C1

Make sense?


--

Dave Peterson