Dim rngTemp As Range

Set rngTemp = Workbooks("soourcedata.xls"). _
Worksheets("Sheet1").Range("F1:I100")

ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, _
"A"), rngTemp, 4, 0)

If this post helps click Yes
---------------
Jacob Skaria


"cluckers" wrote:

thank Luke,

However I need more help if you could. For the second argument it is going
to be an another workbook called "sourcedata". the range is from F1 to the
furthers column to the right and the furthest row down. How would I write
that?

so far I have

ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"),
soourcedata.xls.range(F1,..........), 4, False)

Not even sure if any of the second argument is correct.

thanks


"Luke M" wrote:

Cells(ActiveCell.Row,"A")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cluckers" wrote:

I am trying to use vlookup in a macro. I have to use

ActiveCell.Value = WorksheetFunction.VLookup(........)

because the active cell is in a column that will continuously move to the
right. Therefore I can not use a static command.

For the first argument of the vlookup how can I have it reference the cell
is column A of the same row. If I can get that part I think I can get the
rest.

Thanks

I am getting an error that says "type mismatch"

"Jacob Skaria" wrote:

Dim rngTemp As Range

Set rngTemp = Workbooks("soourcedata.xls"). _
Worksheets("Sheet1").Range("F1:I100")

ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, _
"A"), rngTemp, 4, 0)

If this post helps click Yes
---------------
Jacob Skaria


"cluckers" wrote:

thank Luke,

However I need more help if you could. For the second argument it is going
to be an another workbook called "sourcedata". the range is from F1 to the
furthers column to the right and the furthest row down. How would I write
that?

so far I have

ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"),
soourcedata.xls.range(F1,..........), 4, False)

Not even sure if any of the second argument is correct.

thanks


"Luke M" wrote:

Cells(ActiveCell.Row,"A")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cluckers" wrote:

I am trying to use vlookup in a macro. I have to use

ActiveCell.Value = WorksheetFunction.VLookup(........)

because the active cell is in a column that will continuously move to the
right. Therefore I can not use a static command.

For the first argument of the vlookup how can I have it reference the cell
is column A of the same row. If I can get that part I think I can get the
rest.

Thanks

at first glance perhaps this line:
ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"),
rngTemp, 4, 0)
should read:
ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell*.Row*,
"A"), rngTemp, 4, 0)
(as originally suggested)


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126806

After adding "row" it returns the error "unable to get the vlookup property
of the WorksheetFunction class".

"p45cal" wrote:


at first glance perhaps this line:
ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"),
rngTemp, 4, 0)
should read:
ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell*.Row*,
"A"), rngTemp, 4, 0)
(as originally suggested)


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126806

Very likely because what it's looking for isn't to be found.
I tried this:
ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell.Row,
"A"), srccom, 5, 0)
and it worked, but then gave the error you describe if what was sought
didn't exist.
Try the likes of this:Dim srccom As Range
Set srccom = ThisWorkbook.Worksheets("Sheet1").Range("F1:J100")
xxx = Empty
On Error Resume Next
xxx = WorksheetFunction.VLookup(Cells(ActiveCell.Row, "A"), srccom, 5,
0)
On Error GoTo 0 'cancels the On Error Resume Next
If Not IsEmpty(xxx) Then ActiveCell.Value = xxx


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126806

Do I need to declare xxx as something?

"p45cal" wrote:


Very likely because what it's looking for isn't to be found.
I tried this:
ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell.Row,
"A"), srccom, 5, 0)
and it worked, but then gave the error you describe if what was sought
didn't exist.
Try the likes of this:Dim srccom As Range
Set srccom = ThisWorkbook.Worksheets("Sheet1").Range("F1:J100")
xxx = Empty
On Error Resume Next
xxx = WorksheetFunction.VLookup(Cells(ActiveCell.Row, "A"), srccom, 5,
0)
On Error GoTo 0 'cancels the On Error Resume Next
If Not IsEmpty(xxx) Then ActiveCell.Value = xxx


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126806

