Visual Basic Macros, relative position

Dear Experts,

I am making a report, and I have a set of data, in a very looong row:

MKT_VAL	NET_ASSETS	TOT_ASSETS
46	       51		        51
6233	     6228	 	     6228


Over 100 fields.

I need this data to be changed to run down a column.


MKT_VAL
46
6233


NET_ASSETS
51
6228


TOT_ASSETS
51
6228


I tried to make a macro to do this.
It would be run after I copied and pasted the three column cells into
another area.


Sub ShiftNullData()
'
' ShiftNullData Macro
' Macro recorded 11/14/2006 by Rodger Lepinsky
'
' Keyboard Shortcut: Ctrl+q
'
    Range("A18").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Cut
    Range("B17").Select
    ActiveSheet.Paste
    Range("A19").Select
    Selection.Cut
    Range("C17").Select
    ActiveSheet.Paste
    Range("B17").Select
End Sub


However, it is working on the hard coded position.  A18, B17, etc.

How can I get the macro to work from whereever it starts?


Alternatively, is there a way to pivot all this, with no kinds of
summaries, etc? 



Thanks!

0
dba_222 (3)
11/14/2006 9:54:47 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
799 Views

Similar Articles

[PageSpeed] 5

Public Sub test()
Dim iLastCol As Long
Dim i As Long, j As Long

    With ActiveSheet

        iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        j = 1
        For i = 2 To iLastCol
            j = j + 3
            .Cells(1, i).Resize(3).Copy .Cells(j, "A")
        Next i
        .Cells(1, "B").Resize(3, iLastCol).ClearContents

    End With

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

<dba_222@yahoo.com> wrote in message
news:1163541287.473837.119540@i42g2000cwa.googlegroups.com...
> Dear Experts,
>
> I am making a report, and I have a set of data, in a very looong row:
>
> MKT_VAL NET_ASSETS TOT_ASSETS
> 46        51         51
> 6233      6228      6228
>
>
> Over 100 fields.
>
> I need this data to be changed to run down a column.
>
>
> MKT_VAL
> 46
> 6233
>
>
> NET_ASSETS
> 51
> 6228
>
>
> TOT_ASSETS
> 51
> 6228
>
>
> I tried to make a macro to do this.
> It would be run after I copied and pasted the three column cells into
> another area.
>
>
> Sub ShiftNullData()
> '
> ' ShiftNullData Macro
> ' Macro recorded 11/14/2006 by Rodger Lepinsky
> '
> ' Keyboard Shortcut: Ctrl+q
> '
>     Range("A18").Select
>     Selection.Copy
>     Application.CutCopyMode = False
>     Selection.Cut
>     Range("B17").Select
>     ActiveSheet.Paste
>     Range("A19").Select
>     Selection.Cut
>     Range("C17").Select
>     ActiveSheet.Paste
>     Range("B17").Select
> End Sub
>
>
> However, it is working on the hard coded position.  A18, B17, etc.
>
> How can I get the macro to work from whereever it starts?
>
>
> Alternatively, is there a way to pivot all this, with no kinds of
> summaries, etc?
>
>
>
> Thanks!
>


0
bob.NGs (282)
11/14/2006 11:13:28 PM
Rodger

If you name your very long row and the two rows of data under it "test"
then run the following code it should put the existing data in columns
with the active cell at the top left.

Sub test()

For i = 1 To Range("test").Rows.Count
    For j = 1 To Range("test").Columns.Count
        ActiveCell.Offset(j - 1, i - 1).Value = Range("test").Cells(i,
j).Value
    Next j
Next i

End Sub

Good luck.

Ken
Norfolk, Va





dba_222@yahoo.com wrote:
> Dear Experts,
>
> I am making a report, and I have a set of data, in a very looong row:
>
> MKT_VAL	NET_ASSETS	TOT_ASSETS
> 46	       51		        51
> 6233	     6228	 	     6228
>
>
> Over 100 fields.
>
> I need this data to be changed to run down a column.
>
>
> MKT_VAL
> 46
> 6233
>
>
> NET_ASSETS
> 51
> 6228
>
>
> TOT_ASSETS
> 51
> 6228
>
>
> I tried to make a macro to do this.
> It would be run after I copied and pasted the three column cells into
> another area.
>
>
> Sub ShiftNullData()
> '
> ' ShiftNullData Macro
> ' Macro recorded 11/14/2006 by Rodger Lepinsky
> '
> ' Keyboard Shortcut: Ctrl+q
> '
>     Range("A18").Select
>     Selection.Copy
>     Application.CutCopyMode = False
>     Selection.Cut
>     Range("B17").Select
>     ActiveSheet.Paste
>     Range("A19").Select
>     Selection.Cut
>     Range("C17").Select
>     ActiveSheet.Paste
>     Range("B17").Select
> End Sub
>
>
> However, it is working on the hard coded position.  A18, B17, etc.
>
> How can I get the macro to work from whereever it starts?
>
>
> Alternatively, is there a way to pivot all this, with no kinds of
> summaries, etc? 
> 
> 
> 
> Thanks!

