Hello all, Im customising some code I got online at the page http://www.contextures.com/xlForm02.html
I need some help editing the code in red. Basically, the code is used in conjunction with an excel form, so the user form is on one worksheet and the data collected is stored in another sheet. Currently there's a validation check to ensure that all cells have data entered. I want to adjust it since there are specific cells I wont need data in. So i figure the key piece of code is the line
If Application.CountA(myRng) <> myRng.Cells.Count
so i want to adjust it to something like If Application.CountA(myRng) <> myRng.Cells.Count excluding cell D13. Im just not sure of the correct syntax. Hope i was clear enough. Thank you all in advance for your speedy responses.
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
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With
Bookmarks