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]

This will do what you want, obviously the range involved is different - no clear what your range is.

Set Rng = Range("B1")

    While Rng.Value <> ""
          Rng.End(xlToRight).Offset(, -1) = Rng.Value
          Rng.ClearContents
          Set Rng = Rng.Offset(1)
    Wend

 

[SnyperBunny]

Thank you for your help.
As for range, there IS no clear range. It needs to be able to handle any number of rows and columns, with the data each time being different, and also the location of the final data that is being moved.

 

[SnyperBunny]

and this:

   Dim last_row as Range
   Set last_row = ThisWorkbook.Worksheets(1).Cells.Find("", Range("B2"), xlValues, xlWhole, xlByColumns, xlNext)
   Set Rng = Range("B3", last_row)

Did not work. wont compile - gives an error 1004 - method 'range' of object '_global' failed.


(and on another note - I'm still very new at vba coding, so if this is a silly little thing, please forgive my ignorance of it.)

 

[Norie]

The code I posted will start in B1, which can be changed, and will go down the column until a blank cell is encountered.

It will move the value in column B across as many columns as required so it is in the column to the left of the next value in the row.

Isn't that what you want to do?

 

[Norie]

   Dim last_row as Range
   Set last_row = ThisWorkbook.Worksheets(1).Cells.Find("", Range("B2"), xlValues, xlWhole, xlByColumns, xlNext)
   Set Rng = Range("B3", last_row)

Whose code is this?

 

[SnyperBunny]

thats my attempt to find the location of the first blank in the B column.... All I could think of to find the blank so as to state a range.

and yes, I believe that is exactly what I want to do... however when I put your code in my macro... nothing happened... It compiled and ran, but no evidence of the code you posted being activated/doing anything...

 

[Norie]

Did you try running the code I posted on its own?

Also, where does your data start in column B? Is it B1? If it isn't did you change the code to reflect that.

If you want to find the last row in column B and loop from row 3 down to it you can use this.

    LastRow = Range("B" & Rows.Count).End(xlUp).Row

    For I = 3 To LastRow
        With Range("B" & I)
            .End(xlToRight).Offset(, -1) = .Value
            .ClearContents
        End With
    Next I

 

[SnyperBunny]

I did change it to B3... and still no luck... I'll try that now. Thank you so much for helping me

 

[SnyperBunny]

Still did not appear to do anything....