Difference Between Range("SelectedRange").Value and ActiveCell.Val

[James Barrass]

I've written a simple macro that copies cells from worksheet1 to worksheet 2.

CellVal = ActiveCell.Value
..
..
..
If Not (TargetCol = 0) Then Worksheets(2).Cells(TargetRow,
TargetCol).Value = CellVal

This Leaves All the target cells blank
I've checked using the Immediate window and CellVal does contain a string

CellVal = Range("A4").Value
..
..
..
If Not (TargetCol = 0) Then Worksheets(2).Cells(TargetRow,
TargetCol).Value = CellVal

This Writes the value of CellVal into the target cell no problem, only I
cant seem to dynamically change the range im using
I get Error using range method on _global if I use a variable in the range
statement
I checked CellVal again using the Immediate window and this time it contains
the same string

 

[James Barrass]

Tried Range("A" & CStr(i)).Value to dynamically change the range, only worked
when i was a constant

 

[James Barrass]

Cell Value was being overwritten at a different stage, corrected this now.

 

[Dave Peterson]

Excel/VBA is pretty nice:

Instead of this:
Range("A" & CStr(i)).Value
you could use:
Range("A" & i).Value
or
cells(i,"A").Value

(Yeah, I know you didn't ask.)

 

[James Barrass]

Thanx anyway, Im used to pascal where you have to convert everthing between
types