VBA referecing cell with a variable

**howwwie** · 03-18-2011, 10:06 PM

Hi,
I have a quick question...

suppose I have a variable as follows:

number = 10
I wish to code the cell "A[number]", which equates to the cell "A10". How do I do that?

Hope I'm not confusing anyone!

**MarvinP** · 03-18-2011, 10:08 PM

Hi howwwie and welcome to the forum.

There are two ways using VBA.

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Both should work.

**howwwie** · 03-19-2011, 12:59 AM

Thanks for that! It still doesn't seem to like it...
the full problem is as shown:
I used a user-defined function in VBA called "commasep", which concatenates a group of cells separated by commas.

Next I created a macro which selects active cells in column B, and applies the function "commasep":

Sub CombineEMails()
'
' CombineEMails Macro
' Combine emails into one list, separated by commas
'

'
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With

Range("F8").Select
ActiveCell.Formula = "=commasep(B2:B11)"

End Sub
===================================
See the bit where it is bolded. Instead of "B11", I wish to use B[LastRow].

I was wondering what code should I use instead of B2:B11?

Thanks!

**howwwie** · 03-19-2011, 01:06 AM

@MarvinP

It works now that I used the "range" function: ActiveCell.Formula = commasep(Range("B2:B" & LastRow))
Thanks a lot!

**pike** · 03-19-2011, 01:19 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here