I need to be able to use cell name instead of cell no: K1036 as this cell gets moved all the time I just need the total amount.
I have been using this previous macro which works well but when someone moves the cell up rows or down rows it gets messed up.
TIA
HTML Code:
Please Login or Register to view this content.
Last edited by slohman; 04-13-2016 at 07:45 PM.
I might be reading this wrong, but if you want to give the cell a static name, simply select it and go to the name box (upper left where the cell address is shown) and type it in replacing the cell address. Names must start with a letter, cannot resolve to a cell reference such as END2, or have spaces, and the only punctuation allowed is the underscore.
So select Cell K1036 and in the name box type in something like "My_Total." No matter what you do (add or delete rows and columns) or where you are (you can be anywhere in the workbook), My_Total will still point to the cell. You can use it as is in an excel formula such as = A2 / My_Total or refer to it in VB as Range("My_Total").
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
I have already named the cell Mytotal but I dont know how to fix the macro to apply it to a cell name instead of cell 1036.
For i = 1036 To 1035 '(Sheet Name) Row No:
If Sheets(SheetName).Range("K" & i).Value <> "" Then 'DB Ref No:
Sheets("Cost").Cells(MyRow, MyCol).Value = Sheets(SheetName).Range("K" & i).Value
End If
Refer toPlease Login or Register to view this content.
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
Is there a way of shortening the macro
instead off all of this
Please Login or Register to view this content.
As best I can tell
The loop no longer serves a purpose since the named range "knows" where it is. Also you defined MyRow and MyCol but assigned them to constants.Please Login or Register to view this content.
Also, if MyTotal has a scope of workbook, you don't need Sheets(SheetName).Range("MyTotal").Value. Range("MyTotal").Value will do. IN fact, since .Value is the default Range("MyTotal") is good enough. However, there is absolutely nothing wrong with fully explaining the expression. It's good coding practice.
Thanks I am using this that works great
My problem is that I'm trying to use the same kind of thing with a cell range "Part_No" which has multiple cells A20, A30, A200:A500 but I'm only getting the first cell repeated on my worksheetPlease Login or Register to view this content.
Please Login or Register to view this content.
This code I need to change to "part_no" instead of .Range("G" & i).Value as I need to add multiple part no's to my cost sheet. I have tried changing it to .Range("Part_No").Value but I am only getting the first part no in the define name copied all the way down from B30 to B78. It is not choosing the next part no in the multiple define name range which I have set up.
Please Login or Register to view this content.
Sorry I need to loop through my "cost" worksheet and place any part_no's that appears in the "sheetname" "a1:A5000"(which changes from month to month) that has stock qty's which will be in row ("d1"D5000")
The "cost" worksheet is set up with "B30:B78" which my part_no" should appear in then I will also write the vba macro so that the qty will appear in "D30:D78"
Sorry I don't think i'm explaining myself to well. I will start again.
I have 2 worksheets "Cost" (name never changes) and "Option1" (names changes regularly)
I am using this code that works really well but I need it to change to a define name range
"d1:D5000" Named range = "Part_No"
I need it to loop through my "Cost" sheet Column "B30:B78" and place the Part_No that has a Qty amount which is Named range = "Qty" which is in Column G1:G5000".
Then loop through again and put the Qty amount in Column "D30:D78"
Please Login or Register to view this content.
this looks a crosspost to me
http://www.mrexcel.com/forum/excel-q...lications.html
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
I thought I had to make any edit to both threads so people could see what others had posted. If that is not correct I apologise.
We shooting in the dark here. Please attach the workbook so we can see what we are working with.
Sample attached.
I think I see what you are trying to do here. You want to look up the part number on the Cost Sheet and transfer the information to the Option Sheet.
Could we get some sample data (make up some non-sensitive stuff) on the Cost sheet, and "dummy up" what you'd like that data to look like on the Option sheet. This will help us fill in the slots for you.
I am particularly interested in what logic decides whether a part goes into Single Structure, Orbit Structure, Standard Structure ... etc.
I get the impression you would like the sections to be "sizable" based on how many items go into them and not fixed.
I actually want to look up the part number on the Option 1 sheet (could be Option 2 etc) and copy onto Cost Sheet.
I have attached a sample that I have been using that works wells but people keep adding to the Option 1 sheet and muddles all the rows up so I thought it best to use define name instead so they move the rows up or down and it would upset the Cost Sheet.
My previous post had the sample macro that I have been trying to fix you will see it is very long in the sample.
What column on the Option sheet contains the part number?
I figured it out. It's column D.
If it weren't for the variable sheet name, this problem could be solved with Match and Index. Whenever you have a variable sheet name, the first thing you should think of is INDIRECT. This will work, provided we know what the sheet name is. This code returns the sheet name:
So to find the row with the part number, you can use:Please Login or Register to view this content.
=MATCH(B27,INDIRECT("'"& getsheetname() & "'!D:D"),0)
And to find the quantity and cost associated with the part number use:
=INDEX(INDIRECT("'"& getsheetname() & "'!A:K"),MATCH(B27,INDIRECT("'"& getsheetname() & "'!D:D"),0),6)
=INDEX(INDIRECT("'"& getsheetname() & "'!A:K"),MATCH(B27,INDIRECT("'"& getsheetname() & "'!D:D"),0),7)
That all looks good but I think it is around the wrong way.
The part_no doesn't appear on the cost sheet (until this macro is run) it is only on the Option "sheetname" I need it to find qty amount which will be greater or equal to 1 in Option worksheet that will appear in row F which in turn has a part_no attached row D in Option worksheet which will then transfer to row B into Cost worksheet and the Qty will appear in row D of Cost Worksheet.
After that I will have to work out how maybe a vlookup to transfer the price over to Cost worksheet (sample of Cost sheet attached previously)
In that case, what we need to do is use the function to find the sheetname and then look down the column that contains the quantity in a VB routine.
Please Login or Register to view this content.
I'm sorry but you are changing the entire macro to something that doesn't even look similar to what I had before.
All I needed was the macro to find anything that was in the name range "Part_No" also "Qty" (in Sheet Option 1 or "Sheetname") and transfer those Part_No and Qty's starting on B30 onto the cost sheet.
Can anybody help with this TIA
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks