Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
4 short answer questions
(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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
4 short answer questions
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 -----Original Message----- (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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
4 short answer questions
billabong wrote:
(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! 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 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 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 Hope this helps, Matthew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to answer three questions. | Excel Discussion (Misc queries) | |||
Does any one ever answer questions on the Sharepoint DG? | Excel Discussion (Misc queries) | |||
Vlookup? Questions about answer I got??? Help, please. | Excel Discussion (Misc queries) | |||
View Questions and Answer to questions I created | Excel Discussion (Misc queries) | |||
two quick questions on excel. please answer as soon as possible... | Excel Worksheet Functions |