No, its simply a variant to hold whatever the result of the vlookup is
(whether its an error, a number, a string, etc)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cluckers" wrote:

Do I need to declare xxx as something?

"p45cal" wrote:


Very likely because what it's looking for isn't to be found.
I tried this:
ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell.Row,
"A"), srccom, 5, 0)
and it worked, but then gave the error you describe if what was sought
didn't exist.
Try the likes of this:Dim srccom As Range
Set srccom = ThisWorkbook.Worksheets("Sheet1").Range("F1:J100")
xxx = Empty
On Error Resume Next
xxx = WorksheetFunction.VLookup(Cells(ActiveCell.Row, "A"), srccom, 5,
0)
On Error GoTo 0 'cancels the On Error Resume Next
If Not IsEmpty(xxx) Then ActiveCell.Value = xxx


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126806

Try the below test

Col A Col B Col C Col D
One 11 12 13
Two 21 22 23
Three 31 32 33
Four 41 42 43
One XX

In the above table place your cursor in xx (cell B5) and try the below code.
It should vlookup the text mentioned in A5 in the array A1:D4 and return the
corresponding value from 4th Column of the array

Dim rngTemp As Range

Set rngTemp = Worksheets("Sheet1").Range("A1:D4")
ActiveCell.Value = WorksheetFunction.VLookup(Range("A" & _
ActiveCell.Row), rngTemp, 4, 0)


If this post helps click Yes
---------------
Jacob Skaria


"cluckers" wrote:

I am getting an error that says "type mismatch"

"Jacob Skaria" wrote:

Dim rngTemp As Range

Set rngTemp = Workbooks("soourcedata.xls"). _
Worksheets("Sheet1").Range("F1:I100")

ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, _
"A"), rngTemp, 4, 0)

If this post helps click Yes
---------------
Jacob Skaria


"cluckers" wrote:

thank Luke,

However I need more help if you could. For the second argument it is going
to be an another workbook called "sourcedata". the range is from F1 to the
furthers column to the right and the furthest row down. How would I write
that?

so far I have

ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"),
soourcedata.xls.range(F1,..........), 4, False)

Not even sure if any of the second argument is correct.

thanks


"Luke M" wrote:

Cells(ActiveCell.Row,"A")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cluckers" wrote:

I am trying to use vlookup in a macro. I have to use

ActiveCell.Value = WorksheetFunction.VLookup(........)

because the active cell is in a column that will continuously move to the
right. Therefore I can not use a static command.

For the first argument of the vlookup how can I have it reference the cell
is column A of the same row. If I can get that part I think I can get the
rest.

Thanks

Here is the code

Dim srccom As Range
Set srccom = Workbooks("sourcedata.xls").Worksheets("Sheet1").R ange("F1:J100")
ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"), srccom,
5, 0)

Returns
Run-time error'13':
Type mismatch


"cluckers" wrote:

I am getting an error that says "type mismatch"

"Jacob Skaria" wrote:

Dim rngTemp As Range

Set rngTemp = Workbooks("soourcedata.xls"). _
Worksheets("Sheet1").Range("F1:I100")

ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, _
"A"), rngTemp, 4, 0)

If this post helps click Yes
---------------
Jacob Skaria


"cluckers" wrote:

thank Luke,

However I need more help if you could. For the second argument it is going
to be an another workbook called "sourcedata". the range is from F1 to the
furthers column to the right and the furthest row down. How would I write
that?

so far I have

ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"),
soourcedata.xls.range(F1,..........), 4, False)

Not even sure if any of the second argument is correct.

thanks


"Luke M" wrote:

Cells(ActiveCell.Row,"A")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cluckers" wrote:

I am trying to use vlookup in a macro. I have to use

ActiveCell.Value = WorksheetFunction.VLookup(........)

because the active cell is in a column that will continuously move to the
right. Therefore I can not use a static command.

