New at VB, need help implementing simple commands...

[Nina Hibbler]

It's been a while, but I've programmed in C++. I've never programmed in
VB. I'm trying to figure out how to implement some simple checks within
an Excel sheet. I want to be able to check things like string length (<
and >), column width, number of columns to be a certain amount, whether
or not a string of characters are in all caps and things like this. I
wanted to know if anyone could help me accomplish this. I've been
wanting to figure out an easier way to check through a sheet filled with
data.

Thanks


*** Sent via Developersdex http://www.developersdex.com ***

 

[Gary''s Student]

Each of this things you mentioned can be accomplished without much difficulty
in VBA.

I suggest that you post individual questions. That way we can attack the
list piece-meal.

 

[Nina Hibbler]

Okay...

1. Check cells withing column 1 to make sure character length is exactly
= 9.

2. Entries in columns 2 an 3 are to be centered, 1 character in length,
are to be in all caps and can only be certain characters (i.e. A, C, H,
O).

3. Column 4: 3 characters in length, all caps, are limited to type for
example, ABC or DEF only.

4. Column 5: all caps.

5. Column 6, 7 & 8: these are dates and must be formatted mm/dd/yyyy.

This is the basic details of what I'm trying to accomplish because
sometimes I have a lot of information to look at it and my eyes may not
be the best to rely on. Plus, writing code makes a task like this
easier.

I also want to know if something like this can be put in place so if I
were entering fresh data into a sheet that didn't match the preset rules
...an error message would display...

*** Sent via Developersdex http://www.developersdex.com ***

 

[filo666]

1.

bla bla bla

for a=YourFirstRow to YourLastRow
if cell(a,NumberOfColumn)=9 then
WhateverYouWantExcelDoWhenIfStatementIsTrue
else
WhateverYouWantExcelDoWhenIfStatementIsFalse
End if
Next

more blablabla
HTH

 

[filo666]

sorry, I thowght = 9 (number) hold on

1.

bla bla bla

for a=YourFirstRow to YourLastRow
if cell(a,NumberOfColumn)=9 then
WhateverYouWantExcelDoWhenIfStatementIsTrue
else
WhateverYouWantExcelDoWhenIfStatementIsFalse
End if
Next

more blablabla
HTH

 

[Dave Peterson]

To me, check #2 and check #3 are almost the same--just looking for different
strings.

Maybe this will give you some ideas. (I left #3 for you to do <bg>.)

Option Explicit
Sub testme01()

Dim myRng As Range
Dim wks As Worksheet
Dim myMin As Long
Dim myMax As Long
Dim myCount As Long
Dim myValues As Variant
Dim iCtr As Long
Dim myCell As Range

Set wks = Worksheets("sheet1")

With wks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

myMin = Application.Evaluate("min(Len(" _
& myRng.Address(external:=True) & "))")

myMax = Application.Evaluate("max(Len(" _
& myRng.Address(external:=True) & "))")

If myMin = 9 _
And myMax = 9 Then
'all ok
MsgBox "ok min/max"
Else
MsgBox "Not all length of 9!"
End If

'-----------------------------

Set myRng = .Range("b1:c" & .Cells(.Rows.Count, "A").End(xlUp).Row)

'just fix them
myRng.HorizontalAlignment = xlCenter

myValues = Array("A", "C", "H", "O")

myCount = 0
For iCtr = LBound(myValues) To UBound(myValues)
'=SUMPRODUCT(--(EXACT(B1:C3,"A")))
myCount = myCount + _
Application.Evaluate("Sumproduct(--(exact(" _
& myRng.Address(external:=True) _
& ",""" & myValues(iCtr) & """)))")
Next iCtr

If myCount = myRng.Cells.Count Then
'all ok
MsgBox "b/c ok"
Else
MsgBox "B/C not all ok"
End If

'-----------------------------

Set myRng = .Range("E1:E", .Cells(.Rows.Count, "A").End(xlUp).Row)

'just fix them
For Each myCell In myRng.Cells
myCell.Value = UCase(myCell.Value)
Next myCell

'-----------------------------

Set myRng = .Range("F1:H", .Cells(.Rows.Count, "A").End(xlUp).Row)
'just fix them
myRng.NumberFormat = "mm/dd/yyyy"

'-----------------------------

End With

End Sub

A few of the items are easier to just fix than check. Is that a problem?

And you may be able to use data|validation for a few of these for future
entries.

Debra Dalgleish has lots of info about data|validation at:
http://www.contextures.com/xlDataVal01.html

 

[Rowan Drummond]

Hi Nina

A lot of this can be achieved without any VBA but rather using Excels
built in Conditional Formatting and Data Validation.

For example select column A so that A1 is the activecell. From the menus
select Format > Conditional Formatting. Change the first drop down from
"Cell Value is" to "Formula Is". In the second drop down enter the formula:
=len(A1)<>9
Click on the format button and select a format for the cells that have a
length not equal to 9 eg red background.

To prevent fresh data being entered into Column A which is not 9
characters in length, select the column and from the menus select
Data>Validation. Change the options to Allow Text Length Data Equal To
Length 9. You can then select the Input message tab and enter a message
that will appear if the user tries to enter text that is not 9
characters in length.

I hope this helps
Rowan

 

[filo666]

1.OK, Again
bla bla bla
For a = YourFirstRow To YourLastRow
If Len(Cells(a, NumberOfColumn)) = 9 Then
'WhateverYouWantExcelDoWhenIfStatementIsTrue
Else
'WhateverYouWantExcelDoWhenIfStatementIsFalse
End If
Next
more bla bla bla

 

[Nina Hibbler]

Thanks. All of this information help a lot! I will try it out and see
what I can get from it.

*** Sent via Developersdex http://www.developersdex.com ***