error 1004... moving data from first col to another (variable) column... complicated simple problem...

[SnyperBunny]

Hi guys, I have a column of data down my col B, and then more data staggered in various columns further to the right on my worksheet, I'm trying to write a 'simple' <.< *grrr* script to move the column over to the cell one to the left of my other data.

For example, if this is my current sheet: (the '......''s are essentially blank cells.

a.................................1
b.............................................2
c.....................................3
d.......................................................4
e...................................................................5
f...............................................6

I want it to look like this:

................................a1
..............................................b2
.....................................c3
.......................................................d4
....................................................................e5
.................................................f6


(note that the "a1", "b2", etc have nothing to do with row or column idicies in this example... I just used them as as easy way to demonstrate what I'm trying to do.)

I have the code below:

    t = 3
    k = 3
    
    Do While t < 65536                          'move the first col data to they should be
                                                
      If Cells(t, 1) <> "" Then                 'but also stop when you hit the bottom, or when you reach a blank row in col B.
        first_jan = ThisWorkbook.Worksheets(1).Cells(t, 2).Value
        
        If Cells(t, k) = "" Then                'checking that the Jan 2014 is not actually supposed to be Jan2014 with the rest of the column filled in.
            
            Do While Cells(t, k + 1) = ""       'while the cell col 'k+1'(the NEXT column) on the current row IS Nothing, increment k
                k = k + 1                       'will break once 'k+1' is equivalent to a cell NOT equal to nothing - therefore k is the cell that Jan2014 must be put into.
            Loop
                    
        End If
        
        ThisWorkbook.Worksheets(1).Cells(t, k) = first_jan
        
        ThisWorkbook.Worksheets(1).Cells(t, 2).Value = ""
        
      End If
      t = t + 1                                 'increment t for the next iteration.
    Loop

Prolem with is though, is that in amongst all the other giant code I have for this book, its just not doing what it is supposed to be doing... depending on what I tweek, it varies from doing nothing at all, to moving the data only one or two rows over.... never fully across to where it is supposed to be.

I tried to fiddle with a simpler version of it in a test workbook to get it working, but I am getting a random error that for the life of me I cannot figure out...
the error is: [Runtime Error 1004: Application defined or object defined error], on the line highlighted in yellow in the code below.
I tried Do Until <> "", I tried Do While = ""... Ugh I dont know what to try any more. Please can someone shed some light/wisdom/incredible smartness/experience on this issue...? There are imaginary cookies to be had!! :D

Sub test()
    Dim t As Integer
    Dim k As Integer
    Dim n As Integer
    
     
    t = 3
    k = 3
    n = k + 1
    Do While t < 20
                                                
              ThisWorkbook.Worksheets(1).Cells(t, 2).Value = "t = " & t
           
        If Cells(t, k) = "" Then
            
            [B][COLOR=#daa520]Do Until ThisWorkbook.Worksheets(1).Cells(t, k + 1).Value <> ""[/COLOR][/B]    [COLOR=#006400]'while the cell col 'k+1'(the NEXT column) on the current row IS Nothing, increment k
[/COLOR]              k = k + 1                      [COLOR=#006400] 'will break once 'k+1' is equivalent to a cell NOT equal to nothing
[/COLOR]           Loop
        
        End If
        
        ThisWorkbook.Worksheets(1).Cells(t, k) = "k = " & k
        t = t + 1
    Loop
    MsgBox ("done!")
End Sub

 

[Norie]

How exactly did you try the code?

Was the right sheet active when you ran it?

Could you upload a sample workbook ro somewhere like Box.net and post a link?

 

[SnyperBunny]

Alright, thats the strangest thing ever.... There is no reason it should not be working in my main code. However I thought it strange that nothing seemed to be doing anything.... so I put it into its own module and just ran it manually after the other code was finished and it worked flawlessly... Now I just need to combine them...

Thank you so so much for your help though Norie!!!


EDIT: It worked to call it from a sub, but not to just run it in the main code.... strange, but at least it works. YAY!!!! I am SO happy! Norie, you seriously rock!!! This beast of a macro is DONE!! *happy dance*

 

[Norie]

I don't know if you noticed but there were no worksheet references in the code.

You really should use them to make sure the code works on the correct worksheet.

I left them out because I'm lazy.