Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 61
Default 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.

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default 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.
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 61
Default 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.
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default 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.
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 61
Default 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!




  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default 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.
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default 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.
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 61
Default 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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to check if the cell contains number 1 by moving down thecolumns? cyberdude Excel Programming 6 October 17th 09 04:21 PM
Moving custom number format to NUMBER Doug Boufford Setting up and Configuration of Excel 3 July 23rd 07 11:58 PM
moving cells to another column based on number of characters JOUIOUI Excel Programming 1 June 8th 06 01:42 PM
Moving Cursor once set number of characters are entered. Parkeand Excel Programming 0 July 14th 04 05:52 AM
Moving a variable number of Sheets to another Workbook Nelson M Excel Programming 2 January 16th 04 04:01 PM


All times are GMT +1. The time now is 11:50 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"