Reference Cell # with variable

S

sjw

Need help with the code below.
If I substitute
CellContents = Worksheets("Data for Sim RT").[B100].Text
It works!

Sub TestCellVariable()
Dim CellContents As Variant '...Cell contents
Dim lngCellNum As Long '...Row number
Dim strCell As Variant

lngCellNum = 100

strCell = "B" & lngCellNum
CellContents = Worksheets("Data for Sim RT").[strCell].Text

MsgBox "zzz Cell Contents is " & CellContents

End Sub
 
N

Niek Otten

CellContents = Worksheets("Data for Sim RT").Range(strCell).Text

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Need help with the code below.
| If I substitute
| CellContents = Worksheets("Data for Sim RT").[B100].Text
| It works!
|
| Sub TestCellVariable()
| Dim CellContents As Variant '...Cell contents
| Dim lngCellNum As Long '...Row number
| Dim strCell As Variant
|
| lngCellNum = 100
|
| strCell = "B" & lngCellNum
| CellContents = Worksheets("Data for Sim RT").[strCell].Text
|
| MsgBox "zzz Cell Contents is " & CellContents
|
| End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Wrong data type returned from user-defined function 9
Find value of active cell in other visible worksheets 8
XP Problems with SendKeys - dialing a number in an Excel cell 1
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Finding content of cell in visible "protected" sheets 4
Excel export formatting problem for fixed width files 1
Frustrated with Microsoft 2
Remove Identical words 0

Top