Chacking data in sheet1 and printing values in the next sheet accordingly

[roypogo]

Hi

I have a data in Sheet1 as below:

[TABLE="width: 448"]
<tbody>[TR]
[TD="width: 64"]Data1~[/TD]
[TD="width: 64"]Data2~[/TD]
[TD="width: 64"]Data3~[/TD]
[TD="width: 64"]Data4*~[/TD]
[TD="width: 64"]Data5*[/TD]
[TD="width: 64"]Data6~[/TD]
[TD="width: 64"]Data7~[/TD]
[/TR]
[TR]
[TD]Data8~[/TD]
[TD]Data16~[/TD]
[TD]Data24~[/TD]
[TD]Data32~[/TD]
[TD]Data40[/TD]
[TD]Data48~[/TD]
[TD]Data56~[/TD]
[/TR]
[TR]
[TD]Data9~[/TD]
[TD]Data17~[/TD]
[TD]Data25~[/TD]
[TD]Data33~[/TD]
[TD]Data41~[/TD]
[TD]Data49~[/TD]
[TD]Data57~[/TD]
[/TR]
[TR]
[TD]Data10~[/TD]
[TD]Data18~[/TD]
[TD]Data26~[/TD]
[TD]Data34~[/TD]
[TD]Data42~[/TD]
[TD]Data50~[/TD]
[TD]Data58~[/TD]
[/TR]
[TR]
[TD]Data11*~[/TD]
[TD]Data19~[/TD]
[TD]Data27~[/TD]
[TD]Data35~[/TD]
[TD]Data43~[/TD]
[TD]Data51~[/TD]
[TD]Data59~[/TD]
[/TR]
[TR]
[TD]Data12[/TD]
[TD]Data20~[/TD]
[TD]Data28~[/TD]
[TD]Data36~[/TD]
[TD]Data44~[/TD]
[TD]Data52~[/TD]
[TD]Data60~[/TD]
[/TR]
[TR]
[TD]Data13~[/TD]
[TD]Data21~[/TD]
[TD]Data29~[/TD]
[TD]Data37*[/TD]
[TD]Data45~[/TD]
[TD]Data53~[/TD]
[TD]Data61~[/TD]
[/TR]
[TR]
[TD]Data14~[/TD]
[TD]Data22~[/TD]
[TD]Data30~[/TD]
[TD]Data38~[/TD]
[TD]Data46~[/TD]
[TD]Data54~[/TD]
[TD]Data62*~[/TD]
[/TR]
[TR]
[TD]Data15~[/TD]
[TD]Data23~[/TD]
[TD]Data31~[/TD]
[TD]Data39~[/TD]
[TD]Data47~[/TD]
[TD]Data55~[/TD]
[TD]Data63~[/TD]
[/TR]
</tbody>[/TABLE]

Now in each cell if the last character is not ~ or if the 2nd last character is * it will give error in column A on the other sheet as:

[TABLE="width: 179"]
<tbody>[TR]
[TD]Format Error in Row 1, Col D[/TD]
[/TR]
[TR]
[TD]Format Error in Row 1, Col E[/TD]
[/TR]
[TR]
[TD]Format Error in Row 2, Col E[/TD]
[/TR]
[TR]
[TD]Format Error in Row 5, Col A[/TD]
[/TR]
[TR]
[TD]Format Error in Row 6, Col A[/TD]
[/TR]
[TR]
[TD]Format Error in Row 7, Col D[/TD]
[/TR]
[TR]
[TD]Format Error in Row 8, Col G

How can I acheive it through VBA. Also I want to expand the logic checking in Sheet1 eg. the length of each cell should be greater than 3 and if it is <=3 it will give an error statement in the next sheet.[/TD]
[/TR]
</tbody>[/TABLE]

 

[DushiPunda]

Try this:

Sub roypogo()
For Each c In Worksheets("Sheet1").UsedRange
    If Right(c, 1) <> "~" Or Left(Right(c, 2), 1) = "*" Then
        With Worksheets("Sheet2")
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = "Format Error in Row " & c.Row & ", Col " & UCase(Chr(c.Column + 96))
        End With
    End If
Next
End Sub

As for the other logic you asked about here:

the length of each cell should be greater than 3 and if it is <=3 it will give an error statement in the next sheet.

Do you want to simply include this check with the above check? So, in the end if ANY of the following are true, it will produce an error:
1. Last letter is NOT a ~
2. Second to last letter IS a *
3. Length of string is less than or equal to 3

If that is the goal, then you can change this line:

If Right(c, 1) <> "~" Or Left(Right(c, 2), 1) = "*" Then

to this:

If Right(c, 1) <> "~" Or Left(Right(c, 2), 1) = "*" Or Len(c) <= 3 Then

Also, by using UsedRange in the first line (For Each c In Worksheets("Sheet1").UsedRange), I'm assuming that there is no other data on Sheet1. In other words, I'm assuming there is not any data that you do not want to check with this code. If there is, then you'll need to find another way to specifically define the range you want to look at. If the range is static and will not change, this will be very easy. If it is not static, then you'll need to get a little more fancy with your code.

Let me know if you have any questions.

 

[roypogo]

Try this:

Sub roypogo()
For Each c In Worksheets("Sheet1").UsedRange
    If Right(c, 1) <> "~" Or Left(Right(c, 2), 1) = "*" Then
        With Worksheets("Sheet2")
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = "Format Error in Row " & c.Row & ", Col " & UCase(Chr(c.Column + 96))
        End With
    End If
Next
End Sub

As for the other logic you asked about here:



Do you want to simply include this check with the above check? So, in the end if ANY of the following are true, it will produce an error:
1. Last letter is NOT a ~
2. Second to last letter IS a *
3. Length of string is less than or equal to 3

If that is the goal, then you can change this line:

If Right(c, 1) <> "~" Or Left(Right(c, 2), 1) = "*" Then

to this:

If Right(c, 1) <> "~" Or Left(Right(c, 2), 1) = "*" Or Len(c) <= 3 Then

Also, by using UsedRange in the first line (For Each c In Worksheets("Sheet1").UsedRange), I'm assuming that there is no other data on Sheet1. In other words, I'm assuming there is not any data that you do not want to check with this code. If there is, then you'll need to find another way to specifically define the range you want to look at. If the range is static and will not change, this will be very easy. If it is not static, then you'll need to get a little more fancy with your code.

Let me know if you have any questions.

Hi Your code is working fantastically.
My Range will remain static.
When I say I want to add extra checks the way I want is once the "If Right(c, 1) <> "~" Or Left(Right(c, 2), 1) = "*" Then " code is completed giving results in Sheet2 it will restart the code "Len(c) <= 3 Then" check and display the results in Sheet2 below the results of the first code.

By this way I want to add more and more checks

 

[DushiPunda]

Try this:

Sub roypogo()
For Each c In Worksheets("Sheet1").UsedRange
    If Right(c, 1) <> "~" Or Left(Right(c, 2), 1) = "*" Then
        With Worksheets("Sheet2")
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = "Format Error in Row " & c.Row & ", Col " & UCase(Chr(c.Column + 96))
        End With
    End If
Next c

For Each c In Worksheets("Sheet1").UsedRange
    If Len(c) <= 3 Then
        With Worksheets("Sheet2")
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = "Format Error in Row " & c.Row & ", Col " & UCase(Chr(c.Column + 96))
        End With
    End If
Next c
End Sub

Basically, just go through it all again. You can change the red text above to whatever you want it to say if the length is less than or equal to 3. Hopefully that makes sense!