Hi
I have a simple formula the range with in it will constantly change:
=SUM(A1:A28)
So it will need to be setup as a variable
What I want to do in VBA is to copy the range of cells from the above formula IE: A1:A28 to another column EG: B:B (B1:B28)
Is this possible?
Any help would be greatly appreciated.
try this
Sub aa()
Range("A1:A28").Copy Range("b1")
End Sub
Regards
tom1977
If You are satisfied with my solution click the small star icon on the left to say thanks.
No good, the Range ("A1:A28"). will change
("A1:A28") must be extracted from the formula somehow and setup as a varaible, I dont know how to do it.
So maybe this
Sub aa()
Dim x As Long
x = Cells(Rows.Count, 1).End(xlUp).Row - 1
Range("A1:A" & x).Copy Range("b1")
End Sub
Ill give that a go, thansk verymuch for the swift response, very much appreciated.
This is a small subroutine to add a SUM formula at the bottom of column A. If the last cell is already a formula, it will replace it, otherwise it will insert it.
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
What does this bit mean?
How does this extract the number 28 from the formula in the cell?
Cells(Rows.Count, 1).End(xlUp).Row - 1
My idea here was:
your range start in A1 and finsh in a one cell above the cell with formula (sum) so if formula is sum(a1:a28) then last cell should be in rows no 27
No, the sum formula would be in row 29 ... the formula includes row 28.so if formula is sum(a1:a28) then last cell should be in rows no 27
That doesnt seem to meet the requirements of what i asked
I want to be able to extract the range from a sum formula and then copy that range to a new column
Given that this cell range will constantly change,
Formula is Sum(A1:A28)
Please Login or Register to view this content.
Last edited by arlu1201; 06-05-2012 at 07:55 AM. Reason: Corrected code tags.
im looking for the above bold text as code
Why do you want to extract the range? You've been given the method.
If the formula is in column A, row 29, referring to rows 1 to 28, just use the code that Tom gave you. If the formula is somewhere else, column C, whatever, use Tom's code but don't subtract the 1.
Are you always so brusque with people who are trying to help you?
Don't forget, you can see your spreadsheet and know what it is you're looking for. We can only guess at that BECAUSE YOU HAVEN'T SHARED IT.
If you look at the code that I gave you, you will see that it demonstrates how to get the formula. Maybe you can pick that out?
Regards, TMS
All I need to be able to do is extract the 2 numbers from this formula: Sum(A1:A28)
I Cant see from your code below how it does this
Please Login or Register to view this content.
Last edited by arlu1201; 06-05-2012 at 07:56 AM. Reason: Corrected code tags.
Please Login or Register to view this content.
I think the miscommunication here is the OP has a formula with a summed range, but that range may not be the only data in that column. The posted solution, while cool, assumes that there is no data below this summed range.
For example, I have a sheet I'm working on with a different groups of rows. I have a group from row 8 to 101, then 103 to 197, and 199 to 215. I need to find the sums in a certain column in these three separate chunks.
Fortunately I don't need VBA for this particular problem, but it would be nice if I could determine these row chunks programatically, rather that a defined sum range. The OP sounds like the sum range will change, and his macro needs to reflect this.
I think you can get the formula as a string by calling Cells(1,1).Formula, but I don't know enough about VBA's string manipulation to parse the rest. You want to find everthing between the ":" and the ")" and VBA doesn't seem to do that easily.
More detail:
Please Login or Register to view this content.
@Josh: see post #15 and #17.
It is in the OP's interest to explain his requirements clearly and unambiguously ... with a sample workbook and a clear definition of requirements, this thread would not have gone on as long as it has.
Regards, TMS
Hi TMShucks
Thanks for that
what your formula does is go to the last row where there is a formula and then displays that formula
I know which cell I have to extract from
I need to extract the 2 numbers from the formula int hat cell: Sum(A1:A28) and assign it to a variable
x = Select the first number from formula in active cell
y = Select the second number from formula in active cell
So i can then copy those cells
Toms code works fine for the copy piece
@josh yeah i just need to extraxt/parse teh Formula sum(AX:AY)
X being the first number and y being the second number
Then i can copy those cells by dropping into other code
Range("A" & x & ":A" & y).Copy Range("b1")
Gotta go for now guys, but thanks for all your help, ill pick this up tomorrow morning,
Much appreciated
@TMShucks,
No worries. I'm obviously new here and trying to learn the rules of the board. My problems stem from the fact that I'm an experienced Python programmer, and VBA is a very different language. I'm used to slicing strings with ease.
Your solution is very instructive on how VBA "thinks."
Hey @TMSHUCKS
Your code is very very close to what i need
It is getting as far as stripping out A1:A28)
But im getting an yellow error on this line
MsgBox Range(Mid(Range("B" & lLR).formula, 6, Len(Range("A" & lLR).formula) - 6)).Address
Hey @TMSHUCKS
I think there might be an issue with it wherby if it teh sum range goes to A10:A28 or A1:A100 then the specific lenghts you put in dont return the correct values?
It shouldn't matter. It is using MID to extract the range. Starts at position 6, after the =SUM(, and takes 6 off the length to account for the =SUM( and the trailing bracket. So,MIT should get the start and end cells separated by a colon.
What is the actual formula you are using and what cell is it in?
Your version of the formula refers to Range("B" ... in the first instance.
That may explain the yellow line.
@Josh ... welcome. Sorry, didn't mean to have a go at you
Assuming the formula is in cell E1:
Please Login or Register to view this content.
So, if the formula is:
=SUM(A1:A28)
... this will copy cells A1:A28 to cells B1:B28
As a test:
with: =SUM(A10:A128) in cell F1 worked just finePlease Login or Register to view this content.
I would recommend that you do not try to extract the individual start and end cells/row as, although it can be done, it will require a messy MID function with FINDs for the open bracket, the colon, and the closing bracket as the delimiters of the cell addresses. But, your choice.
Regards, TMS
HI
Thanks for your help, I am one step away with the code below.
The only problem I have is I want to be able to copy the value of the cells to Q1 the code below is copying the forumlas, im not sure how to do that
See the section in bold.
Please Login or Register to view this content.
Last edited by arlu1201; 06-05-2012 at 07:57 AM. Reason: Corrected code tags.
Using the paste special function doesnt seem to work its coming up in yellow
Please Login or Register to view this content.
Last edited by arlu1201; 06-05-2012 at 07:57 AM. Reason: Corrected code tags.
Please add code tags before I can follow this up.
Regards, TMS
Dim lLR1 As Long
lLR1 = Range("N" & Rows.Count).End(xlUp).Row
If Range("N" & lLR1).HasFormula _
Then
Range(Mid(Range("N" & lLR1).formula, 6, Len(Range("N" & lLR1).formula) - 6)).Select
Selection.copy
Range("R1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Loop
MsgBox "Use " & L & " containers with quantity of " & Maximum & ""
Dim O As Integer
Dim Minimum As Double
Minimum = Range("S2").Value
O = 0
Range("Q1").Select
Do While ActiveCell.Value <> ""
If ActiveCell.Value = Minimum Then
O = O + 1
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
MsgBox "Use " & O & " containers with quantity of " & Minimum & ""
Murtaep,
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks