VBA loop code Error help!

[erimhast83]

I am running the below code and getting an error at the end when the do untilempty is not working and the code runs until the third line and there are no more rows in the spreadsheet since column G had no data in any cells. Can someone please help me figure out the error in my coding. Thank you.



Do Until IsEmpty("D2:D")


Range("G2").End(xlDown).Select
ActiveCell.Offset(1, -3).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight).Offset(0, 3)).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-1, 3).Select
Selection.Cut
ActiveCell.Offset(1, -1).Select
ActiveSheet.Paste
ActiveCell.Offset(-1, -1& - 1).Select
Range(Selection, Selection.End(xlToRight).Offset(0, -1)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

Loop

 

[MARK858]

To start with without reading the rest of the code this line is an invalid range

Do Until IsEmpty("D2:D")

possibly you meant

Do Until IsEmpty(Range("D2:D" & Range("D" & Rows.count).End(xlUp).Row))

but I doubt it as it would find the first empty cell.

Can you please explain in words what you actually want the code to do?

 

[erimhast83]

To start with without reading the rest of the code this line is an invalid range

Do Until IsEmpty("D2:D")

possibly you meant

Do Until IsEmpty(Range("D2:D" & Range("D" & Rows.count).End(xlUp).Row))

but I doubt it as it would find the first empty cell.

Can you please explain in words what you actually want the code to do?

I want to have the code look at column D and if there is data in that cell then do what the code says in Line 2 of the code until the loop then go back and continue the loop until there is no data in column D.

 

[MARK858]

then do what the code says in Line 2 of the code

That statement is not explaining it at all.
I don't need to know about code that isn't working, I need to know what you actually want the code to do in words.

 

[erimhast83]

That statement is not explaining it at all.
I don't need to know about code that isn't working, I need to know what you actually want the code to do in words.

I want the code to check each cell in column D to see if there is something in that cell. If there is I want it to go to G2 and then go down to the first cell in G that has data, go one row down, over three cells to the left and then highlight 5 cells and insert a new row of cells there. Cut the data in the column G cell into column F cell in the row just inserted, and then copy the information from the row above in cells Dand E into the newly inserted row below. After that I want the code to go backto the top and check if the cell in column D associated with the cell in columnG has data in it – if it does I want the loop to start over.

 

[MARK858]

Is there ever any data in G2? and if yes is it ever part of the search in "the first cell in G that has data"?
What do you want to happen when "since column G had no data in any cells" is true?

Can you please reword

cell in column D associated with the cell in columnG

as I don't understand what you mean especially as I see no test in the original code.

 

[erimhast83]

I have a file with a client ID, Name and three columns withdifferent services provided. I want to have only one column of servicesprovided. There are three columns because if one person had three services itwas in one column with comas separating. I did text to columns on the columnand now I have three service columns but there are a lot of blanks in thesecond and third columns. What I want the macro to do is look to see if thereis a client id in column D of the data and if so then go to column G where thesecond service column is and then insert a blank row below the second service,move the service type to the first service column and copy the client ID andname down to the blank row. I have the code that works for moving the servicesbut the loop portion doesn’t work.

Is there ever any data in G2? and if yes is it ever part of the search in "the first cell in G that has data"?
What do you want to happen when "since column G had no data in any cells" is true?

Can you please reword

as I don't understand what you mean especially as I see no test in the original code.

 

[erimhast83]

I have a file with a client ID, Name and three columns with different services provided. I want to have only one column of services provided. There are three columns because if one person had three services it was in one column with comas separating. I did text to columns on the columnand now I have three service columns but there are a lot of blanks in the second and third columns. What I want the macro to do is look to see if thereis a client id in column D of the data and if so then go to column G where the second service column is and then insert a blank row below the second service,move the service type to the first service column and copy the client ID and name down to the blank row. I have the code that works for moving the services but the loop portion doesn’t work.

Do Until IsEmpty("D2:D") - THIS IS THE LINE I AM HAVING ISSUES WITH -THE CODING BETWEEN THE DO UNTIL AND THE LOOP WORKS


Range("G2").End(xlDown).Select
ActiveCell.Offset(1, -3).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight).Offset(0, 3)).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-1, 3).Select
Selection.Cut
ActiveCell.Offset(1, -1).Select
ActiveSheet.Paste
ActiveCell.Offset(-1, -1& - 1).Select
Range(Selection, Selection.End(xlToRight).Offset(0, -1)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

Loop


Do Until IsEmpty("D2:D")

Range("H2").End(xlDown).Select
ActiveCell.Offset(1, -4).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight).Offset(0, 4)).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-1, 4).Select
Selection.Cut
ActiveCell.Offset(1, -2).Select
ActiveSheet.Paste
ActiveCell.Offset(-1, -1& - 1).Select
Range(Selection, Selection.End(xlToRight).Offset(0, -1)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

Loop


Do Until IsEmpty("D2:D")
Range("I2").End(xlDown).Select
ActiveCell.Offset(1, -5).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight).Offset(0, 4)).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-1, 5).Select
Selection.Cut
ActiveCell.Offset(1, -2).Select
ActiveSheet.Paste
ActiveCell.Offset(-1, -1& - 1).Select
Range(Selection, Selection.End(xlToRight).Offset(0, -1)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

Loop
Do Until IsEmpty("D2:D")
Range("J2").End(xlDown).Select
ActiveCell.Offset(1, -6).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight).Offset(0, 4)).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-1, 6).Select
Selection.Cut
ActiveCell.Offset(1, -2).Select
ActiveSheet.Paste
ActiveCell.Offset(-1, -1& - 1).Select
Range(Selection, Selection.End(xlToRight).Offset(0, -1)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

Loop
[/QUOTE]

 

[MARK858]

You need a row number after the 2nd D if you are using that method or a last row variable

 

[erimhast83]

You need a row number after the 2nd D if you are using that method or a last row variable

I would like to utilize a last row variable so the macro is dynamic. I need help with the coding for that. Please and thank you.