Macro to skip to next non blank cell

[slohman]

I am trying to figure out when I run this macro if there is already text or value in

it will skip to next blank row ie row 27, 28, 29 are filled then skip to row 30 and place text or value.

[pre]

Private Sub CommandButton1_Click()
Dim SheetName As String
SheetName = "Estimate1"
SheetName = InputBox("enter the name of a sheet to use", "sheet name", SheetName)
Dim i As Long
Dim MyCol As Integer
Dim MyRow As Integer

LR = Sheets(SheetName).Range("BY" & Rows.Count).End(xlUp).Row
MyCol = 3
MyRow = 27
For i = 118 To 561
If Sheets(SheetName).Range("BZ" & i).Value <> "" Then
Sheets("Installer").Cells(MyRow, MyCol).Value = Sheets(SheetName).Range("BZ" & i).Value
MyRow = MyRow + 1
End If
If MyRow = 52 Then
If MyCol = 3 Then
MyCol = 16
MyRow = 27
Else
MsgBox "You have ran out of room.  Some entries were not copied"
Exit For
End If
End If
Next i

End Sub

[/pre]

 

[Hui]

Slohman


It is really difficult to debug code without data


Are you able to upload a sample worksheet that this can be ran on?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[slohman]

http://www.2shared.com/file/EziNjFPX/sample_workbook.html

 

[SirJB7]

Hi, slohman!


Thy with this fix:

-----

[pre]

Private Sub CommandButton1_ClickXXX()
Dim SheetName As String
SheetName = "Estimate1"
SheetName = InputBox("enter the name of a sheet to use", "sheet name", SheetName)
Dim i As Long
Dim MyCol As Integer
Dim MyRow As Integer

LR = Sheets(SheetName).Range("BY" & Rows.Count).End(xlUp).Row
MyCol = 3
MyRow = 27
For i = 118 To 561
If Sheets(SheetName).Range("BZ" & i).Value <> "" Then
' skip non blanks - start
Do Until Sheets("Installer").Cells(MyRow, MyCol).Value = ""
If MyRow = 52 Then
MyCol = 16
MyRow = 27
Else
MyRow = MyRow + 1
End If
Loop
' skip non blanks - end

Sheets("Installer").Cells(MyRow, MyCol).Value = Sheets(SheetName).Range("BZ" & i).Value
MyRow = MyRow + 1
End If
If MyRow = 52 Then
If MyCol = 3 Then
MyCol = 16
MyRow = 27
Else
MsgBox "You have ran out of room.  Some entries were not copied"
Exit For
End If
End If
Next i

End Sub

[/pre]
-----


Regards!

 

[slohman]

Thankyou so much for that it worked great but I should have taken out the carry on MyCol 16 I dont want it to carry onto MyCol 16 but just tell me it ran out of room at MyRow 52 sorry for that. How would I fix this.

 

[SirJB7]

Hi, slohman!


Replace all the code by this:

-----

[pre]

Private Sub CommandButton1_ClickXXX()
Dim SheetName As String
SheetName = "Estimate1"
SheetName = InputBox("enter the name of a sheet to use", "sheet name", SheetName)
Dim i As Long
Dim MyCol As Integer
Dim MyRow As Integer

LR = Sheets(SheetName).Range("BY" & Rows.Count).End(xlUp).Row
MyCol = 3
MyRow = 27
For i = 118 To 561
If Sheets(SheetName).Range("BZ" & i).Value <> "" Then
Do Until Sheets("Installer").Cells(MyRow, MyCol).Value = "" Or MyRow > 52
MyRow = MyRow + 1
Loop
If MyRow <= 52 Then
Sheets("Installer").Cells(MyRow, MyCol).Value = Sheets(SheetName).Range("BZ" & i).Value
MyRow = MyRow + 1
Else
MsgBox "You have ran out of room.  Some entries were not copied"
Exit For
End If
Next i

End Sub

[/pre]
-----


Regards!

 

[slohman]

Going back to my original code is there a way to have a blank cell if I write another macro to start at 1 to 118 then 1 blank row then start again from 118 to 561 continously mycol 3 my row 25 then mycol 16 my row 25.


I hope this makes sense??

 

[slohman]

If I use either of these macro can I change it and add that I would like BY and BZ columns joined togehter with a hypen -


Or can I add a concatenate to the macro.