Roger
asked on
VBA to add a value to an excel cell at a defined row and column position
I am building a lookup list of temporary sheet names (in colA), and this code aims to add a new sheet name to bottomRow+1 (ie to append the sheet name to the list in colA).
In colB: False means the data in the respective temp sheet has not been changed; True means it has been changed.
Since the length of the list is dynamic, I chose to add sheet names to the list using a .Range(Cells) syntax. But I cant get it to function.
Here is my code, with a set of failed lines, using a dynamic cell range. The only thing that worked was the static .Range("A3"):
Dim bottomRow as integer
Dim rn_bottomRow as range
Set rn_bottomRow =
ThisWorkbook.Worksheets("T empList_Sh ts_VisioDa ta").Range ("A1").End (xlDown)
bottomRow = rn_bottomRow.row ' this identifies the last occupied row ok
With Worksheets("TempList_Shts_ VisioData" )
.Activate 'sheet IS activated
.Range(Cells(3, 2)) = "hello" 'this line failed
.Range(.Cells(bottomRow + 1, 2)) = "hello" 'this line failed
.Range(.Cells(3, 2)) = "hello" 'this line failed
.Range(.Cells(rn_bottomRow .row + 1, 3)) = "False" 'this line failed
.Range(.Cells(3, 2)) = "hello" 'this line failed
.Range("B3") = "False" 'this line worked but I need a dynamic cell reference!!!
End With
Evidence that .with syntax is working:
.Activate 'sheet IS activated: TempList_Shts_VisioData becomes the active sheet
.Name returned the correct sheet name
I cant fathom my error...
What is the correct syntax for Range using Cells property - for active and inactive sheets, respectively?
This code is written within a class module; does that matter?
An explanation of the principles behind a solution would be appreciated.
Thanks
Kelvin
In colB: False means the data in the respective temp sheet has not been changed; True means it has been changed.
Since the length of the list is dynamic, I chose to add sheet names to the list using a .Range(Cells) syntax. But I cant get it to function.
Here is my code, with a set of failed lines, using a dynamic cell range. The only thing that worked was the static .Range("A3"):
Dim bottomRow as integer
Dim rn_bottomRow as range
Set rn_bottomRow =
ThisWorkbook.Worksheets("T
bottomRow = rn_bottomRow.row ' this identifies the last occupied row ok
With Worksheets("TempList_Shts_
.Activate 'sheet IS activated
.Range(Cells(3, 2)) = "hello" 'this line failed
.Range(.Cells(bottomRow + 1, 2)) = "hello" 'this line failed
.Range(.Cells(3, 2)) = "hello" 'this line failed
.Range(.Cells(rn_bottomRow
.Range(.Cells(3, 2)) = "hello" 'this line failed
.Range("B3") = "False" 'this line worked but I need a dynamic cell reference!!!
End With
Evidence that .with syntax is working:
.Activate 'sheet IS activated: TempList_Shts_VisioData becomes the active sheet
.Name returned the correct sheet name
I cant fathom my error...
What is the correct syntax for Range using Cells property - for active and inactive sheets, respectively?
This code is written within a class module; does that matter?
An explanation of the principles behind a solution would be appreciated.
Thanks
Kelvin
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to both.
hgholt: I appreciated your explanation, which enabled me to make a functional xl demo file of exercises.
Cheers
Kelvin
hgholt: I appreciated your explanation, which enabled me to make a functional xl demo file of exercises.
Cheers
Kelvin
.cells(3,2) = "hello" (drop .range)