Link to home
Start Free TrialLog in
Avatar of Roger
RogerFlag for United Kingdom of Great Britain and Northern Ireland

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("TempList_Shts_VisioData").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
SOLUTION
Avatar of COACHMAN99
COACHMAN99

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of COACHMAN99
COACHMAN99

it works fine as:
 .cells(3,2) = "hello"    (drop .range)
Avatar of Roger

ASKER

Thanks to both.

hgholt: I appreciated your explanation, which enabled me to make a functional xl demo file of exercises.

Cheers
Kelvin