Re: Worksheets Destination:
message
Re: Worksheets Destination:
message
Re: Change Formula Based On Cell Value In A1
You could try changing:
=SUMIF(SHIPPED!$A$3:$A$13,A2,SHIPPED!$B$3:$B$13)
to:
=SUMIF(SHIPPED!$A$3:$A$13,A2,indirect("SHIPPED!$"&a1&"$3:$"&a1&"$13")
Re: Moderators: error Posting Code...
Additional bug. I can't edit the post with the code in it.
Try to edit your post above...
Re: Copy Rows With Common Number then paste in different area
Follow up Fuad -
The macro above is to be applied to data already imported (~10K rows of data in 3 columns)
The Macro will not sort for you it only formats for printing - so sort your data B4 you run the Macro if you want to.
If you really have 10K rows....notice that with 256 columns available....and 3 columns of data to copy over and over...you can only hate about 85 column sets before you hit the end of the spreadsheet.
At a row depth of 100, 10K lines won't fit.
I have made the row depth a variable instead. I would consier making the row depth much larger (1K, 2K, etc.) This way you can fit your data to be 1 page wide worth of columns....and just let it print out that way.
Good luck
Re: Moderators: error Posting Code...
Roy - Interesting.
I can Post the code (with tags) without a problem.
It is when I try to generate a preview (with tags) that I get the error (every time.) Small bug somewhere.
Re: Copy Rows With Common Number then paste in different area
Faud,
Try:
Sub moveit()
Dim MyRows As Double
Dim TotalRows As Double
Dim Counter As Double
Dim StartRange As Variant
Dim Newrange As Variant
MyRows = InputBox("This macro will copy and paste the current data into mupltiple columns. Make sure that you have at least one cell in the targetted region of data selected. How many rows deep do you want the finished data to be?") - 1
StartRange = ActiveCell.CurrentRegion.Cells(1).Address
TotalRows = Selection.CurrentRegion.Rows.Count
Counter = 1
Do Until Counter > (TotalRows / (MyRows + 1))
Newrange = Range(Range(StartRange).Offset((Counter * MyRows) + Counter), Range(StartRange).Offset((Counter + 1) * MyRows + Counter, 2)).Address
Range(Newrange).Copy Destination:=Range(StartRange).Offset(0, Counter * 3)
Counter = Counter + 1
Loop
End Sub
Display More
Make sure to have at least one cell in your data selected B4 you run the Macro. Any data to the right will be overwritten.....
HI guys. In an attempt to help with a issue in another post I am trying to post the following code:
Sub moveit()
Dim MyRows As Double
Dim TotalRows As Double
Dim Counter As Double
Dim StartRange As Variant
Dim Newrange As Variant
MyRows = InputBox("This macro will copy and paste the current data into mupltiple columns. Make sure that you have at least one cell in the targetted region of data selected. How many rows deep do you want the finished data to be?") - 1
StartRange = ActiveCell.CurrentRegion.Cells(1).Address
TotalRows = Selection.CurrentRegion.Rows.Count
Counter = 1
Do Until Counter > (TotalRows / (MyRows + 1))
Newrange = Range(Range(StartRange).Offset((Counter * MyRows) + Counter), Range(StartRange).Offset((Counter + 1) * MyRows + Counter, 2)).Address
Range(Newrange).Copy Destination:=Range(StartRange).Offset(0, Counter * 3)
Counter = Counter + 1
Loop
End Sub
Display More
The Code executes just fine in Excel - but when I post it here with code tags I get the following error from the server:
Fatal error: Call to undefined function handle_bbcode_code() in /home/ozgrid/public_html/forum/includes/class_bbcode_alt.php on line 171
Am I doing something wrong?
Re: Method Range Of Object_global Failed
HI atran,
Unless there is more that you haven't shown us, in the code above "myRange" will refer to column "H" in the active sheet of the active workbook .
This smay be perfectly fine - and you might not need to define a workbook or worksheet....it depends on what you are trying to accomplish.
It can be easy, however, to have the code to be focused on the wrong sheet or workbook when it is running. In some cases you might need to define the full path of "myrange" like...
..to make sure the code is focused on the right place. I couldn't say either way in your case without more info.
Can you post a copy of the workbook and the rest of the code you are trying to run?
Re: Method Range Of Object_global Failed
I don't see anyting immediately wrong with what you have written.
You have not defined a workbook....is the focus on the wrong workbook when you do your loop code?
That is the error I would be likely to make LOL.
Re: Update Links Automatically
Do you care if the file updates to all of its sources rather than just the 1 file you are defining? If you don't care - then you could use
Then you don't have to worry about the file name/path being typed correctly.
Re: Randomly Shuffling Data In A Column
KoKo - this can be done with VBA (then best way)- or with a combination of worksheet functions.
If you want to stay out of VBA...look at the attached workbook for an idea of how you could set it up. The yellow cels do what you are asking for.
This relies on "RAND()" functions in the worksheet (which are random number generators) - so every time the spreadsheet calulates (any cell entry) the random order will change.
If you don't want things to change all the time - you may have to turn calculation off.
Good luck.
Re: Copy Rows With Common Number then paste in different area
Are you sure you need a macro?
You could highlight the data and apply auto filter.
Filter on (custom...) and chose "begins with". Set the value to 1 (or space 1 if there is a space in front of the 1's)
This will hide everything that does not start with a 1. Print the filtered data.
****I reread your post.*****
If column 1 contians only numbers....you don't need to use "Custom" on the auto filter. You can simply sort by the value 1 from the drop down. Play with it....you'll get it.
Re: Getting Address Of Range Copied To Clipboard?
RbRhodes -
Wow - thanks for all that hard and inventive work. I appreciate that. I'm sure I can make that work. Again, I greatly appreciate it.
Roy, RB, Anyone else -
It still seems very very very odd to me that Excel goes to the trouble of "marqueeing" manually copied ranges (with the rotating dashed line around the exterior) and yet there appears to be no property (for any class) for that marqueeing...wierd.
I was just hoping someone might no a method property that is hidden.
I now have begun to realize that the clipboard is a very strange animal. I guess anything related to it is going to be strange too.
Re: Getting Address Of Range Copied To Clipboard?
Bump
Re: Update Values Applying Goalseek In Macro
Rishi - if you want the values in "sheet2" to change so that the sum formulas on "total" hit a minimum of your criteria (50)...
...then you don't need goal seek.
The following code should do it
Sub set_to_criteria()
Mycrit = InputBox("what is the criteria")
For Each x In Selection.Cells
myrange = x.Address
delta = x.Value - Mycrit
If delta < 0 Then
With Worksheets("sheet2").Range(myrange).Offset(0, -1)
.Value = .Value - delta
End With
End If
Next x
End Sub
Display More
Note - however that the reference is hardcoded to "sheet2" and the cell to change is always offset(0,-1) for the address of the starting cell on "total" sheet. This means that if your sheet layout changes ...you would need to revise the macro.
Good luck.
Re: Scale In X Axis
On the chart - right mouse click on the x axis data. Select "format axis". select the "number" tab. Change category to date.
Re: Automatic Update
No need to record.
If you are a novice to VBA but still want to try to do this without becming a programmer - here are some steps you could try. read them all before proceeding.
1: start excel and open 1 blank new workbook.
2: open the VBE (tools>macro>visual basic editor)
3: You are now in the Visual Basic editor. On the left hand side you will see something like "VBA project (book1)" listed. Select (single click) this project and go to the menu Insert>module
4: You now have a blank module on the right side of the screen. Copy and Paste exactly what I posted in this forum (including the "sub end_date" and the "end sub" lines) into the blank module.
5: go back to Excel
6: go to tools>macro>macros...
7: This will bring up a list of available macros. Find set_date in the list (it may be the only one)
8: select (single click) "set_date" and click the optinos button.
9: assign a keyboard shortcut try capital "D" (for date) - press OK
10: press cancel to close the marco list.
11: save this file to the "xlstart" directory on your hard drive so that it will open whenever you start Excel. The path could be something like:
C:\Program Files\Microsoft Office\OFFICE11\XLSTART (but it could be slightly different depending on how Excel was installed.)
You may have to find the "xlstart" path on your hard drive if it is not in the path above.
Give the file any name you want.
12: Hide the file (Windows>hide.)
Exit excel and say yes to save changes to your file (whatever you named it.)
Now the file with the macro will open anytime you open Excel (hidden) and you can run the macro anytime by pressing control+shift+D at the same time.
Delete or move the file if you want to stop using it at any time.
Good luck