Formula - Update Form - Skip blank cells

March 20, 2012 at 08:35:30
Specs: Windows 7
This formula works great but won't skip the blank rows

Dim historyWks As Worksheet
Dim inputWks As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim myRng As Range
Dim myCopy As String
Dim myCell As Range

'cells to copy from Input sheet - some contain formulas
myCopy = "D5,D7,D9,D11,D13"

Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("PartsData")

With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With inputWks
Set myRng = .Range(myCopy)

If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With

With historyWks
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(nextRow, "B").Value = Application.UserName
oCol = 3
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With

'clear input cells that contain constants
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.Goto .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With

End Sub


See More: Formula - Update Form - Skip blank cells

Report •


#1
March 20, 2012 at 09:37:36
First, it's not a formula, it's a VBA macro.

Second, please click on the blue line at the end of this post and read the instructions on how to post code in this forum. Then repost your code so that it is easier for us to read.

Third, it might help if you included a brief explanation of what you are trying to do so that we can better understand what you mean by it "won't skip the blank rows".

Keep in mind that we can't see your spreadsheet from where we're sitting, so the more deatil you provide, the easier it is for us to help you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 20, 2012 at 11:33:34
The "Macro" and thanks for clarifing that, wan'ts all of the cells filled in, I have several rows below this that may or may not be filled out. I need to include those cells in the "Macro" for additional items.

<Sub UpdateLogWorksheet()

Dim DataWks As Worksheet
Dim SPRWks As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim myRng As Range
Dim myCopy As String
Dim myCell As Range

'cells to copy from Input sheet to data sheet - some contain formulas
myCopy = "B7,B9,B11,E9,E11,G11,G7,G9,I7,I9,I11,C14,D14,E14,I14,J14,K14,L14,M14,N14,O14,P14,Q14"

Set SPRWks = Worksheets("SOPF")
Set DataWks = Worksheets("Data")

With DataWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With SPRWks
Set myRng = .Range(myCopy)

If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all cells"
Exit Sub
End If
End With

With DataWks
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(nextRow, "B").Value = Application.UserName
oCol = 3
For Each myCell In myRng.Cells
DataWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With

'clear input cells that contain constants
With SPRWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
End Sub
>
http://www.flickr.com/photos/781834...
Here is the link to view the form just in case


Report •

#3
March 20, 2012 at 12:28:10
Did you read the instructions on how to post code in this forum like I asked you to?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 20, 2012 at 12:57:50
Why yes I did, can you not see it?

Report •

#5
March 20, 2012 at 13:17:02
If you have followed the instructions properly, your code would post with the indents preserved, making it a lot easier to read. Of course, I am assuming that you used indents when you wrote the code.

For example...

 With DataWks
    nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
 End With

 With SPRWks
   Set myRng = .Range(myCopy)
     If Application.CountA(myRng) <> myRng.Cells.Count Then
         MsgBox "Please fill in all cells"
         Exit Sub
     End If
 End With

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
March 20, 2012 at 13:29:10
I find the indents pointless, however if it's something you feel strongly about, I will do so on my next issue.

Report •

#7
March 20, 2012 at 15:11:15
If you find the indents pointless, then you are in a very small minority.

If you have to spend any amount of time reviewing code written by others (as many of us that help others do) you will find that the indents work just like paragraphs in a novel, setting off sections of code to make it easier to follow - and troubleshoot.

As noted here, "whitespace" which includes indentation, "can set things off and reduce the strain on the reader's eyes."

http://www.cprogramming.com/tutoria...

Here's another article on The "Golden Rule" of Indentation

http://www.cs.arizona.edu/~mccann/i...

Look at any of the VBA examples posted here and note the consistent use of indentation for even the simplest of macros.

http://www.ozgrid.com/VBA/vba-macro...

Did you ever wonder why the VBA editor includes an Auto-Indent feature?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question