0
krcowen (5)
11/14/2006 11:17:24 PM
Hi,
Might try a copy and paste special where you can change rows to columns.
-- 
David


"dba_222@yahoo.com" wrote:

> Dear Experts,
> 
> I am making a report, and I have a set of data, in a very looong row:
> 
> MKT_VAL	NET_ASSETS	TOT_ASSETS
> 46	       51		        51
> 6233	     6228	 	     6228
> 
> 
> Over 100 fields.
> 
> I need this data to be changed to run down a column.
> 
> 
> MKT_VAL
> 46
> 6233
> 
> 
> NET_ASSETS
> 51
> 6228
> 
> 
> TOT_ASSETS
> 51
> 6228
> 
> 
> I tried to make a macro to do this.
> It would be run after I copied and pasted the three column cells into
> another area.
> 
> 
> Sub ShiftNullData()
> '
> ' ShiftNullData Macro
> ' Macro recorded 11/14/2006 by Rodger Lepinsky
> '
> ' Keyboard Shortcut: Ctrl+q
> '
>     Range("A18").Select
>     Selection.Copy
>     Application.CutCopyMode = False
>     Selection.Cut
>     Range("B17").Select
>     ActiveSheet.Paste
>     Range("A19").Select
>     Selection.Cut
>     Range("C17").Select
>     ActiveSheet.Paste
>     Range("B17").Select
> End Sub
> 
> 
> However, it is working on the hard coded position.  A18, B17, etc.
> 
> How can I get the macro to work from whereever it starts?
> 
> 
> Alternatively, is there a way to pivot all this, with no kinds of
> summaries, etc? 
> 
> 
> 
> Thanks!
> 
> 
0
David4882 (672)
11/14/2006 11:33:38 PM
Reply:

Similar Artilces:

Macro to save a file as
My issue is that I have a macro to copy data from one file to another - but the first file can have different names. So - How do I create a macro in Excel to save the file with a new file name, or how do I make the file that I start the macro in - read the current file name into the macro so it keeps toggling back to this file. The second file will always have the same name. Thanks, Yosef You will need to edit the macro code. Go to Tools/Macro/Visual Basic Editor. You will see a project window listing the open workbooks. Find your workbook and you'll see a folder called "Mo...

basic example with sockets
Does someone know where I can find a very simple example of internet programming using sockets, where you create a server and a client, connect to the server with the client and then exchange a few messages and then close down? http://tangentsoft.net/wskfaq/ Sreeram Joachim wrote: > Does someone know where I can find a very simple example > of internet programming using sockets, where you create a > server and a client, connect to the server with the client > and then exchange a few messages and then close down? ...

Using Visual C++ 6.0 MFC Application
01/19/2004 Using a single document or a dialog application, I am able to use my own variables that are declared in the same source file, however, if I try to declare a global variable in a header file or a source file included before the code I am using, I get the error "Undeclared Identifier". Example in Old C: "First.h" int i; "Main.cpp" #include "First.h" main() { i=5; printf("%d",i); } When I declare a variable in a header file, in Class View - Globals, my variable does show up, b...

Standard Visual Basic vs Visual Basic for Applications
I've recently purchased Front Page and understand that I can code with Visual Basic as part of this software package. 1. What does Front Page contain: Standard Visual Basic or VBA? 2. Do either or both work with Visual Studio? 3. Is Visio different from Visual Studio? -- Deb Front Page and Visio are both Microsoft Office applications that contain VBA (Visual Basic for Applications). These programs are productivity applications first and programming platforms second. Visual Studio is a pure programming application. (All it does is let you write other programs.) Older versions of ...

very basic MS access 2007 (button click no read issue)
I added 2 text fields and a button to a form in MS access 2007. and i went to the code builder and tried to add values to the text fields . ex: textBox1.text = "name" i coded this to the button click event.. but some times after i get an error.. the button click property never works... so i have to create a new form and redo it. can some one tell me why this is hapenning , and tell me a way to prevent this.. In Access, you can only use the text property if the control has the focus. Instead, try: textbox1 = "Name" or textbox1.Value = "...

Execute Macro
Hello, Is there a way to execute a macro in a formula ?? For example a need to execute a macro if the value in a cell is out of range. Many thanks for your help Yves Hi not directly, you can however, put worksheet change code against the sheet to read the result of the formula and run appropriate code, e.g. ------ Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" And Target.Value < 4 Then Application.EnableEvents = False Call Macro1 Application.EnableEvents = True End If End Sub ------- if you'ld like help ...

I Visual Basic Error "File Not Found" when Excel opens
Good afternoon. I am using XP Pro at work and My computer just started doing this. when I open Excel, I get a message pop up that says in title "Visual Basic Error" and in body of error window is "File Not Found". If I click on help, says something about error 53 but when I try to look that up on Microsoft's website, can't find what seems to apply to my issue. And now when I go into my personal.xls to view my macros, I can get in, but if I try to save anything, I get a window pop up saying Excel must be shut down, and then a window pops up asking me if I want...

Timed macro
How can I get a specific macro I have created to do it's function every 1 minute automatically? Any help would be appreciated. Chip Pearson has lots of instructions at: http://www.cpearson.com/excel/ontime.htm ftahbaz@gmail.com wrote: > > How can I get a specific macro I have created to do it's function every > 1 minute automatically? > > Any help would be appreciated. -- Dave Peterson Didnt really help...too confusing for me. Dave Peterson wrote: > Chip Pearson has lots of instructions at: > http://www.cpearson.com/excel/ontime.htm > > ftahbaz@gm...

CRM and Great Plains Basic Installation Instructions for Client
I did a search for installation instructions for CRM and it reported no articles found. Just loading CRM can be a major ordeal with this fix and that fix, and to not have an easy to find detailed instructions is just confounding to me. How about just a basic list on how an installation of CRM and Great Plains on the client system. Thank you. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow t...

Books on writing EXCEL MACROS (for beginners)
I am looking for some good book(s) on Excel formulae, functions and o writing Excel Macros for beginners. Can anyone help me -- Message posted from http://www.ExcelForum.com I'd take a look at John Walkenbach's Excel 200x Bible: http://j-walk.com/ss/books/index.htm If you think you'll want more, and can afford it, I'd really recommend getting both the Excel 200x formulas and Excel 200x Power Programming with VBA, since you may not be a beginner for very long. In article <bouncer.144f8i@excelforum-nospam.com>, bouncer <<bouncer.144f8i@excelforum-nospam.c...

Visual C++ AND .Net
What would be the best package for me to purchase if I want to move from VC++ 6.0 and VB 6.0 to .Net? I think it is Visual Studio .Net (around $800.00). Is this correct? I just need a little direction since I have just been given the go ahead to purchase these items. Any information on these products - from someone who has used them - would be helpful. Thank you Ken >-----Original Message----- >What would be the best package for me to purchase if I want to move from >VC++ 6.0 and VB 6.0 to .Net? I think it is Visual Studio .Net (around >$800.00). Is this correct? I just nee...

chart MAcro to change on activecell
Here is the macro below.. The only problem is that the columns change every month. so range (columns)keeps widening.... I have sorted it by selecting range upto column N. so it is provided for all 12 months. But now the "Grand total" column which is always the last column also gets included..(.which shouldnt be included in the range for the chart) Is there a way to modify this macro ? Sub updatechart1() Dim ThechartObj As ChartObject Dim Thechart As Chart Dim Userrow As Long Dim CatTitles As Range Dim SrcRange As Range Dim SourceData As Range If Sheets("summary").Ch...

Looking for Visual C++ programmer with MS SQL Server 2005 to work off-site on small project
Looking for Visual C++ programmer with MS SQL Server 2005 to work off-site on small project. Please send me an email to: larryTAKEOUT@seldin.net Lawrence M. Seldin, CMC, CPC Contributing writer for FUTURES Magazine Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP NOTE: To send me an email, remove TAKEOUT from my email address: larryTAKEOUT@seldin.net NOTE: My web home page: www.seldin.net ...

Can't find a working macro in Excel 2007
Some time ago, I wrote a couple of macros for an Excel workbook. I recently returned to that workbook, discovered the macros, and would like use them in other workbooks. Unfortunately, they will only run in that workbook. The workbook is named xyz.xlsm. I suspect that makes a difference. In xyz.xlsm, I clicked on the View tab, the Macros button, and View Macros. I got the Macro dialog box, but no macros are visible. "All open workbooks" is in the Macros in: field. I also unhid PERSONAL.XLSB. No macros. Can someone help me find these macros and then make them available to all work...

Printing a treeview on a report (or anything visually similar)
The topic says it all. I need to be able to print a hierarchy on a report. There's no limit to the number of levels (tho no more than 10 at the moment). I'm looking to simulate the look of a treeview. I'm stumped and I don't know where to start. Any suggestions are very welcomed. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200801/1 ...

