| Home |
| Search |
| Today's Posts |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax again for DA
Again, can anyone tell me why?
"Dean" wrote in message ... Since Bob does not seem to be around and it's been a few days, can anyone else tell me why the macro below is copying to cell R12, not R11. Thanks! Dean "Dean" wrote in message ... Thanks, Bob. I can't believe I am messing this up. Here is the front of my macro, adapted from what you supplied: Sheets("Output").Select ActiveSheet.Unprotect Application.Goto Reference:="R18C2" ActiveCell.Select If ActiveCell.Offset(1, 0).Value < "" Then Range(ActiveCell, ActiveCell.End(xlDown)).Select End If Selection.Copy Sheets("Joe").Select Application.Goto Reference:="R11C18" Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ' this row may not be needed Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Selection.Copy ActiveCell.Offset(0, -17).Range("A1").Select 'now we will past into column A of the REPORT sheet Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False As I understand it, it should go to cell B18 of Output. Then, if B19 is empty, it should copy cell B18 to cell R11 of "Joe". Then, since there is also now only one entry in the pasted area of Joe, it should leave it in the same place after sorting. Then, it should copy this one cell back to cell A11 of Joe. Cell A11 is Ok despite the fact that it seems that the above macro is first copying to cell R12, not R11, of Joe, which I do not understand. Can someone tell me why it is doing this? Doesn't Range("A1).Select mean to select the current cell? Kindly put a comment beside any lines in the macro that you think I don't understand. Thanks! Dean "Bob Phillips" wrote in message ... Range("N18").Select If Range("N18").Offset(1, 0).Value < "" Then Range(Range("N18"), Range("N18").End(xlDown).Offset(-2, 0)).Select End If -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dean" wrote in message ... For the following follow-up question, ignore the two row offset which applies in a different worksheet. Occasionally, (in the non offset case), there is no other entry below the first cell in question, in which case the end-down brings us to the bottom of the worksheet, row 65536 or whatever. Is there are a way to rewrite your command to tell the macro not to do the end down in that event, but just select the single cell? BTW, if the cell below is empty, all of the cells below will be also, so there is no risk of missing something. Thanks! Dean "Bob Phillips" wrote in message ... Range(Range("N18"), Range("N18").End(xlDown).Offset(-2, 0)).Select -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dean" wrote in message ... He doesn't exactly tell us why the recording doesn't work, but this will do just fine, I suppose. But will this work if I then try to go back up by two rows? Thanks! Dean "Frederik" wrote in message ... Hello Maybe this tip can help... http://www.j-walk.com/ss/excel/tips/tip10.htm It's on the site of John Walkenbach, an Excel goeroe! greetz "Dean" schreef in bericht ... For keystrokes, I want my macro to go to a fixed cell location, say cell N18, then while holding the shift key down, then do an end-down, then move the bottom up by two rows (same thing as two cells since it's a single column), then release the shift key. In this way, I select a variable-length block of cells that is the whole contiguous block except for the bottom two cells which I always want to ignore. I try recording these keystrokes in a macro with "use relative references" set to either of the two possible ways, but either way, the macro records a fixed set of cells, not the variable length block I need in general. Why doesn't it work? And, if recording can't do this, please give me the code that works - I get a little confused on the syntax. Here are the two ways it comes out (for the particular none-empty cells I have right now), neither of which is a variable length depending upon where the bottom of the list happens to be: Application.Goto Reference:="R18C14" Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A45").Select or Application.Goto Reference:="R18C14" Range(Selection, Selection.End(xlDown)).Select Range("N18:N62").Select Thanks! Dean |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IRR Syntax | Excel Worksheet Functions | |||
| Help with syntax | Excel Programming | |||
| syntax | Excel Programming | |||
| syntax | Excel Programming | |||
| syntax for a sub ( ) | Excel Programming | |||
![Dean[_8_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Threaded Mode
