4 short answer questions

  • Thread starter Thread starter billabong
  • Start date Start date
B

billabong

(1) I would like to check cells A1 and A2 to see if either is NOT
blank and then do some copying. How do you use the NOT opeartor on
isblank in if statement below, and how do you simply copy whats in one
cell to another? Have tried Cells(1, "e").value but dont no what that
does


If Cells(1, "a") = isblank & Cells(2, "a") = isblank Then

Cells(1, "e") = Cells(1, "a") 'nothing appears in E1
Cells(2, "e") = Cells(2, "a")

End If
I will be checking several cells to see if they are blank!


(2) Also is it possible to code in VBE data validation? If so what
does the code look like? Any simple examples out there


(3) Is there a more elegant way to clear a column besides this:

Columns("a").Select
Selection.ClearContents
When this is done a black flicker appears in the comlumn

(4) Last question for today? I have a for statement but want it to
stop at less than iteration
For i = 0 To iteration
Were does the "<" go

Thanks for your help
Lisa
 
Lisa,

I can answer the easy one (3) and maybe (4)

(3)
You can combine those two lines into one:

Columns("a").ClearContents


You can also put

Application.ScreenUpdating = False

before your code and

Application.ScreenUpdating =True

after your code. This should stop the flicker.

(4)
If what you mean is that you may reach a condition that
allows you to continue before you reach iteration, then
you can include the line

i=iteration

and it will exit as soon as it hits the

Next i

line

I'm not sure I undeerstand what you are asking with your
other two, so I'll leave lthem for somebody else to
attempt. Hope that helps.

Ryan
 
billabong said:
(1) I would like to check cells A1 and A2 to see if either is NOT
blank and then do some copying. How do you use the NOT opeartor on
isblank in if statement below, and how do you simply copy whats in one
cell to another? Have tried Cells(1, "e").value but dont no what that
does


If Cells(1, "a") = isblank & Cells(2, "a") = isblank Then

Cells(1, "e") = Cells(1, "a") 'nothing appears in E1
Cells(2, "e") = Cells(2, "a")

End If
I will be checking several cells to see if they are blank!
Click to expand...

isblank is not a built-in part of VBA. You might find it helpful to
put the line "Option Explicit" at the start of each module - it will
require you to DIM variables and raise errors when you use words VBA
doesn't know (like isblank here)

That said, you could use the following:
If Cells(1, "a").Formula = "" And Cells(2, "a").Formula = "" Then
Cells(1, "e").Value = Cells(1, "a").Value
Cells(2, "e").Value = Cells(2, "a").Value
End If

1. isblank is replaced by looking for any formula in the cell
2. the & operator is replaced with the logical And. '&' would put
together the values as string (like "TrueTrue")
3. .Value is added on each cell line. .Value is the default property
so this isn't needed - just helps for readability.

My personal style would be to use either:
Range("A1") <or>
Cells(1, 1)
instead of Cells(1, "a"). I find the first easier to read and the
second easier to use with variables (both
(2) Also is it possible to code in VBE data validation? If so what
does the code look like? Any simple examples out there
Click to expand...

I haven't done it. From the VBA help I found this example:
With Range("e5").Validation
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertInformation, _
Minimum:="5", Maximum:="10"
.InputTitle = "Integers"
.ErrorTitle = "Integers"
.InputMessage = "Enter an integer from five to ten"
.ErrorMessage = "You must enter a number from five to ten"
End With

Essentially, there is a Validation object that is part of the the
Range object. Also note that Cells(1, "a") is another way of using
Range objects.

(3) Is there a more elegant way to clear a column besides this:

Columns("a").Select
Selection.ClearContents
When this is done a black flicker appears in the comlumn
Columns("a").ClearContents

(4) Last question for today? I have a for statement but want it to
stop at less than iteration
For i = 0 To iteration
Were does the "<" go
Click to expand...

Two suggestions

For i = 0 to 10
<code block a>
If <want to get out> Then
Exit For
End If
<code block b>
Next

Above, Exit For will immediately jump out of the next - code block b
will not execute for the jumped out iteration

For i = 0 to 10
<code block a>
If <want to get out> Then
i = 10
End If
<code block b>
Next

Here, i will = 10 while code block b executes. At the Next line, i
will increment to 11 and the For-Next loop will terminate.

Thanks for your help
Lisa
Click to expand...

Hope this helps,

Matthew
 
Back
Top