6 Replies - 5030 Views - Last Post: 13 April 2012 - 11:19 AM Rate Topic: -----

#1 MattRidge  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 27
  • Joined: 27-January 12

Script required to do specific task of copy column, move and Delete.

Posted 11 April 2012 - 05:51 AM

Ok, first off I am learning VBA for Excel, it is a trying issue in where my boss wanted it yesterday, with no knowledge of how to get there today, so i am looking here for some guidance in how to get this finalized.

I've gotten this far, in where I can get the code to copy from one column to another, but I need two other functions and I don't know how to get them working with the code I have now. I've been reading over the internet, trying to learn what I can but a lot of the sites out there are VBA for Excel 2003 and it seems that the 2007 sites, well do things not exactly complete, I am using Excel 2010 at work and Excel 2011 at home, so I am getting mixed messages about what to do since VBA in Excel 2003 and 2007 never mind 2010 is is different.

Here is my code, as I said before I've gotten it to where it copies and pastes, I thought I could delete and paste but it seems that VBA doesn't work that way, or if it does I haven't found out how.

The other thing I need to try to get into this code, is make it where I can choose which row to use as an identifier, so that it could be row 6, row 7, etc, and then finish the rest of the script as described above.

Any help would be greatly appreciated.

Option Explicit

Sub MoveColumns()
    
    Dim cel As Range
    
    With Sheets("BackOrder")
        For Each cel In Intersect(.UsedRange, .[B6:N6]).SpecialCells(xlCellTypeBlanks).Cells
        Next
    End With
End Sub


This post has been edited by MattRidge: 11 April 2012 - 06:02 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Script required to do specific task of copy column, move and Delete.

#2 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: Script required to do specific task of copy column, move and Delete.

Posted 11 April 2012 - 12:43 PM

Well, this code doesn't look like it does anything at all, let alone copy columns. Try recording macros and looking at the result. That should give you some ideas.
Was This Post Helpful? 0
  • +
  • -

#3 MattRidge  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 27
  • Joined: 27-January 12

Re: Script required to do specific task of copy column, move and Delete.

Posted 11 April 2012 - 03:04 PM

View PostBobRodes, on 11 April 2012 - 12:43 PM, said:

Well, this code doesn't look like it does anything at all, let alone copy columns. Try recording macros and looking at the result. That should give you some ideas.



Actually it does, I realized a mistake, but the code does do something. On like 6 of each rule the columns it copied over what I wanted it to do....

The code should of been:


Option Explicit

Sub MoveColumns()
    
    Dim cel As Range
    
    With Sheets("BackOrder")
        For Each cel In Intersect(.UsedRange, .[B:N]).SpecialCells(xlCellTypeBlanks).Cells
        Next
    End With
End Sub



You'll see what I mean if you make that change.
Was This Post Helpful? 0
  • +
  • -

#4 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: Script required to do specific task of copy column, move and Delete.

Posted 12 April 2012 - 08:01 AM

I'm not setting up your code, just looking at it. It sure looks to me like you are looping through all the blank cells in the intersection of the sets UsedRange and column b through n, and doing absolutely nothing to them in the loop. Since there isn't any code in the loop.
Was This Post Helpful? 0
  • +
  • -

#5 MattRidge  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 27
  • Joined: 27-January 12

Re: Script required to do specific task of copy column, move and Delete.

Posted 12 April 2012 - 09:17 AM

View PostBobRodes, on 12 April 2012 - 08:01 AM, said:

I'm not setting up your code, just looking at it. It sure looks to me like you are looping through all the blank cells in the intersection of the sets UsedRange and column b through n, and doing absolutely nothing to them in the loop. Since there isn't any code in the loop.



If you aren't willing to set up my code, then you aren't going to see what it does... It does something, as I have said before. It copies everything over exactly like I want to, but it doesn't delete the old lines of code. Which is what I want it to do.

Do you know how to do it, and can you help me get there? I've already said I am new to this, and I have been looking for help, you obviously know what you are doing, but telling me the code does nothing if you don't want to test it sort of makes me question if you really want to help me or if you are here to instigate.

I don't ask for help for the hell of it, I ask because I am stuck.

Attached File(s)


This post has been edited by MattRidge: 12 April 2012 - 09:18 AM

Was This Post Helpful? 0
  • +
  • -

#6 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: Script required to do specific task of copy column, move and Delete.

Posted 13 April 2012 - 09:18 AM

Well, I'm sure I can spend some time figuring out how an empty for each loop miraculously copies data from one place to another, just because you say that it does, but if you're going to adopt that sort of attitude with people who are helping you just for the hell of it, then I guess I got better things to do. After all, you appear to have better things to do than explain why it is that your empty for each loop does something when every other for each loop that I've ever seen in my 15 years of experience with various flavors of VB only does whatever is in the loop, which in this case is nothing.

Instead, you'd like me to go to the trouble of setting it up first, because, well, you're too busy to go to the trouble of explaining this, and of course I'm too lazy to bother to witness this miracle for myself. The miracle where an empty for loop actually does something, for the first time in history.

But hey, says I. Maybe I didn't explain myself clearly, says I. Let me tell him again. You have a for each loop with nothing in it, and it isn't going to do anything. The emperor has no clothes. And, you have the effrontery to downrep me for telling you that this code can't actually copy and paste something without actually having any commands that copy and paste stuff in it, because I didn't go to the trouble of setting up your problem and proving what was already obvious to anyone with junior-level programming experience?? So hey. I have more polite people I'd rather work with. Just for the hell of it, you know. I won't even downrep you back, although probably someone else will.

Just so you know, most people on here don't download files. It's a security problem, and it usually means that people are just too lazy to go to the trouble of pulling out relevant parts of their code and posting them. They'd rather just dump the whole thing on someone else and say here you are, fix it for me, there's a good fellow. You being stuck and all, I guess that's what you're looking for too. This site is about helping people think for themselves, not about writing scripts for people because they "require" it.

Perhaps someone else will help this guy, just for the hell of it.

This post has been edited by BobRodes: 24 April 2012 - 08:22 AM

Was This Post Helpful? 1
  • +
  • -

#7 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: Script required to do specific task of copy column, move and Delete.

Posted 13 April 2012 - 11:19 AM

Just to make sure I hadn't fallen into the twilight zone, I actually did go to the trouble of creating a spreadsheet. I filled up cells a1 to q12 with varying values, blanked out a few (including some in and some out of the columns b through n and as expected the code did precisely nothing, except whirr around for a couple of seconds. I then added this code into the for next loop: MsgBox cel.Address and to my complete non-surprise, I got a message box with the address of each cell that was blank in that range, and not for the ones that weren't, and not for the blank ones outside of that range.

Imagine that...

This post has been edited by BobRodes: 13 April 2012 - 11:43 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1