Won't insert value

[davegb]

I'm copying the contents of a cell in Sheet "B" to a cell in Sheet "A",
but the program hangs when I try to put the value in "A". The command
I'm using is:

Worksheets("A").Range(iCurRow, 1).Value = strCurID

The iCurRow value checks out correctly, as does the strCurID. What's
wrong with this code?
Thanks for the help.

 

[Bernie Deitrick]

Dave,

Range doesn't take numeric values, only strings. Cells uses row/col
numbers.

Use:
Worksheets("A").Cells(iCurRow, 1).Value = strCurID

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Oh, if you want to use the Range object with your iCurRow, you would need to
use

Worksheets("A").Range("A" & iCurRow).Value = strCurID

HTH,
Bernie
MS Excel MVP

 

[davegb]

Bernie,
Thanks for your reply. I'm still confused. In your first reply, you
said, "Range doesn't take numeric values, only strings. Cells uses
row/col numbers." In your second message, you show how to use Range to
take a numeric value. Can you elucidate further?
I'm new to VBA programming and am finding it far more confusing than
any other programming language I've ever learned. Granted, it's been a
lone time and none of the older languages were object oriented, so I'm
trying to accept that everything I ever knew about programming is worse
than useless. It's misleading!
I can't seem to figure out when to use Cells and when to use Range, or
when to use Worksheet, Worksheets, and Sheets. There seem to be an
awful lot of different things to represent the same thing. I know you
didn't invent VBA, and it's not your fault, but it sure is strange. Any
ideas or guidlines as to when to use which of the many similar objects?

 

[Bernie Deitrick]

Dave,

I'm sorry. In my second example, Excel coerces the value of the integer
variable into a string due to the concantenation operation:

"A" & iCurRow

will result in a string "A1" (if iCurRow is 1 at the time). So Range ends
up being passed a string.

Which method or property is best to use depends on what you want to do, and
what you have. Sheets is a collection of all sheets, including chart sheets
and worksheets, and worksheets is a collection of only worksheets. So which
is better depends.....

Experience is the best teacher, with posting here (or just reading the
posts) a close second. Also, get a good book: Excel Power Programming with
VBA by John Walkenbach is an excellent resource for the beginning VBA coder.
And use your macro recorder to generate code that you can edit. And just get
in there and do it....

HTH,
Bernie
MS Excel MVP

 

[davegb]

Thanks Bernie! It sort of makes sense. It's going to take some time to
figure out a whole new paradigm for me in programming.

"Sheets is a collection of all sheets, including chart sheets and
worksheets, and worksheets is a collection of only worksheets."
Does that mean that if there are no charts present, Sheets and
Worksheets would be the same thing? Or is there still a difference?

"Experience is the best teacher, with posting here (or just reading the

posts) a close second. Also, get a good book: Excel Power Programming
with
VBA by John Walkenbach is an excellent resource for the beginning VBA
coder.
And use your macro recorder to generate code that you can edit. And
just get
in there and do it.... "

Actually, that's exactly what I've been doing. I got Walkenbach's book
last week, and I agree that it's a good one.
Thanks again,
Dave