Excel VBA - using variable in function range

Question

I'm using a function to set a variable:

Dim LastMemRow As Integer
Dim FLAGFileNo As Integer
Dim MemNoRow As Integer

MemNoRow = WorksheetFunction.Match(FLAGFileNo, Workbooks(TalkRegTempFile).Worksheets("Setup Sheet").Range("A1", "A2000"), 0)

Which works fine, however I want to replace "A2000" with a variable LastMemRow (previously set by some earlier code) so that the Match only searches to the last used row in col A of the target worksheet. I've tried this:

MemNoRow = WorksheetFunction.Match(FLAGFileNo, Workbooks(TalkRegTempFile).Worksheets("Setup Sheet").Range(Cells(1, 1), Cells(1, LastMemRow)), 0)

and although the FLAGFileNo and LastMemRow variables contain the expected values, MemNoRow is always 0.

Maybe I can't use Cells in this way in a worksheet function? (Excel 2010)

BIRDS VIEW: Try this Workbooks(TalkRegTempFile).Worksheets("Setup Sheet").Range(Cells(1, 1).address, Cells(1, LastMemRow).Address) — Siddharth Rout
– Siddharth Rout, Commented Sep 9, 2014 at 15:17

Aiken · Accepted Answer · 2014-09-09 15:20:25Z

2

Rather than using .Range(Cells(1,1), Cells(1, LastMemRow)) which may or may not be referencing the wrong worksheet, concatenate a string using LastMemRow in place of 2000.

MemNoRow = WorksheetFunction.Match(FLAGFileNo, Workbooks(TalkRegTempFile).Worksheets("Setup Sheet").Range("A1", "A" & LastMemRow), 0)

answered Sep 9, 2014 at 15:20

Aiken

2,6682 gold badges20 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Siddharth Rout Over a year ago

+ 1 I had totally forgotten about "A" & LastMemRow :P

Roemer · Accepted Answer · 2014-09-09 15:19:35Z

0

Try:

MemNoRow = WorksheetFunction.Match(FLAGFileNo, Workbooks(TalkRegTempFile).Worksheets("Setup Sheet").Range("A1", Cells(1, LastMemRow).AddressLocal), 0)

If the first code worked, this should too. Let me know.

answered Sep 9, 2014 at 15:19

Roemer

1,2799 silver badges27 bronze badges

Comments

DavidMUK · Accepted Answer · 2014-09-09 17:49:19Z

0

If anyone's interested, this works:

MemNoRow = WorksheetFunction.Match(FLAGFileNo, Workbooks(TalkRegTempFile).Worksheets("Setup   Sheet").Range("A1", "A" & CStr(LastMemRow)), 0)

i.e. instead of "A2000" in the original post use "A" & CStr(LastMemRow), the last bit converts the LastMemRow integer variable into a string.

Thanks for your replies, pointed me in the right direction.

answered Sep 9, 2014 at 17:49

DavidMUK

112 bronze badges

Collectives™ on Stack Overflow

Excel VBA - using variable in function range

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related