Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to check if the cell contains number 1 by moving down thecolumns? | Excel Programming | |||
Moving custom number format to NUMBER | Setting up and Configuration of Excel | |||
moving cells to another column based on number of characters | Excel Programming | |||
Moving Cursor once set number of characters are entered. | Excel Programming | |||
Moving a variable number of Sheets to another Workbook | Excel Programming |