Moving a number
 

Say I have the number "2" in cell A1 of sheet "Assets". Can someone please provide a simple vba command for:

1. Entering the contents of cell A1 into cell A5 of "Assets"

2. Entering the contents of cell A1 of "Assets" into cell A5 of sheet "Liabilities".

In the old (pre-Vba Excel) macro language which I am familiar with the command "Formula" was used, however, it does not appear to available in VBA.

I greatly appreciate all help.

Moving a number
 

programmernovice wrote:

Say I have the number "2" in cell A1 of sheet "Assets". Can someone
please provide a simple vba command for:

1. Entering the contents of cell A1 into cell A5 of "Assets"

Sheets("Assets").Range("A5").Value = Sheets("Assets").Range("A1").Value

2. Entering the contents of cell A1 of "Assets" into cell A5 of sheet
"Liabilities".

Sheets("Liabilities").Range("A5").Value = Sheets("Assets").Range("A1").Value

(In VBA, an underscore ("_") by itself at the end of a line is a line-
continuation character.)

In the old (pre-Vba Excel) macro language which I am familiar with the
command "Formula" was used, however, it does not appear to available in
VBA.

In VBA, the .Formula property refers to, somewhat obviously, the formula,
while the .Value property refers to the displayed value. So, if A1 has a
formula of =1+1, then

Cells(1, 1).Value = 2
Cells(1, 1).Formula = "=1+1"

(Cells(1, 1) is how you refer to cells in R1C1 format. Range("A1") is of
course the other way. R1C1 is easier for me since I do a lot of looping
through every cell in a given column/row/sheet.)

I greatly appreciate all help.

It might be worth reading through a beginner's introduction the VBA. Googling
that phrase should be useful. Wikibooks has a few, but the ones I looked at
don't seem to be for beginners.

--
Power attracts the corruptible. Suspect all who seek it.

Moving a number
 

On Tuesday, November 10, 2020 at 11:12:00 PM UTC-6, Auric__ wrote:
programmernovice wrote:

Say I have the number "2" in cell A1 of sheet "Assets". Can someone
please provide a simple vba command for:

1. Entering the contents of cell A1 into cell A5 of "Assets"
Sheets("Assets").Range("A5").Value = Sheets("Assets").Range("A1").Value
2. Entering the contents of cell A1 of "Assets" into cell A5 of sheet
"Liabilities".
Sheets("Liabilities").Range("A5").Value = Sheets("Assets").Range("A1").Value

(In VBA, an underscore ("_") by itself at the end of a line is a line-
continuation character.)
In the old (pre-Vba Excel) macro language which I am familiar with the
command "Formula" was used, however, it does not appear to available in
VBA.
In VBA, the .Formula property refers to, somewhat obviously, the formula,
while the .Value property refers to the displayed value. So, if A1 has a
formula of =1+1, then

Cells(1, 1).Value = 2
Cells(1, 1).Formula = "=1+1"

(Cells(1, 1) is how you refer to cells in R1C1 format. Range("A1") is of
course the other way. R1C1 is easier for me since I do a lot of looping
through every cell in a given column/row/sheet.)
I greatly appreciate all help.
It might be worth reading through a beginner's introduction the VBA. Googling
that phrase should be useful. Wikibooks has a few, but the ones I looked at
don't seem to be for beginners.

--
Power attracts the corruptible. Suspect all who seek it.

Many thanks, Auric, for the excellent, thorough explanation. Do you have an opinion on a good beginners VBA book?

I greatly appreciate your helping me out.

Moving a number
 

programmernovice wrote:

On Tuesday, November 10, 2020 at 11:12:00 PM UTC-6, Auric__ wrote:
[snip]
(In VBA, an underscore ("_") by itself at the end of a line is a line-
continuation character.)

Oops. I had used one of these in the example code I first wrote, then found a
way to make it all on one line, but forgot to delete this.

Many thanks, Auric, for the excellent, thorough explanation. Do you
have an opinion on a good beginners VBA book?

I don't, sorry. I learned Visual Basic in the mid-90's using one of those "in
24 hours"-type books, and haven't really needed one since then. (Of course,
it helped that I had been using other forms of BASIC since the early 80's.)

Microsoft Press has what they call a "VBA Primer" he

https://www.microsoftpressstore.com/...aspx?p=2224365

It's 5 pages of the basics (no pun intended), but doesn't get you very far
along the process.

They have 2 books, "Microsoft Excel 2019 VBA and Macros" (watch the
wordwrap):

https://www.microsoftpressstore.com/...-2019-vba-and-
macros-9781509306114