Modifier and Visual Basic Editor dim ??
Hi, I have already set the security to access the customization tools (Modifier and Visual Basic Editor) for my user login. Why is both of this options still dim -- I can't click on these items (Modifier and Visual Basic Editor) because it is disabled. Please advise. Thanks a lot. it wont be available until you have purchased it - it is a separate module. You will get a new set of reg keys that will activate the choices. HS "Dolly" <Dolly@discussions.microsoft.com> wrote in message news:1553009B-B7A0-4D54-AAD1-ADD7F17D49A2@microsoft.com... > Hi, > > I have a...

How to create chart basic
How to create a scatter chart with spread sheet below. The x axis may be location and Y axis are lot name Lot value location A 3 1 A 4 2 A 5 3 B 6 1 B 4 2 B 2 3 C 5 1 C 6 2 C 8 3 D 4 1 D 9 2 D 5 3 Daniel, Highlight the whole chart of data. Select the Chart Wizard on the top of Excel screen. Select the scatter plot. Data should be "rows" not "columns". Select the "Series" tab. "Name:" ="A" "X Values:" =Sheet1!$B$2:$B$4 "Y Values:" =Sheet1!$C$2:$C$4 This assumes that your data is in column A thru C, Lot in A1, value in ...

Non-macro method (like conditional formatting) to insert text in cell?
(XL 2007) Currently, we use conditional formatting to change cell colors when a certain cell value is selected. The colors alert use that this item needs special atention. The colors, though, can make it difficult for some people to read the text. For example, column A has report numbers. If the user selects "Urgent" in Col B, the report number cell turns red. If he selects "Completed", it turns blue. If it needs revising, it turns green. I'm wondering if there is a way that a "text flag" can be inserted into a cell using a non-macro method like contio...

Microsoft visual C++ Runtime Error #2
When I open Outlook 2000, the first time I ger the following error "RUNTIME ERROR PROGRAM:C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\OUTLOOK.EXE" It says this application has requested the runtime to terminate it in an unusual way. Please contact the applications support team for more information. There are two of us on the outlook e-mail that we share. We have two profiles and sepaarate PAB & PST files. I get it the first time, and when I close it out, it will open on the second go around with no problem. When I open the other profile, I get it the first time I open it, the...

activate a cell value through a macro
I would like to activate a cell value which is a formula based throgh a control button.Iam new to macro code .As some users wants see their current balance of their a/c,query"wants to see your current balance" by pressing button "yes" the cell should display his balance otherwise the cell value blank.Any body from excel experts,please help me how to write assign a macro for this task Why do you want to use a button? Why not just add a formula to that cell to continuously show the balance? -- HTH RP (remove nothere from the email address if mailing direct) "...

making a macro run first thing when opening a workbook
is there a way to make a macro run right away when you open a workbook???? Hi either name your macro Auto_Open() or place your code in the workbook_open event -- Regards Frank Kabel Frankfurt, Germany Dennis Schuessler wrote: > is there a way to make a macro run right away when you > open a workbook???? ...

Visual Studio 8.
I currently use Visual C++ Version 6. Should I purchase and begin using Visual Studio 8? Do I qualify for the the upgrade version of Visual Studio 8? TonyG wrote: > I currently use Visual C++ Version 6. > > Should I purchase and begin using Visual Studio 8? Maybe, or perhaps wait till the end of the month when Visual Studio 9 is released. :-) > > Do I qualify for the the upgrade version of Visual Studio 8? Yes. Bo Persson Yes and yes. No reason to go through all those other version in my opinion. Tom "TonyG" <TonyG@junk.com> wrote in message ne...

Macro to paste chart as enhanced metafile
I have a large number of excel charts to paste into Word as enhanced metafiles. I need a macro to do this, but can't figure out how to write it. Any help would be appreciated! ...

Macros disabled?
I just tried to record a simple macro that would open several CSV files and record them as XLS's. The recording part went fine, but then I tried to open the file to test the macro and got this message: "Macros in this workbook are disabled because the security level is high, and the macros have not been digitally signed or verified as safe." The macros are being made to run on various machines within an office. Should I change the security level of all of the machines? Or how can I digitally sign them? Thanks I'd suggest setting the machines to Medium so users can pick w...

need some basic help
I have a basic daily sales sheet that has a number value in column one for the day of the month from 1 to 31, column 2 has the daily sales that I put in manually and column 3 has the total sales for the month =SUM(B2+C1). At the bottom on row 31 I calculate daily average sales with a basic =C3/C1 and I have to change on a daily basis by hand... all extremely basic stuff. But I'm looking for a way to calculate the daily average sales automatically as I put in the daily sales each day without having to edit the row 31 formula every day. I tried googling for daily average sales but th...