For the first argument of the vlookup how can I have it reference the cell
is column A of the same row. If I can get that part I think I can get the
rest.

Thanks

Try the below

Dim srccom As Range
Set srccom = Workbooks("sourcedata.xls").Worksheets("Sheet1").R ange("F1:J100")
ActiveCell.Value = WorksheetFunction.VLookup(Range("A" & _
ActiveCell.Row), srccom, 5, 0)


If this post helps click Yes
---------------
Jacob Skaria


"cluckers" wrote:

Here is the code

Dim srccom As Range
Set srccom = Workbooks("sourcedata.xls").Worksheets("Sheet1").R ange("F1:J100")
ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"), srccom,
5, 0)

Returns
Run-time error'13':
Type mismatch


"cluckers" wrote:

I am getting an error that says "type mismatch"

"Jacob Skaria" wrote:

Dim rngTemp As Range

Set rngTemp = Workbooks("soourcedata.xls"). _
Worksheets("Sheet1").Range("F1:I100")

ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, _
"A"), rngTemp, 4, 0)

If this post helps click Yes
---------------
Jacob Skaria


"cluckers" wrote:

thank Luke,

However I need more help if you could. For the second argument it is going
to be an another workbook called "sourcedata". the range is from F1 to the
furthers column to the right and the furthest row down. How would I write
that?

so far I have

ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"),
soourcedata.xls.range(F1,..........), 4, False)

Not even sure if any of the second argument is correct.

thanks


"Luke M" wrote:

Cells(ActiveCell.Row,"A")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cluckers" wrote:

I am trying to use vlookup in a macro. I have to use

ActiveCell.Value = WorksheetFunction.VLookup(........)

because the active cell is in a column that will continuously move to the
right. Therefore I can not use a static command.

For the first argument of the vlookup how can I have it reference the cell
is column A of the same row. If I can get that part I think I can get the
rest.

Thanks

The problem is that there is not going to be a vlookup value for all active
cells. When you run this is excel t returns a value of #N/A but in VBA it
returns an error. Do you know how to get around the error so it keeps
running the macro?

"Jacob Skaria" wrote:

Try the below

Dim srccom As Range
Set srccom = Workbooks("sourcedata.xls").Worksheets("Sheet1").R ange("F1:J100")
ActiveCell.Value = WorksheetFunction.VLookup(Range("A" & _
ActiveCell.Row), srccom, 5, 0)


If this post helps click Yes
---------------
Jacob Skaria


"cluckers" wrote:

Here is the code

Dim srccom As Range
Set srccom = Workbooks("sourcedata.xls").Worksheets("Sheet1").R ange("F1:J100")
ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"), srccom,
5, 0)

Returns
Run-time error'13':
Type mismatch


"cluckers" wrote:

I am getting an error that says "type mismatch"

"Jacob Skaria" wrote:

Dim rngTemp As Range

Set rngTemp = Workbooks("soourcedata.xls"). _
Worksheets("Sheet1").Range("F1:I100")

ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, _
"A"), rngTemp, 4, 0)

If this post helps click Yes
---------------
Jacob Skaria


"cluckers" wrote:

thank Luke,

However I need more help if you could. For the second argument it is going
to be an another workbook called "sourcedata". the range is from F1 to the
furthers column to the right and the furthest row down. How would I write
that?

so far I have

ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"),
soourcedata.xls.range(F1,..........), 4, False)

Not even sure if any of the second argument is correct.

thanks


"Luke M" wrote:

Cells(ActiveCell.Row,"A")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cluckers" wrote:

I am trying to use vlookup in a macro. I have to use

ActiveCell.Value = WorksheetFunction.VLookup(........)

because the active cell is in a column that will continuously move to the
right. Therefore I can not use a static command.

For the first argument of the vlookup how can I have it reference the cell
is column A of the same row. If I can get that part I think I can get the
rest.

Thanks