....and "Microsoft Excel 2019 Inside Out" (again, wordwrap):

https://www.microsoftpressstore.com/...19-inside-out-
9781509307692

Those *might* be somewhat helpful, but neither one is specifically a
beginning-to-end guide to VBA.

--
- Nothing personal, but I hate doctors.
- It's cool, most of us hate some of our patients.

Moving a number
 

On Wednesday, November 11, 2020 at 3:26:24 PM UTC-6, Auric__ wrote:
programmernovice wrote:

On Tuesday, November 10, 2020 at 11:12:00 PM UTC-6, Auric__ wrote:
[snip]
(In VBA, an underscore ("_") by itself at the end of a line is a line-
continuation character.)
Oops. I had used one of these in the example code I first wrote, then found a
way to make it all on one line, but forgot to delete this.
Many thanks, Auric, for the excellent, thorough explanation. Do you
have an opinion on a good beginners VBA book?
I don't, sorry. I learned Visual Basic in the mid-90's using one of those "in
24 hours"-type books, and haven't really needed one since then. (Of course,
it helped that I had been using other forms of BASIC since the early 80's.)

Microsoft Press has what they call a "VBA Primer" he

https://www.microsoftpressstore.com/...aspx?p=2224365

It's 5 pages of the basics (no pun intended), but doesn't get you very far
along the process.

They have 2 books, "Microsoft Excel 2019 VBA and Macros" (watch the
wordwrap):

https://www.microsoftpressstore.com/...-2019-vba-and-
macros-9781509306114

...and "Microsoft Excel 2019 Inside Out" (again, wordwrap):

https://www.microsoftpressstore.com/...19-inside-out-
9781509307692

Those *might* be somewhat helpful, but neither one is specifically a
beginning-to-end guide to VBA.

--
- Nothing personal, but I hate doctors.
- It's cool, most of us hate some of our patients.


Thanks again, Auric. I will look into your suggestions for books.

I tried your suggested syntax for moving a number into a different sheet. My statement is:

Range("M12").Value = Sheets("stocks").Range("c2").Value

"M12" refers to the active sheet. "Stocks" is another open sheet. Cell "C2" contains a number. When I run it, I get the error message

Run-time error '9'
Subscript out of range.

What am I doing wrong. Many thanks for your patience!

Moving a number
 

programmernovice wrote:

I tried your suggested syntax for moving a number into a different
sheet. My statement is:

Range("M12").Value = Sheets("stocks").Range("c2").Value

"M12" refers to the active sheet. "Stocks" is another open sheet. Cell
"C2" contains a number. When I run it, I get the error message

The contents shouldn't matter; that code should copy whatever is in stocks!c2
to [activesheet]!m12, whether it's a formula, number, plain text, or nothing.
Should.

Run-time error '9'
Subscript out of range.

What am I doing wrong. Many thanks for your patience!

My only suggestion is to make sure the sheet name is spelled correctly. Your
code works for me.

That error usually applies to arrays, so make sure you also have things like
"Range" and "Sheets" spelled correctly.

--
I thought this would never end.
I am so tired of fighting and battles and death I can't begin to express it.

Moving a number
 

I wrote:

programmernovice wrote:

I tried your suggested syntax for moving a number into a different
sheet. My statement is:

Range("M12").Value = Sheets("stocks").Range("c2").Value

"M12" refers to the active sheet. "Stocks" is another open sheet.
Cell "C2" contains a number. When I run it, I get the error message

The contents shouldn't matter; that code should copy whatever is in
stocks!c2 to [activesheet]!m12, whether it's a formula, number, plain
text, or nothing. Should.

Er... not the formula, just the results of it. Derp. Copying the formula
would require using the .Formula property, of course.

--
Nah, I just love pushing people's buttons.

Moving a number
 

On Monday, November 16, 2020 at 1:02:56 AM UTC-6, Auric__ wrote:
I wrote:

programmernovice wrote:

I tried your suggested syntax for moving a number into a different
sheet. My statement is:

Range("M12").Value = Sheets("stocks").Range("c2").Value

"M12" refers to the active sheet. "Stocks" is another open sheet.
Cell "C2" contains a number. When I run it, I get the error message

The contents shouldn't matter; that code should copy whatever is in
stocks!c2 to [activesheet]!m12, whether it's a formula, number, plain
text, or nothing. Should.
Er... not the formula, just the results of it. Derp. Copying the formula
would require using the .Formula property, of course.

--
Nah, I just love pushing people's buttons.
OK, many thanks Auric.