Validating excel data in cells in a row

[rogerte]

Completely new to VBA (always just used Excel functions before)

I have added a button to a worksheet, which I have added the macro to.

The macro needs to validate the cells in cols A to E of the last data row. If fails validation then bring-up a warning message.

The problem I am having is with validating the following:

Cols C and D both formatted as currency, and can contain either a positive value or blank, however only one of the two can contain a value, the other must be blank.

Any help would be appreciated.

Thanks

 

[SkipVought]

hi,

What code do you have so far?

Please post.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[rogerte]

Sorry for delay -

Have sort of worked it out, however not sure if it the best way of doing it so have attached code. It appears to work.

It validates the various columns and then assigns to one of 2 other sheets, based on whichever one of 2 columns has data in it

Sub Checkdata()
Dim i, j As Integer
Dim sSheet, sCell As String

Rem 1 - find bottom line of sheet1
i = FindLastRow("Sheet1")
j = 0
' MsgBox (CStr(i))
Rem 2 - check that row not already copied and that required fields are entered
If Range("F" & i).Value = "Transferred" Then
  MsgBox ("Data already copied - cannot continue")
  Exit Sub
End If

If (IsEmpty(Range("A" & i))) Or (IsEmpty(Range("B" & i))) Or (IsEmpty(Range("E" & i))) Or (IsEmpty(Range("C" & i)) And IsEmpty(Range("D" & i))) Then
  MsgBox ("Not all data entered - please correct")
  Exit Sub
End If

Rem 3 - check that data is correct
'  a valid date in column A
If Not (IsDate(Range("A" & i))) Then
  MsgBox ("Not a valid date in column A - please correct")
  Exit Sub
End If
'  either C or D, but not both, contain a positive value
If (Val(Range("C" & i)) > 0) And (Val(Range("D" & i)) > 0) Then
  MsgBox ("Cannot have a value in both receipts and expenditure columns - please correct")
  Exit Sub
End If
'  cannot have a negative
If (Val(Range("C" & i)) < 0) Or (Val(Range("D" & i)) < 0) Then
  MsgBox ("Cannot have a negative value - please correct")
  Exit Sub
End If

Rem 4 - if type is input then get bottom row of sheet2 and then copy relevent cells
Rem   - if type is output then as above but for sheet 3
If Val(Range("C" & i)) > 0 Then
  sSheet = "Sheet2"
  sCell = "C"
ElseIf Val(Range("D" & i)) > 0 Then
  sSheet = "Sheet3"
  sCell = "D"
End If
' get bottom filled row of sheet to copy to and add 1 to it to get next clear row
j = FindLastRow(sSheet) + 1
'MsgBox (sSheet & " Row:" & CStr(j))
'Need to copy cells A, E and either C or D to relevent sheet cells A, B and C
Sheets(sSheet).Range("A" & j).Value = Range("A" & i).Value
Sheets(sSheet).Range("B" & j).Value = Range("E" & i).Value
Sheets(sSheet).Range("C" & j).Value = Range(sCell & i).Value

Rem 5 - now add copied comment to Sheet1
Range("F" & i).Font.Color = vbBlue

Range("F" & i).Value = "Transferred"
End Sub

'==========================================
'Find the last used Row on a Worksheet
'==========================================
Function FindLastRow(sSheet) As Integer

    Dim afRange As String, afRangeNrows As Long, afRangeNcols As Long
    Dim afRangeLastRow As Variant, afRangeLastCol As Variant
    Dim lastRow As Variant
    lastRow = Worksheets(sSheet).Cells(Rows.Count, 1).End(xlUp).Row
    ' Abandoned line below as always returned last formatted row even if empty
    'lastRow = Sheets(sSheet).Cells.SpecialCells(xlCellTypeLastCell).Row
    FindLastRow = lastRow

End Function

This is the first bit of VBA code I have ever written, did a lot of Googling things like "VBA copy cell", and got very confused, so would be interested in hearing how it could improved.

Cheers

Roger

 

[Andrzejek]

Just small stuff:
In your Function FindLastRow you declared several variables, but you just use one (lastRow). If you do not use others, delete them. To find which variables are dec;ared and not used, install MSTools from

http://www.MZTools.com

Those 2 lines:[tt]
Dim i, j As Integer
Dim sSheet, sCell As String
[/tt]
You should know that variables i and sSheet are Variants since you did not specify their type.

Just FYI

Have fun.

---- Andy

 

[rogerte]

Thanks for the reply,

The variables in that function were because I was originally going to try it a different, long winded, way, then found the code that gets the row directly. Forgot to comment out the other variables.

re the comment about variables i and sSheet, I am not certain what you mean. I thought the DIM statement did that

Cheers

Roger

 

[SkipVought]

dim i, j as integer

means that i is a [highlight]Variant by default[/highlight] and j is an [highlight]Integer by declaration[/highlight]. ie all varaibles by be explicitly declared AS a datatype, else implicitly declared a Variant.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

also, any variable that will be used as a ROW counter, ought to be declared as LONG rather than Integer, since row counts can exceed the upper positive limit of Integer (32767)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[rogerte]

Ah ok I understand.

I assumed it would be like previous languages I have used (Clipper/Delphi) where you could define variable type as I did in those DIM lines

Thanks very much