VLOOKUP
 

Hi,

Not sure if this is the right section of this site to post this question so
bear with us, I have written a piece of VBA code that selects data from SQL
and puts it into a small table on sheet1, then on sheet2 I am using a vlookup
to look at the data on sheet1 and put it in the relevant sections, the
problem that I am having is when I run the vba code, which repopulates the
data on Sheet1 "this never contains any more rows, just different numbers so
the range is never different" the Vlookup breaks, rather than giving the
range A1:B23, it reverts back to A1:A23, breaking everything, if I go back in
an manually change this it works again???? It might have something to do
with the column with all the numbers in, as this is deleted first and then
the numbers put back in again, can anyone see where I am going wrong.

Thanks PD

VLOOKUP
 

Do you mind posting the code?
When you sellect the range do you use Address or do you use the references
like Range("A1:A23"). It might be better if you did something like.

Range("&Cells(1,1).Address&":"&Cells(23,2).Address &")

Didn't try it out, but without seeing your code, I don't know how you are
going about this. Lmk

"Phil" wrote:

Hi,

Not sure if this is the right section of this site to post this question so
bear with us, I have written a piece of VBA code that selects data from SQL
and puts it into a small table on sheet1, then on sheet2 I am using a vlookup
to look at the data on sheet1 and put it in the relevant sections, the
problem that I am having is when I run the vba code, which repopulates the
data on Sheet1 "this never contains any more rows, just different numbers so
the range is never different" the Vlookup breaks, rather than giving the
range A1:B23, it reverts back to A1:A23, breaking everything, if I go back in
an manually change this it works again???? It might have something to do
with the column with all the numbers in, as this is deleted first and then
the numbers put back in again, can anyone see where I am going wrong.

Thanks PD

VLOOKUP
 

Hi AKphildelt,

Thanks for the help, my Vlookup string looks like this

=VLOOKUP(D7,KPIResults!A2:B22,2,FALSE)
So I guess I am using references like range.

Thanks PD


"AKphidelt" wrote:

Do you mind posting the code?
When you sellect the range do you use Address or do you use the references
like Range("A1:A23"). It might be better if you did something like.

Range("&Cells(1,1).Address&":"&Cells(23,2).Address &")

Didn't try it out, but without seeing your code, I don't know how you are
going about this. Lmk

"Phil" wrote:

Hi,

Not sure if this is the right section of this site to post this question so
bear with us, I have written a piece of VBA code that selects data from SQL
and puts it into a small table on sheet1, then on sheet2 I am using a vlookup
to look at the data on sheet1 and put it in the relevant sections, the
problem that I am having is when I run the vba code, which repopulates the
data on Sheet1 "this never contains any more rows, just different numbers so
the range is never different" the Vlookup breaks, rather than giving the
range A1:B23, it reverts back to A1:A23, breaking everything, if I go back in
an manually change this it works again???? It might have something to do
with the column with all the numbers in, as this is deleted first and then
the numbers put back in again, can anyone see where I am going wrong.

Thanks PD

VLOOKUP
 

Stop deleting the column - I would use Clear or Clearcontents instead.


"Phil" wrote:

Hi,

Not sure if this is the right section of this site to post this question so
bear with us, I have written a piece of VBA code that selects data from SQL
and puts it into a small table on sheet1, then on sheet2 I am using a vlookup
to look at the data on sheet1 and put it in the relevant sections, the
problem that I am having is when I run the vba code, which repopulates the
data on Sheet1 "this never contains any more rows, just different numbers so
the range is never different" the Vlookup breaks, rather than giving the
range A1:B23, it reverts back to A1:A23, breaking everything, if I go back in
an manually change this it works again???? It might have something to do
with the column with all the numbers in, as this is deleted first and then
the numbers put back in again, can anyone see where I am going wrong.

Thanks PD

VLOOKUP
 

Hi JMB,

That worked a treat, thought it would of had something to do with the
delete, well you learn something new every day :-)

Thanks Phil

"JMB" wrote:

Stop deleting the column - I would use Clear or Clearcontents instead.


"Phil" wrote:

Hi,

Not sure if this is the right section of this site to post this question so
bear with us, I have written a piece of VBA code that selects data from SQL
and puts it into a small table on sheet1, then on sheet2 I am using a vlookup
to look at the data on sheet1 and put it in the relevant sections, the
problem that I am having is when I run the vba code, which repopulates the
data on Sheet1 "this never contains any more rows, just different numbers so
the range is never different" the Vlookup breaks, rather than giving the
range A1:B23, it reverts back to A1:A23, breaking everything, if I go back in
an manually change this it works again???? It might have something to do
with the column with all the numbers in, as this is deleted first and then
the numbers put back in again, can anyone see where I am going wrong.

Thanks PD

VLOOKUP
 

You're welcome-thanks for the feedback.

"Phil" wrote:

Hi JMB,

That worked a treat, thought it would of had something to do with the
delete, well you learn something new every day :-)

Thanks Phil

"JMB" wrote:

Stop deleting the column - I would use Clear or Clearcontents instead.


"Phil" wrote:

Hi,

Not sure if this is the right section of this site to post this question so
bear with us, I have written a piece of VBA code that selects data from SQL
and puts it into a small table on sheet1, then on sheet2 I am using a vlookup
to look at the data on sheet1 and put it in the relevant sections, the
problem that I am having is when I run the vba code, which repopulates the
data on Sheet1 "this never contains any more rows, just different numbers so
the range is never different" the Vlookup breaks, rather than giving the
range A1:B23, it reverts back to A1:A23, breaking everything, if I go back in
an manually change this it works again???? It might have something to do
with the column with all the numbers in, as this is deleted first and then
the numbers put back in again, can anyone see where I am going wrong.

Thanks PD