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

roypogo

New Member
Joined
Jul 3, 2017
Messages
20
Hi

I have a data in Sheet1 as below:

Data1~Data2~Data3~Data4*~Data5*Data6~Data7~
Data8~Data16~Data24~Data32~Data40Data48~Data56~
Data9~Data17~Data25~Data33~Data41~Data49~Data57~
Data10~Data18~Data26~Data34~Data42~Data50~Data58~
Data11*~Data19~Data27~Data35~Data43~Data51~Data59~
Data12Data20~Data28~Data36~Data44~Data52~Data60~
Data13~Data21~Data29~Data37*Data45~Data53~Data61~
Data14~Data22~Data30~Data38~Data46~Data54~Data62*~
Data15~Data23~Data31~Data39~Data47~Data55~Data63~

<tbody>
</tbody>

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:

Format Error in Row 1, Col D
Format Error in Row 1, Col E
Format Error in Row 2, Col E
Format Error in Row 5, Col A
Format Error in Row 6, Col A
Format Error in Row 7, Col D
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.

<tbody>
</tbody>
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

DushiPunda

Well-known Member
Joined
Nov 14, 2015
Messages
508
Try this:

Code:
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:

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

to this:

Code:
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.
 
Last edited:

roypogo

New Member
Joined
Jul 3, 2017
Messages
20
Try this:

Code:
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:

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

to this:

Code:
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

Well-known Member
Joined
Nov 14, 2015
Messages
508
Try this:

Rich (BB code):
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,599
Members
417,154
Latest member
gm_jagath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top